Designing Data Models for Power BI

The need for Data Modeling

When building a business intelligence solution, data has different modeling requirements than what is needed for operational applications. This module describes the challenges that can arise when storing data, and how good data models can overcome these. It presents the data model as an abstraction layer between the operational part and the analytical part of the corporate IT stack.

• Data model definitions and terminology
• Advantages and disadvantages of normalizing data
• Issues when reporting on operational databases
• Differences between data warehouse, data flow, and data set
• Data model advantages
• Conducting user interviews
• Prioritizing the requirements
• Documenting the requirements
• LAB: Compare different data models

Modeling Dimensional Tables

Dimensional modeling is based on the idea of splitting data in the things that are (dimensions) and the things that happen (facts). This module describes the properties of dimension tables: Keys, attributes, hierarchies, ... .

• Introducing dimensional modeling
• The need for dimension tables
• Properties of dimension tables
• Surrogate keys
• Star versus snowflake dimensions
• LAB: Building dimensional tables

Modeling Factual Tables

Dimensional modeling is based on the idea of splitting data in the things that are (dimensions) and the things that happen (facts). This module describes the most common types of fact tables

• Properties of Fact tables
• Additive, Semi-additive and none-additive measures
• Transactional Fact Table
• Snapshot Fact Table
• Accumulating Snapshot Fact Table
• Inspecting the size of the fact tables in Power BI
• LAB: Building fact tables

Designing the Date Dimension

Everybody wants to analyze over time, so this is the most common dimension in data models. Based on the principles introduced in the previous module a date dimension is built.

• Why do we need a date dimension
• The danger of the auto-generated date tables in Power BI
• Designing the date dimension
• Working with Time
• Multiple time zones
• Useful tools and scripts
• LAB: Building a Date dimension in Power BI

Working with Slowly Changing Dimensions (SCD)

Since the dimension tables describe the things that are, they don't change as often as fact tables. Yet also properties of products, customers and other dimensions change over time as well. The different ways to register this in the data warehouse are discussed in this module.

• Why do we need Slowly Changing Dimensions
• Type I, II and III slowly changing dimensions
• Designing for SCD
• Advanced SCD types
• LAB: Working with Slowly Changing Dimensions

Advanced dimensional modeling

The previous chapters covered the regular, most common scenario of modeling data. In this module some of the more special scenarios are covered, such as data arriving out of chronological order, parent-child relationships etc.

• Early arriving facts
• Late arriving facts
• Dealing with currency conversion
• Dealing with hierarchies
• Role playing dimensions
• Modeling many to many relations
• LAB: Advanced Dimensional Modeling

Physical data model design

When implementing a data model in Power BI, a few choices need to be made regarding the physical implementation of the model. These are discussed in this module.

• Staging tables In Power Query
• Impact of modeling on model size
• Data Flows
• Aggregation tables
• LAB: Implementing a dimensional model