The starting place to many successful Power BI data solutions is the star schema data model.
• create a relational data model from a flat-file data source
Produce a Power BI file that:
• creates fact and dimension tables from the flat-file sales data provided
• creates a [Dates] table from the flat-file dates data provided
• creates a [Key Measures] table to house the DAX measures created from the DAX data provided
• links the various tables into a full star schema using only 1-to-many relationships and a waterfall layout
• includes slicers, a table, and cards to showcase the star schema data model “in action” (i.e., while making slicer selections)
• use only the DAX code provided; do not modify nor create additional measures or calculated columns
• (data source: a 10,000 row subset of the Microsoft [ContosoRetailDW] sample database)
Using the only the data and DAX files provided, create your solution, and reply to this post. Upload the following:
• a screenshot of the queries pane of the [Power Query Editor] with all groups expanded
• a screenshot of the star schema data model as displayed in the [Model View]
• an animated GIF (or, alternatively, a set of screenshots) of your Power BI file “in action” (i.e., while making slicer selections)
• your PBIX file
Please blur your screenshots and GIFs or place them in a hidden section.
Thanks for participating! Good question about further normalization of dimension tables to snowflake schema. Generally speaking, in Power BI it’s best to stop at the star schema and not continue to higher order normalization.
Disadvantages of snowflake schema relative to start schema:
DAX usually becomes more complicated due to more complex filter context
Slicers become much more difficult to synch properly
Performance may decrease due to have to traverse longer paths
Cannot create heirarchies across multiple dimension tables
This question was actually the focus of Data Modeling Workout 003, in which you had to denormalize a snowflake schema INTO a star schema.
Nice and easy workout. Thanks for setting this up!
One could argue about a Geography table or combining the Store/Channel in one. IMHO even on simple models there are always things to argue about.
One thing I don’t understand is why in your solution file you didn’t clean up the Sales fact table.
In Power Query I added the index keys to my fact Sales table and then removed all the columns I modelled out to the dim tables. I was surprised you kept them all in. Slip of the pen or on purpose?