Data modeling is a required skill to get the most out of Power BI, Power Pivot for Excel, and Analysis Services. This video course is aimed at users of Power BI Desktop or Power Pivot for Excel, and at Analysis Services developers who want to learn how to build the optimal data model for their reporting needs.

The goal of the video course is to teach through examples of increasing complexity how to solve business scenarios by adapting the data model, so that the required DAX code becomes easier, faster and more robust. All the demos and the exercises are based on Power BI examples. However, the very same concepts can be applied to Power Pivot and Analysis Services Tabular.

The video course is made up of over 10 hours of lectures, plus another estimated 8 hours of individual exercises. You can watch the videos at any time and the system will keep track of your progress. Within the video course you can download the material for all the exercises.


Students of the video course have access to a private discussion area where they can interact with the instructors asking questions related to the lectures and the exercises.

Curriculum

  • Presentation of Data Modeling for Power BI

    • Presentation of Data Modeling for Power BI
      FREE
  • Exercises, slides, and demos

    • How to download and complete exercises
      FREE
    • Exercises download
    • Demos download
    • Slides of the video course
    • SQL Server Contoso DW database
  • Introduction to data modeling

    • Introduction to data modeling
      FREE
    • Introduction
      FREE
    • Analytical limits
      FREE
    • Increasing the analytical power
      FREE
    • Introducing the data model
      FREE
    • Leveraging the data model
      FREE
    • Normalization and denormalization
    • Facts and dimensions
    • Introducing star schemas
    • If you don't have a star schema
    • Chains of relationships
    • How many dimensions?
    • Why data modeling is useful
    • LAB number 1
  • Header / detail tables

    • Header / detail tables
      FREE
    • Introducing header / detail schemas
    • Bidirectional filtering is not the way to go
    • Header / detail
    • Denormalizing the discount
    • Back to a star schema
    • LAB number 2
  • Multiple fact tables

    • Multiple fact tables
      FREE
    • Using multiple fact tables
    • Moving filters with DAX
    • Building a star schema
    • How to properly use multiple fact tables, if present
    • Trying bidirectional filtering
    • Model ambiguity
    • Solving ambiguity
    • LAB number 3
  • Working with date and time

    • Working with date and time
      FREE
    • Date attributes in the fact table
      FREE
    • Building a date dimension
    • CALENDARAUTO
    • The model with a date dimension
    • Automatic date grouping in Excel and Power BI
    • Automatic date grouping in Power BI
    • Quick calculations in Power BI Desktop
    • Disable automatic date columns
    • Handling multiple dates
      FREE
    • Multiple date tables
      FREE
    • Multiple date tables with multiple fact tables
      FREE
    • Multiple relationships with date
    • Handling date and time
    • Computing with working days
    • Create a holidays table (one country)
    • Create a holidays table (multiple countries)
    • Weekends are not always the same
    • Handling special periods of the year
    • Non-overlapping periods: the model
    • Non-overlapping special periods
    • Overlapping special periods
    • Overlapping period measure
    • LAB number 4
  • Tracking historical attributes

    • Tracking historical attributes
      FREE
    • Attributes change over time
    • Handling variations over time
    • Slowly changing attributes or dimensions?
    • Rapidly changing dimensions
    • Attributes in the fact table
  • Using snapshots

    • Using snapshots
      FREE
    • What is a snapshot?
    • Sales versus inventory in the same model
    • Non-additive measures
    • LASTDATE does not work here
    • Optimizing performance
    • Snapshots and granularity
    • Transition matrix
    • Parameter table
    • LAB number 5
  • Analyzing date and time intervals

    • Analyzing date and time intervals
      FREE
    • What are intervals?
    • Solving with DAX... too complex!
    • Changing granularity
    • Split hours AND amount!
    • Analyzing active events
    • Open orders: the starting model
    • Open orders with DAX
    • Changing the model
    • Open orders is a snapshot table
    • Events with different durations
    • Daily Salary (DAX)
    • Precompute the values
    • LAB number 6
  • Many-to-many relationships

    • Many-to-many relationships
      FREE
    • What are many-to-many relationships?
    • Possible solutions to the scenario
    • Bidirectional filtering
    • Using CROSSFILTER
    • Using expanded table filtering
    • CROSSFILTER versus expanded tables
    • Understanding non-additivity
    • Cascading many-to-many
    • LAB number 7
  • Working with different granularities

    • Working with different granularities
      FREE
    • Dimensions define granularity
    • Analyzing budget data
    • Reduce granularity on all the tables
    • Using DAX to move the filters
    • Filtering through relationships
    • Use the correct column to slice
    • Leveraging relationships
    • Checking granularity in the report
    • Hiding or reallocating?
    • LAB number 8
  • Segmentation data models

    • Segmentation data models
      FREE
    • Segmentation models
      FREE
    • Static segmentation
      FREE
    • Static segmentation: the formula
      FREE
    • Dynamic segmentation
    • Dynamic segmentation: the formula
    • ABC and Pareto analysis
  • Working with multiple currencies

    • Working with multiple currencies
      FREE
    • Using multiple currencies
    • Beware of simple calculations
    • Multiple sources, one reporting currency
    • Conversion with a calculated column
    • Single source, multiple reporting currencies
    • What the formula should perform
    • Multiple sources, multiple reporting currencies
