Build a robust data model from a data warehouse: presentation outline
Here’s an outline of the presentation I’m giving today at an internal conference at Oracle Cerner.
about me
Four basics of Power BI: DAX, data modeling, Power Query, visualization
Power BI is a model-based tool (thanks for
Attempt 1: flat files in Power BI: recreate limits of report-based tools
Flat files in Power BI Desktop
Ideal model: star schema: dims connected to fact
Star schema in Power BI Desktop
Facts & dimensions: totals & aggregations and grouping by attribute
Star schema in Power BI Desktop: Pros/Cons (model based benefits)
Power BI & Data Warehouses both used facts & dimensions…
Attempt 2: recreate data warehouse structure in Power BI
Flat files to star schema made easy (not)
Section: A field guide to data warehouses
Best practices for dataset tables & columns: just what you need
Section: Fact Tables
One fact table star schema
Multiple fact tables via shared dimensions (not directly)
Header/detail tables (combine)
Section: Junk Dimensions
Junk dimensions for attributes without dimensions
Two ways to handle junk dimensions: join or keep separate
Join junk dimension with fact: no grouping benefit, big dims are slower, simpler model
Keep separate: junk dim useful for connecting dependent facts
Keep Separate: cardinality will be 1:1 (change cardinality to *:1
Example of junk connecting fact and 2 dependent fact tables
Special case: mini data warehouses that don't have date dims or dims from other mini-dws
Special case: Must get dimension keys onto the fact table
As a data modeler: you have the power to organize data into a model
Section: Dimensions
Date dimension
Avoid snowflake dimensions
Combine cascading dimensions into one
Role playing dimensions Option 1: active/inactive relationships
If dim isn't a unique list, you can set filtering so dim filters fact only
Historical dims: to see changes is easy but to filter by departmentt of person at time of work needs big changes. Ideally, historical departmentt is on the fact table.
Key takeaways: star schema, date table, unidirectional relationships, only what you need
Resources: Microsoft, SQLBI, RADACAD, my blog
Get Certified in Power BI!
Unlock the Power of Power BI: model-based tool, powerful reports which respond quickly, empower exploration and new insights