Data modeling is a required skill to get the best 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 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 course is made of more than 10 hours of lectures, and other 8 hours of individual exercises. You can watch the videos at any time and the system will keep track of your advances. Within the 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.
Presentation of Data Modeling for Power BI
Presentation of Data Modeling for Power BI FREE
- Presentation of Data Modeling for Power BI
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
- How to download and complete exercises
Introduction to data modeling
Introduction to data modeling 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
- Introduction to data modeling
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
- Header / detail tables
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
- Multiple fact tables
Working with date and time
Working with date and time FREE
Date attributes in the fact table FREE
- Building a date dimension
- 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
- Working with date and time
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
- Tracking historical attributes
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
- Using snapshots
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
- Analyzing date and time intervals
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
- Many-to-many relationships
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
- Working with different granularities
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
- Segmentation data models
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
- Working with multiple currencies
- Ivon Ampuero (Dec 4, 2019)
- Richard Valdez (Nov 18, 2019)
This is a fantastic course!! I am studying this course concurrently with the Mastering DAX course and I am finding it quite useful to go back between both courses. I am eager to continue in this wonderful journey of learning the DAX language and applying it to my work.
- Robin Neven (Oct 25, 2019)
Very good: clear explanations and examples, though harder than I had expected. But that's good because it means there was a lot to learn! Minor point of improvement: sometimes the assignments weren't totally clear to me. In those cases it started with a description, but never really asked a question/gave an assignment.
- Lucas Minikoski (Oct 24, 2019)
Thank's, this course leveled me to another way of "DAXing". Now I feel better when I open the PBI and start measuring. Thank's a lot!!!!
- Abhijith DSouza (Oct 16, 2019)
Thank you Marco and Alberto for a wonderful course. A lot of real world applications were covered in the course and also different techniques to solve them. I particularly enjoyed the techniques which we shouldn't use and the reasons for not using them. Highly recommended for anyone interested in an in depth analysis for Power BI data modeling.
- Claudio Trombini (Oct 14, 2019)
Best course of Data Modeling!!!
- Thomas Allan (Oct 7, 2019)
A thorough review of model scenarios (simple to complex) covering date and time, historical attributes, snapshots, intervals, many-to-many relationships, differing granularities, segmentation, and multiple currencies.
- Luigi Bissolotti (Oct 2, 2019)
lessons and examples are very clear discussion sections are very useful
- Andreas Ratz (Aug 22, 2019)
Again a perfect course. Very useful are the patters provided. So all you have to do is to identify the right pattern and apply ;-)
- Sergio Murru (Aug 15, 2019)
This course covers different scenarios, from the simplest to the most complex ones, using them as examples to give a clear explanation of the different techniques that can be used when building the data models for our reports.
- Ciro Gómez Parssian (Jul 13, 2019)
Excellent and astonishing course.
- harm bons (Jun 25, 2019)
Course was good.
- Marcelle Georgiev (Apr 30, 2019)
Thank you! Excellent!
- John Mathews (Apr 4, 2019)
Excellent course and presentation, especially with the (deliberate) mistakes and interactions. The row explosion implications of (S/F)CD-Type2 were eye opening. Would like to see some of the scalability modelling issues discussed such amount splitting by order of magnitude, albeit you did mention date/time splitting, and how patterns change to deal with it. Now it's time to rip up my model and do it properly!
- Donald Wiesmann (Apr 4, 2019)
Thank you very much, very good in depth explanations, shows in a detailed way philosophy and specialities for data modeling with DAX in Power BI with still following the Kimball method.
Do you prefer a course in classroom?This video course is based on a live, classroom course we teach all around the world. If you prefer a live learning experience, take a look at the dates below for a list of our upcoming events!
|New York, NY, US||Apr 15-17, 2020 New York|
|Amsterdam, NL||Jun 3-5, 2020 Amsterdam|