Dimensional Modeling - Part 0: 4-Step Design Process

Step 1: Select the Business Process

Consider the needs of the business along with the realities of the underlying source data during the collaborative modeling sessions.

• Business processes are the operational activities performed by an organization, such as taking an order, processing an insurance claim, registering students for a class, or snapshotting every account each month.
• Business process events generate or capture metrics that translate into facts. Most fact tables focus on the results of a single business process.
• The process is important because it defines a specific design target and allows the grain, dimensions, and facts to be declared.
• Each business process corresponds to a row in the enterprise data warehouse bus matrix.

Following the business process, grain, dimension, and fact declarations, the design team determines the table and column names, sample domain values, and business rules.

Step 2: Declare the Grain

• The grain establishes exactly what a single fact table row represents
• The grain declaration becomes a binding contract on the design
• The grain must be declared before choosing dimensions or facts because every candidate dimension or fact must be consistent with the grain
• This consistency enforces a uniformity on all dimensional designs that is critical to BI application performance and ease of use
• Atomic grain refers to the lowest level at which data is captured by a given business process
• We strongly encourage you to start by focusing on atomic grained data because it withstands the assault of unpredictable user queries

Step 3: Identify the Dimensions

Dimensions for Descriptive Context: Dimensions provide the "who, what, where, when, why, and how" context surrounding a business process event.

In Kimball dimensional modeling, the "7Ws" refers to a structured approach to identifying business processes and events, focusing on "who, what, when, where, why, how, and what else" to build a data warehouse model, ultimately leading to a better understanding of the business.

The 7Ws Process

• Who: Identifies the actors or entities involved in the business process
• What: Describes the events or actions that occur
• When: Specifies the timing or timeframes of the events
• Where: Indicates the location or context of the events
• Why: Explains the motivations or reasons behind the events
• How: Describes the methods or processes used to execute the events
• What Else: Encourages exploration of additional relevant details and context

BEAM Method

The 7Ws approach is often used in conjunction with the BEAM method:

• Business Event
• Attribute
• Measure

BEAM helps in identifying event stories by asking "who does what" questions and then expressing the answer as a simple story, like "traders buy commodities".

The BEAM method uses tools like hierarchy charts, timelines, event matrices, and enhanced star schemas to model events, dimensions, processes, and star schemas.

Benefits of the 7Ws Approach:

• Helps in identifying key business processes and events
• Facilitates the creation of a more accurate and business-oriented data warehouse model
• Enables better data analysis and reporting

Dimension tables contain the descriptive attributes used by BI applications for filtering and grouping the facts. Dimension tables are the drivers of the user's BI experience.

Step 4: Identify the Facts

• Facts are the measurements that result from a business process event
• A single fact table row has a one-to-one relationship to a measurement event as described by the fact table's grain
• A Fact table corresponds to a physical observable event, and not to the demands of a particular report
• Only facts consistent with the declared grain are allowed

References

• Lecture notes from Business Analytics course, Prof. Paolo Menna, University of Verona 2024-2025