Working with Slowly Changing Dimensions in Power BI

Do you have slowly changing dimensions in your Power BI dataset? Struggling to work with them to get the right values? Patrick walks you through how you can deal with them.

Wikipedia

Using calculation groups to switch between different dates (SQLBI)


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #SCD #GuyInACube

[evnpresson]

I literally was just talking to our data engineer about how to model with SCD2 data when I saw this video was posted. So timely!

[koenverbeeck]

Great video and interesting solution with the calculation group. What I typically do is add another column to the store dimension called "current manager". It has the advantage of not modifying the fact and not adding extra measures (or calculation groups). Users just have to remember to use the correct column to view the manager.

[BryanCampbell]

Probably one of the most useful videos you guys have done thus far... It'll be over the head of some folks, and it's a lot to take in, but you keep the length short enough that you can "skim watch" and then "study watch" later... Kudos 👍👍👍

[mytwospence]

Happy to see the Data warehouse toolkit getting a shout-out! Great book for building DW's.

[thomasivarsson]

It was fun to see how you can use Power Query here. I sent Parick some examples of how to solve this in a DWH with Anchor modelling that makes it possible to use several SCD-types. You can also avoid putting the manager in the same dimension table as the store and create two dimensions. This is easy and will record all changes but you need to do this in the ETL.

[mdhidayat]

Love this Patrick! Hope to see more DW concepts videos that can be used in PowerBI!
Thank you, as always :)

[rehanahmed]

I have implemented Scd using SSIS and that was quite easy to do.
Anyways thank you Patrick for the great video!
Surely I'll implement the same in Power BI too..
Cheers

[dizzyharris]

This was super helpful. I was able to apply this same sort of logic to a different report for "Total IT Tickets open more than 10 days, month over month"

[dirkuecker]

Thanks Patrick. I love your way of thinking in terms of where the changes need to happen. Depending on the requirements not all could be solved in the Front end tool, even if it is Power BI. Some things are better to be fixed in the DWH. I love your videos and would love to work with you in a project.

[Adam-hyou]

The steps taken to get the surrogate key into the fact table is the only thing missing. So adding the surrogate key based on the From and To dates in the SCD table. Lots of solutions via google but none particularly elegent.

[TheJustinjet]

Really great work! Excellent communicator.

[Negi_From_Pauri]

Dear Patrick bro you are the collest american power bi God.

[DaveRuijter]

Love the video, Patrick! In your query examples in the first minutes you're using StoreID where that should be StoreAK.

[dimitridelizee]

Amazing video. I can't download the examples. I have this message: Account is cancelled and can not accept new subscribers.
Can you make them available again. Thanks

[alexandermilland]

Would love a video about doing some other calculations, like how long was Adam manager of X store?
Especially for an example where you might have multiple columns you are doing SCD2 logic on.

[stephiesobgoum]

Merci beaucoup pour cette vidéo Patrick ainsi qu'à la team @GuyInACube
Moi j'ai une autre préoccupation toujours avec la gestion du SCD Type 2 dans POWERBI.

En fait, pour chaque dimension, j'ai un StartDate, un EndDate et un Statut (Actif / Expiré).
Je souhaite sur PowerBI disposer un filtre de date (DateExtraction): lorsqu'une date sera sélectionner, PowerBI devra présenter uniquement les données tels qu'elle se trouvait à cette Date.
Donc pour chaque Mesure dans le table de fait j'ai rajouter à la fonction calculate un filtre qui sélectionne une ligne lorsque la startDate <= DateExtraction < EndDate.

Mais le chargement est prêt de 5 min pour moins de 150 000 lignes.

En bref, je souhaite afficher les données sur PowerBI selon leurs exact au moment ou leurs insertion dans l'entrepôt de donnée a été éffectué.
Stp Patrick @GuyInACube, y'a t'il une meilleure façon de faire.

[fabiendelaloye]

Hi from Swizerland! Nice video!

Question or idea for a new video:
With SCD2, How to exploit the startDate and endDate (as shown in 4:17) in power bi ?

For example display a list of all active rows in a date (between startDate and endDate)
-> and, challenge... change the reference date with a slicer...

Bye
Fabien

[StuartGreenBinkyBMF]

Hi Patrick, how would power BI handle an SCD for trending changes, such as an item is either rented, unrented or being repaired? The SCD would log the times in and out of the shop, So you could trend over the year what status the item was in?

[nigeltufnel]

SCD, great for DW, challenge for BI. Great stuff here!

[SoheilBakhshinz]

Very good topic Patric.
Just one thing, in minute 2:51 you mention type 2 is over-write, I suppose you meant type 1 is over-write. So SCD0, fixed dimension, SCD1 over-write, SCD2 historical.