Working with Slowly Changing Dimensions in Power BI

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.

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.

Does the use of views rather than straight tables not break direct-query models?

What if you don't have the surrogate key in the Fact table? How do you do the join? As far as I can tell, you cannot do joins in PowerBI on GREATER/LESS than?

The title is wrong it is not "SCD IN PB" it is "SCD IN SQL". All the issue which pop up with SCD was solved in the source. I have a project where the source is excel, and no chance to make changes before porwer bi. :(

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 extreeeeeeeeeeeeement lonnnnnng, 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.

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

Great work, thanks

I just messaged about this and this shows up. Not sure if i should be thankful for spying on my data or not

How do you handle SCD2 with Dataverse as data source?

I gave up mate. I will watch later.

Excellent video. I really liked the introduction and context you gave at the beginning of the video, and the way you explained things with clear examples. I'm doing a similar project, and wanted to try and create from source data, a collection of fact and dimension tables using Power Query. However one of the tables I need to create, (or which I think I should be creating, and would like to create) is a Type 2 - S.C.D.. But after doing some web research to find out whether this is possible, and reading some of the best practice documentation on STAR Data Models, on the official Power B.I. site, I've concluded that it's just not possible. So I think I'm going to have to create a dimension table whose primary key is just a composite key of all the attribute fields within it, and then link it the Dimension Table that way to the Fact Table. It would be good to understand though, how to do such a thing properly. But thanks for the video anyway.

Really great work! Excellent communicator.

why would you need to modify Fact to add new custom store surr key ,instead couldnt you handle in the measure by putting a Case statement ? also custom surr key is needed in dikension, i think we could live only with the Current manager flag ?

very funny man

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

Thank you very much, very helpful. I am struggling to find a good solution with scd2 and hierarchy with the path function. Do you have any suggestions?

Amazing!!!

Your Company consists of the regulatory and non regulatory business divisions for which segregated data sets are required due to compliance reasons but high level management, Finance & HR requires combined access to both the divisions. Some of the HR data elements like salary & personal information columns are required to be accessed by HR only. Further for operational reporting, line of business managers require access to their respective areas, general managers to their geographical regions, team leads to their teams and personnel to their own activity reports.
Considering above organizational structure and requirements what are the security models and methods you will suggest to apply for securing reporting and data access?

Anyone have an answer for this?

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

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"

that's amazing

Yaa Patrick, super video and a great help, where do we get the code or resources for this please.

Same video but for Tableau please?

That's again foking bananas :)))

it is new option in power bi But i didn't able to understand. I had seen SCD in SSIS . I was worked on that. It's simple to understand. This is little bit confusion.

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.

You are awesome

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 👍👍👍

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

I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.

great!! and function is littlcel compliacated but amazing

Interesting

nice video. Btw does anyone know why we are still missing Power BI update from last month?

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

Hello Patrick, i just want to mention that for SCD2 (historical) the start date and end date is overlapping. What i mean is that if first manager Adam EndDate is 20210116 for the new manager Patrick it has to be from the next day. I guess you cannot be managers in the same time both of you. Imagine that you query the manager in act for the 20210116 - the result will be both of you - if you query on a range of dates - for example - "select Manager from table where today() between StartDate and EndDate" - and today is 20210116. Anyway, nice videos and useful aswell, great job!

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?

Thanks! It will come handy for me to track the employees moving from one team to another .)

Thanx Patrick !!

Patrick, you nail it !!!!

What if I really really don't want to add another column to my fact? My fact table is already massive as is.

How upset would you be if I kept my SCD as a table related to the fact using a surrogate key, but then calculated my 'current' dimension (current indicator = 1) as an additional table to add to the model as a snowflake? I would  use the SCD alternate key to relate to the (now primary key of the) 'current' table snowflake. Then users that need point-in-time reporting can use the SCD table, and users that need current reporting can use the 'current' table, which will push the alternate key filter from the 'current' table to the SCD which will push the surrogate key filter to the fact.

Again, I know that in ideal world we should avoid snowflakes, but this seems like the most straightforward way of accomplishing it without monkeying my fact. Thoughts?

Really nice video! and just wonder if anyhow Guy in a Cube could make more data transformation videos! Now, I am facing a data cleaning question, the business needs to match the first six characters of a data field to another one, after the comparing, the unmatched ones could be modified by hand directly. I have tried PowerBI to extract the characters and use the fuzzy match to filter out the values in data transformation, but then it seems like that Powerbi cannot change the original value, so does it mean the unmatched value has to be changed from the source?

great method to use ''userelationship()' function

Great video Patrick! As always thank you.:)

Thank you. Excellent video. Column masking based on user login. Any thoughts?

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.

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