Call Us: +32 2 466 00 16
Email: info@u2u.be
Follow Us:

Designing Data Models for Power BI

2 days
upbidm
2 days

Upcoming Sessions

Date:

Format:

Price:

Location:

Book now

Date:

Format:

Price:

Location:

Book now

Interested in a private company training? Request it here.

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

Data modeling is an essential step in business intelligence, when data is stored centrally to be used by multiple reports and analytical tools. This becomes an essential skill when building Power BI data flows, Power BI data sets, Analysis tabular models or other analytical data storage solutions on top of a data warehouse. In this training you will learn how to design such models according to the popular dimensional modeling techniques of star schemas.

This course is intended for Business Intelligence developers responsible for designing Power BI data models, or Power BI report builders who need to grasp the dimensional modeling technique to understand how to use their source data model.

Contact Us
  • Address:
    U2U nv/sa
    Z.1. Researchpark 110
    1731 Zellik (Brussels)
    BELGIUM
  • Phone: +32 2 466 00 16
  • Email: info@u2u.be
  • Monday - Friday: 9:00 - 17:00
    Saturday - Sunday: Closed
Say Hi
© 2022 U2U All rights reserved.