Slowly Changing Dimension Type 2

turnbullca
Posts: 49
Joined: Wed Mar 23, 2016 8:24 am

Slowly Changing Dimension Type 2

Postby turnbullca » Sat Jan 06, 2018 3:52 am

Hi All

In the attached Power BI file there is a Query called StudentTeachingGroups which processes the 6 queries one after another and checks for student class changes. If there is a change it adds an end date to that record.

The query is a an SCD Type 2 Dimension that process 6 queries one after another and gives me a table that records changes of when Students Change Class. It works as is but I am sure if I knew what I was doing I could create a function that a gave me 1 query string (there are 6 queries at the moment and I must be able to do this on the fly) and I could have one process in StudentTeachingGroups instead of repeating the same process over and over.

Hope this makes sense.

I appreciate any help.

Chris
Attachments
TeachingGroupSCD2.xlsx
(12.91 KiB) Downloaded 79 times
SCD Type 2 Forum.pbix
(265.81 KiB) Downloaded 74 times

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Slowly Changing Dimension Type 2

Postby MattAllington » Sat Jan 06, 2018 7:21 am

Have a read of my blog article on Combining Excel workbooks with Power Query.


The principle you need is the same as outlined.

Matt
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

turnbullca
Posts: 49
Joined: Wed Mar 23, 2016 8:24 am

Re: Slowly Changing Dimension Type 2

Postby turnbullca » Tue Jan 09, 2018 9:17 pm

Hi Matt

Thanks for the reply.

I have worked through the example a number of time but I don't think that is what I am trying to achieve. I realise it might be the way I have explained my issue so I will have another go.

The process I am currently using does this:-

let
Source = #"01 - StudentTeachingGroups",
#"Append-Collection02" = Table.Combine({Source, #"02 - StudentTeachingGroups"}),
----------- Process Query for Collection 2 ---------------------------
#"Append-Collection03" = Table.Combine({#"RemoveAllButCurrent-02", #"03 - StudentTeachingGroups"}),
----------- Process Query for Collection 3 ---------------------------
#"Append-Collection04" = Table.Combine({#"RemoveAllButCurrent-03", #"04 - StudentTeachingGroups"}),
----------- Process Query for Collection 4 ---------------------------
#"Append-Collection05" = Table.Combine({#"RemoveAllButCurrent-03", #"05 - StudentTeachingGroups"}),
----------- Process Query for Collection 5 ---------------------------
#"Append-Collection06" = Table.Combine({#"RemoveAllButCurrent-03", #"06 - StudentTeachingGroups"}),
----------- Process Query for Collection 6 ---------------------------
#"RemoveAllButCurrent-06"

What I actually want the Function to look like is:-

Source = #"01 - StudentTeachingGroups",
#"Append-Collection" = Table.Combine({Source, #"StudentTeachingGroups"}),
----------- Process Query for Collection ---------------------------
LOOP TO APPEND UNTIL NO COLLECTIONS AND THEN MOVE ONTO TO CLEANING THE QUERY

Hope this makes sense

Chris

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Slowly Changing Dimension Type 2

Postby MattAllington » Wed Jan 10, 2018 3:01 pm

OK, so this is more like this video I produced some time ago https://www.youtube.com/watch?v=xN2IRXQ2CvI

I have had a go but couldn't get it to do what I needed. Sorry, I am out of time. This may be a good one for Imke. I will ask her if she has time to take a look.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

turnbullca
Posts: 49
Joined: Wed Mar 23, 2016 8:24 am

Re: Slowly Changing Dimension Type 2

Postby turnbullca » Wed Jan 10, 2018 5:57 pm

Hi Matt

Thanks for having a look.

Don't ever apologise you do an amazing job.

I use the offset Index method in my solution but I have to check Next and Previous and the code works how I want it. I just want to turn it into a reusable function or sub routine that I can call and feed it the CollectionID.

Hopefully Imke will have the time to have a look, if she needs anything or needs to contact me to clarify anything she can contact me directly.

Thanks

Chris

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Slowly Changing Dimension Type 2

Postby MattAllington » Thu Jan 11, 2018 7:11 am

OK, it came to me when I was taking a walk late yesterday. I have used the technique I posted in my video above twice. The first time I used it to map the current record to the next record and remove any class records that didn't change (this what the problem I had yesterday). After removing these superfluous records, I used the same technique to map the current record to the previous record to bring in the end date. If you watch my video and then step through this query you will be able to see what I did. Interestingly, I got a different answer - I think yours is wrong :-)

SCD Type 2 Forum.pbix
(272.15 KiB) Downloaded 75 times
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

turnbullca
Posts: 49
Joined: Wed Mar 23, 2016 8:24 am

Re: Slowly Changing Dimension Type 2

Postby turnbullca » Thu Jan 11, 2018 10:24 pm

Cheers Matt

I have implemented the Solution and all looks good so thank you very much. I can use this pattern for many dimension tables that I have so double woop woop. I did have a strange sorting issue but have resolved that.

One last question to do with SCD Dates - I need to assign the TeachingGroupID to the Results FACT table to show which TeachingGroup the Student was in when the assessment took place. Have you come across any videos or blogs that show how to merge 2 queries based on a date to date range. I may actually be better of using a Calculated column using the Lookup command but if there is another way with M I would prefer that.

You have made me realise that my problem is that I do a morning walk in very cold conditions. :D

Once again thanks a lot you are amazing

MattAllington
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Slowly Changing Dimension Type 2

Postby MattAllington » Fri Jan 12, 2018 8:17 am

I'm not really sure what you mean wrt the new data. I assume you are saying your fact table will have a single date column and somehow it needs to map to the start/end dates your SCD. Is that correct? If this is correct and you want to load all these SCD records to your datamodel, then the SCD is actually a data table (I assume). But you know what you want to do better than me.

Have a look at these blog articles I wrote and see if it helps.

https://powerpivotpro.com/2016/12/how-m ... -off-work/

As for using PQ, I can't really say without seeing the data
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest