TDWI Dimensional Data Modeling Primer: From Requirements to Business Analytics

This third course is a step above an introductory course and discusses these topics without getting into the "nuts and bolts" detail. It was compromised of five modules, "Dimensional Modeling Concepts," "Requirements Gathering for Dimensional Modeling," "Logical Dimensional Modeling," "From Logical Model to Star Schema," and "Dimensional Data and Business Analytics."

In the "Concepts" module (Module 1) the course covers business metrics, conceptual/logical/physical levels of modeling, differences between relational (normalized) and dimensional modeling especially at the logical level, and dimensional modeling definitions.

In the "Requirements Gathering" module (Module 2) the course covers the business context for data modeling, business questions as requirements models, fact/qualifier analysis, and a summary. After discussing business questions the course had students fill out a list of business questions regarding tracking the instructor's performance and a goal of continuous improvement. This module finished off by having the students map out a business question into an existing matrix. Then the students were asked to take their list of questions and fill out a blank matrix.

In the "Logical Dimensional Modeling" module (Module 3) the course described how to take the fact/qualifier matrix and turn it into a logical model by finding the meters in the facts and the hierarchies in the qualifiers, then completing the dimensions by expanding them with additional attributes. The model is then refined by determining the granularity, examining the measures and updating to meet the granularity, and adding measures. Sounds easy no? The module finished off by having the students map out their matrix from the end of Module 2. This does not prove as easy as it sounds. It took several iterations for the entire class to get a dimensional model that was agreeable. We discussed several different ways to include the idea of the location's event having an effect, attendee attrition during the class, is the class even based or self based, etc.

In the "Star Schema" module (Module 4) the course described how to move from the logical to the physical levels, degenerate dimensions, defining keys, supporting calculated measures, conformed dimensions, different types of changing dimensions, and semi-additive and non-additive facts. The module finishes of by having the students take their logical model from Module 4 and create a physical model from it.

We ran out of time for Module 5, "Dimensional Data and Business Analytics," but it basically was to consist of an OLAP demonstration.

Take aways:
The concept of turning a business requirement into a physical model.
The use of a fact/qualifier matrix.
Differences between a conceptual, logical, and physical model.