Module 1: Introduction to Data Modeling
- What is Data Modeling?
- Analytical limits
- Increasing the analytical power
- Introducing the facets of data modeling
- Leveraging the data model
- Normalization and denormalization
- Facts and dimensions
- Introducing star schemas
- Chains of relationships
- How many dimensions?
- Why data modeling is useful?
- Lab 01 – Basic data modeling
Module 2: Header / Detail Tables
- Introduction to header / detail schemas
- Bidirectional filtering is not the way!
- Denormalizing the discount
- Back to star schema
- Lab 02 – Header / detail
Module 3: Multiple Fact Tables
- 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 03 – Multiple fact tables
Module 4: Working with Date and Time
- Date attributes in the fact table
- Building a date dimension
- CALENDARAUTO function
- The model with a date dimension
- Automatic date grouping in Power BI
- Quick calculations in Power BI Desktop
- Disable automatic date columns
- Handling multiple dates
- Multiple date tables
- Multiple date tables with multiple fact tables
- 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 in the model
- Non-overlapping special periods
- Overlapping period measure
- Lab 04 – Working with date and time
Module 5: Tracking Historical Attributes
- Attributes change over time
- Handling variations over time
- Slowly changing attributes or dimensions
- Rapidly changing dimensions
- Attributes in the fact table
Module 6: Using Snapshot Tables
- What is a snapshot table?
- Sales versus inventory in the same model
- Non-additive measures
- LASTDATE function does not work here!
- Optimizing performance
- Snapshots and granularity
- Transition matrix
- Parameter tables
- Lab 05 – Snapshots
Module 7: Analyzing Date and Time Intervals
- 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
- Open orders in a snapshot table
- Events with different durations
- Daily salary in DAX
- Precompute the values
- Lab 06 – Date and time intervals
Module 8: Many-to-Many Relationships
- What are many-to-many relationships?
- Possible solutions to this complex scenario
- Bidirectional filtering
- Using CROSSFILTER function
- Using expanded table filtering
- CROSSFILTER versus expanded tables
- Understanding non-additivity
- Cascading many-to-many
- Lab 07 – Many-to-many relationships
Module 9: Working with Different Granularities
- Dimensions define granularity
- Analyzing budget data
- Reduce granularity on all the tables
- Using DAX to move the filters
- Filtering through relationships
- Using the correct column to slice
- Leveraging relationships
- Checking granularity in the report
- Hiding or reallocating?
- Lab 08 – Granularity
Module 10: Segmentation Data Models
- What are segmentation models?
- Static segmentation
- Dynamic segmentation
- ABC and Pareto analysis
Module 11: Working with Multiple Currencies
- 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
