Data Modeling for Power BI Video Course
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 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
-
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
-
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
-
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
- 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
-
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
-
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
-
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
Training Platform
This course is conducted through our training platform which is immersive and designed to facilitate your experience.
- Steven Fortier (Mar 17, 2019)
Wow, this course was so amazing. Marco and Alberto are really good teachers and I recommand taking this course together with the other other one Mastering Dax. The only complain I might have is there's no chapter on handeling different langage in reports. Like if I want to show a reports to my customer in Europe and he talk spanish and the othe one wants it in French. I know there isn't a proper way to do it in Power BI but maybe talk about different possibilities and what are the possible solutions. Except that, everything is well covered. Thank you!
- – That is not a limitation of Power BI and the Tabular modeling where we do not have a good solution yet. The Tabular model supports translations of metadata only, but there are not good solutions for translating entity names (e.g. product names) or report labels. You can figure out workarounds, but any solution has implementation and execution costs - so a feature in Power BI to manage those scenario is really required.
Reply by SQLBI (Mar 18, 2019)
- – That is not a limitation of Power BI and the Tabular modeling where we do not have a good solution yet. The Tabular model supports translations of metadata only, but there are not good solutions for translating entity names (e.g. product names) or report labels. You can figure out workarounds, but any solution has implementation and execution costs - so a feature in Power BI to manage those scenario is really required.
- Jordi Andres Aguiñaga Gonzalez (Feb 21, 2019)
Great course, I really enjoyed it! I'll continue with the DAX advanced course! Regards!
- Eva Vackova (Feb 5, 2019)
Thanks Marko & Alberto for organizing the course. Best practices and scenarios solved during the course are often daily on tables of the data analysts. I enjoyed the course a lot and still need to go back tor review some more complex scenarios :)
- Yusuf Hamzah (Dec 23, 2018)
Outstanding materials. The contents are "meaty" and of high-quality.
- Enrico Vezzalini (Aug 20, 2018)
congratulazioni, il corso è molto ben fatto ed efficace! Se posso, vorrei aggiungere 2 suggerimenti: - sarebbe utile pensare ad un nuovo modulo orientato alla gestione e controllo dei modelli e del flusso dei dati in un'organizzazione complessa in relazione ai vari modelli di deployment - sarebbe molto interessante apprendere quali soluzioni si possano sviluppare mediante il data modeling per facilitare l'applicazione di tutti i requisiti di privacy by design e di compliance control nella creazione di una piattaforma di BI aziendale (sempre in relazione ai modelli di deployment di cui sopra ed all'utilizzo o meno dei filtri di confidenzialità di Power BI)
- Maria Luz Munoz (Aug 8, 2018)
It gave me the confirmation of thoughts and also gave me new ideas. I thank you for publishing this good course.
- Bill Brown (Jul 27, 2018)
Now have a much better understanding of data modeling.
- Emmanuel Dubosson (Jul 13, 2018)
after using Power Pivot for some years, I could with this course improve my data modeling skills to the next level. The core principles explained in the sections are good understable. Some topics have some (DAX) magic in it. I appreciated the ways proposed in many examples to simplify the data model. Doing it right, it's much more easier and performant. Thanks a lot! With this video course, I'm just only missing eating a pizza with Alberto or Marco.
- – Thanks!!
Reply by SQLBI (Jul 13, 2018)
- – Thanks!!
- Eduardo Rocha Clemente (Jun 24, 2018)
The last Module that I really have done the exercises was the "Analyzing date and time intervals". I think that the previous course that you advised me to do "Introducing Dax", the free one, was not enough to understand the solution that was depeloped in DAX code. I intend to do the "Mastering Dax" course, and do all the exercises again in this course to a better understanding . I feel that a roadmap to master DAX is needed
- – Thanks for the feedback!
Please, keep in mind that the goal of the Data Modeling for Power BI course is to teach how to set the goal in designing the data model, not in implementing the transformation. The way you have to consider the exercise is in practicing the definition of the data model. It would be better to use SQL or Power Query (M) to obtain such a result. For practical reasons, we used DAX to accelerate the creation of the required table, otherwise the course would have dedicated 70% of the time describing how to use SQL and/or Power Query, with a more complex setup for the exercises required.
Reply by SQLBI (Jun 24, 2018)
- – Thanks for the feedback!
Please, keep in mind that the goal of the Data Modeling for Power BI course is to teach how to set the goal in designing the data model, not in implementing the transformation. The way you have to consider the exercise is in practicing the definition of the data model. It would be better to use SQL or Power Query (M) to obtain such a result. For practical reasons, we used DAX to accelerate the creation of the required table, otherwise the course would have dedicated 70% of the time describing how to use SQL and/or Power Query, with a more complex setup for the exercises required.
- Hana Palkova (May 10, 2018)
Many interesting new insights. Thanks Marco & Alberto!
- Paulo Rocha (May 7, 2018)
Wonderful! SQLBI, Marco and Alberto not only are the best in the field, bust also know how to share their knowledge. This approach of focusing in the Data Modeling and making more efficient DAX expressions takes us to a whole new level in Business Intelligence and Big Data. I'm very pleased with the content and the professional way everything was presented. Keep up the excellent work.
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!
|
Jul 19-21, 2021 |
|
Oct 13-15, 2021 |