On-site & Public, Instructor-led, Hands-on IT Training
(International 01) 908.233.8900
  400+ Course Offerings:

Search for Course:

DW1011 Advanced Data Warehousing
Duration2 Days
Delivery MethodInstructor Led
Public Price$950.00

DESCRIPTION (Return to Top)

This 2 day course introduces experienced students to best industry practices for dealing with difficult data warehouse data structures, databases and processes.

This class is for experienced data warehouse architects and database designers. If you already have hands-on experience and want to refine your data warehousing skills, this is the class for you. The class will describe the most challenging data warehouse design problems the world of data warehousing has faced. You’ll also have the opportunity to share experiences gained on previous projects with other attendees. This class is intense and fast-paced.

Among these requirements are: handling aggregation, heterogeneous product and transaction types, handling time and history, handling changing dimensions, handling late arriving data, supporting data with different rates of change and stability, supporting large scale database environments such as MPP (massively parallel processing).

This class will also go over in depth different architectures for building data warehouses, such as centralized, federated and functional.

When first modeling for the data warehouse, even the most experienced of applications data modelers and database designers can falter over these challenges. They can even make critical errors in design. This is especially true when dealing with the situations just described above. On the one hand, a database designer could design a database that could perform badly. On the other hand, the database designer could come up with a design that squanders the power of a given technology.

The reason is that the data warehouse requires different roles and uses of data, a different use of normalization, and new modeling constructs. Key special requirements of the data warehouse focus on time, location, and dimensional aspects of data These requirements are among the reasons that analytical data modeling demands different skills, perspectives and techniques.

This workshop focuses on where data models fit into the data warehouse development process. It provides the skills required and techniques necessary to produce the data models. It shows how to use data to implement and maintain a data warehouse. In addition, modeling data warehouses presents new data design challenges. The major factors to consider in data warehouse database design are: data size and complexity; query composition and complexity; query load; and query concurrency. Technology also plays a role. Evaluation of these factors will result in different database designs. Analytical modeling constructs that support time, location, dimensionality and redundancy mean that even experienced data modelers and database designer need to learn new skills.

This workshop provides those skills.

AUDIENCE (Return to Top)

Experienced developers and administrators involved in data warehousing and experienced business and technical data warehouse team members.

OBJECTIVES (Return to Top)

Upon successful completion of this course, the student will be able to understand:

  • How to decide the best architecture
  • Proven analytical modeling concepts
  • Use of surrogate keys
  • Deciding what is a dimension, dividing and combining dimensions, leveled dimensions, abstract dimensions, ragged dimensions
  • How to handle changing dimensions
  • How to handle snapshot data
  • How to handle complex dimensions
  • How and when to use aggregates
  • How to handle multiple units of measurement, such as currency reporting, multi-time-zone reporting, etc.
  • How to handle multi-valued dimensions
  • How to handle restatement
  • How to handle snowflake structures
  • Traversing unbalanced hierarchy

TOPICS (Return to Top)

  • Data Warehouse Architectures
  • Analytical Modeling Primer
  • Dimensions
  • Keys
  • Time and History
  • Changing Dimensions
  • Dimensions and Hierarchies
  • Value Banding
  • Aggregation
  • Operational Data Store (ODS)
  • Case Exercises


Our “Data Modeling for the Data Warehouse”, or our "Designing the Data Warehouse", or equivalent experience and an understanding of data modeling, especially entity-relationship modeling.