Data Modelling Workout 01 - Star Schema

[Shidy]

Total Sales =
SUMX( Sales,
Sales[Quantity] * Sales[Unit Price] ) + 0

Total Costs =
SUMX( Sales,
Sales[Quantity] * Sales[Unit Cost] ) + 0

Canada Sales =
CALCULATE(
[Total Sales],
FILTER( ALL( Stores ), Stores[Country] = “Canada” )
) + 0

US Sales =
CALCULATE(
[Total Sales],
FILTER( ALL( Stores ), Stores[Country] = “United States” )
) + 0

Computer Sales =
CALCULATE(
[Total Sales],
FILTER( ALL( Products ), Products[Product Category] = “Computers” )
) + 0

Online Sales =
CALCULATE(
[Total Sales],
FILTER( ALL( Channels ), Channels[Channel] = “Online” )
) + 0

Blue Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(),
FILTER( ALL( Products ), Products[Colour] = “Blue” )
) + 0

Red Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(),
FILTER( ALL( Products ), Products[Colour] = “Red” )
) + 0

2007 Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(),
FILTER( ALL( Dates ), Dates[Year] = 2007 )
) + 0

2008 Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(),
FILTER( ALL( Dates ), Dates[Year] = 2008 )
) + 0

2009 Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(),
FILTER( ALL( Dates ), Dates[Year] = 2009 )
) + 0

2010 Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(),
FILTER( ALL( Dates ), Dates[Year] = 2010 )
) + 0![Workout 001 Power BI|690x460](upload://5Jf6wj0Iw7TRpweND488aESM8WX.png) [Data Modeling Workout 01 by Rasheed.pbix|attachment](upload://dWnLRKJIUFOZoCMHn8QNiFGp0mU.pbix) (219.7 KB)

[razn.jadee]

Workout1.pbix (220.5 KB)

Summary

Summary

Summary

This text will be blurred

[EricW]

Thank you for adding this workout! I hope this is an appropriate location for this question.
I have a data warehouse where I can load the equivalent of the flat file in this workout using a SQL query. Is there an advantage to loading a data query, and then building the lookups from the raw file versus loading only the transactional columns for a fact table and then loading separate distinct tables from the warehouse for use as lookups? What are the pros and cons to each approach (if any)?

[Greg]

Hi @EricW. The posting of new issues/questions to a solved thread is discouraged; additionally, many forum members only review unsolved threads.

@EnterpriseDNA, please extract this question into its’ own thread.

Regardless, for a small dataset like the one in this workout, I can’t imagine that you’d be able to see any data refresh performance difference with a SQL (or other data warehouse) connection. For larger datasets, then you always want to minimize the data loaded as much as possible, so if separate fact and dimension tables are available upstream of Power BI, all the better. Once the data is in Power BI (assuming “import” mode) and all transformations are done, report performance should be the same.

Greg

[KRashedi]

Hi everybody,
Please find my first Data Modeling Workout 01 below:

[anonymous311]

Hi All,
Here is my submission to my very first E-DNA workout.

[details=“Summary”]
StarSchema.pbix (245.2 KB)

Here is gif of working Power BI dashboard:

[/details]

[Elo]

Hello,
Here is my submission

[SarahH]

Hi,
My submission, some section’s I haven’t covered in the course as yet but have some knowledge of Power Bi already so managed to figure my way around the tasks.

Summary

[Sujan_Chhetri]

Hi @Greg - this is my first workout submission.

Power Query Pane screenshot

Data Model Screenshot

Data with slicer selections

Data with clearing all slicers selections

My power BI workout file
Data Modelling Workout 01.pbix (198.5 KB)

[iainmc_macca]

Hi, here is my effort. Really enjoyed it and found it so useful. Thanks.

Data Modelling Workout 01 - Star Schema.pbix (213.5 KB)

[Cambo]

Here is my solution.

Good first workout to put in to practice the lesson tought.

Cheers

[anonymous416]

Summary

Summary

Summary

Workout 01.pbix (358.6 KB)

[NatalijaPavlovic]

Hi, I am a new member and this is my first workout. Thanks for the great learning platform!
workout_01.pbix (226.6 KB)

[/spoiler][spoiler]

[spoiler]

[spoiler]

[spoiler]

[spoiler]

[/spoiler][/spoiler][/spoiler][/spoiler]

[delbert80]

Herewith my submission

Summary

Data Modelling Workout 01.pbix (235.8 KB)

[jeremy.lesch]

Thank you for my first workout!

[RebeccaChasarik]

Hi, I am very confused as to how to do this. Is there a video walk through or some place that the steps are outlined?

Thanks,
Rebecca

[maximillia76]

Hi All, I hope you all are doing well.

[JohnAdair]

Answer:
This is my first attempt. I wasn’t sure how to do this, so I followed Greg’s notes. I don’t understand what the + 0 is for in the measures. BTW, I also did this in Power Pivot just see what I needed to do to make it work.

[Greg]

Hi @JohnAdair. The “+ 0” in the measures is not at all necessary, just a quick-and-dirty way to ensure that everything is a number, even if blank. Greg

[JohnAdair]

Thanks @Greg. I left it off in my Power Pivot version of it, and it worked just as well. So basically, that’s the only reason I could come up with for the + 0. Have a great day.