Student Rating
4.8
55 ratings
81%
15%
2%
2%
0%
2-star Reviews (1 of 35)
  • Robert Williams (Jan 20, 2020)

    The course subject and topics covered is really interesting and great explanation. However, it is really frustrating so far as you cannot complete this course unless you know DAX, and I'm not talking about the free dax course, it needs to go higher because it is assumed you understand how dax and each of it's functions work in order to complete each of the tasks/challenges at the end of each module. That was never made clear when buying the course! It should be clearly stated that you should either complete one of the more advanced DAX courses first, or take a quick self test on DAX questions to ensure your level is high enough before buying this course.

    • Hi Robert, we're sorry you feel this way. The purpose of the course is to show what is the right way to create a data model in order to simplify the DAX code required. Most of the DAX code shown in the course is actually what is required in case you do not have a proper data model. Once this first goal is achieved (understanding what is the right data model), then you have to transform the source data in case it doesn't fit the model. There are many tools you can use for that: SQL queries, ETL tools, Power Query (with M language). Sometimes you can use DAX, even though this is not necessarily the best tool for the job, maybe with the exception of some data model based on snapshot tables. Our experience is that knowing the right data model is a very useful skill for Power BI users, even though they don't know data tools and languages and don't want to / cannot spend time to learn them, because they can ask for the data in a proper format from someone else, maybe a co-worker in the company for example. If you want to achieve proficiency in data transformation, you need to use Power Query/M or SQL or other commercial ETL Tools (Informatica, Integration Services, Azure Data Factory, and many others). However, knowing these tools without having a goal (like the star schema we discuss in the course) could lead to models that solve the problem for a specific report and not for a generic semantic model, as a Power BI model can be. We made the choice to show some transformation in DAX because it was the easiest to use and deploy for the examples. The alternative would have required a more complex setup for the data sources, with the risk of losing time with configuration details while the focus should be on the concepts contained in the course. Please, contact us at info (at) sqlbi (dot) com if you want to provide additional feedback about the tools you would have liked to see to perform data transformation. We are always looking for feedback that can help us improve the content. We also hope that this explanation will help other readers that evaluate this course. Thanks!
      Reply by SQLBI (Jan 20, 2020)

Would you prefer a classroom course?

This video course is based on a classroom course we teach all around the world. If you prefer a classroom learning experience, take a look at the dates below for a list of our upcoming classroom courses!
 Sydney, NSW, AU Jul 22-24, 2020
Sydney
 New York City, NY, US Sep 21-23, 2020
New York City