Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
cottrera
Post Prodigy
Post Prodigy

Dimension Vs Snap shot table question

Hi 

 

I am sruggling with the consept of our new data warehouse team and approach. The legacy warehouse used to produce me a daily snapshot report that pointed to our outstanding repairs table which looked at all open repairs and whether they were awaiting scheduling or were scheduled. This provided me insight into working progress overtime. 

 

Our new warehouse contains an Dimension outstanding repairs table that appplies a new row if the status has changed and has a start and end date of these changes.

 

Where as the Snapshot would have continuous daily added rows for a repair for the duration that it remained outstanding

The Dimention table only has an entry (row) if the status changes from  awaiting scheduling or scheduled

 

This means the Dimention table looks more like this. Jobs with dates spread out based on status changes

Job#Dim DateStatus
101/01/2024Awaiting Shed
104/01/2024Scheduled
118/01/2024Awaiting Shed
119/01/2024Scheduled
201/01/2024Scheduled
218/01/2024Awaiting Shed
219/01/2024Scheduled
301/01/2024Scheduled
318/01/2024Awaiting Shed
312/02/2024Scheduled
401/01/2024Awaiting Shed
403/02/2024Scheduled
501/01/2024Awaiting Shed
506/01/2024Scheduled
513/01/2024Awaiting Shed
521/01/2024Scheduled
525/01/2024Awaiting Shed
530/01/2024Scheduled


As opposed to the snapshot table which looked like this. An entry even if the status did not change

Job#Date Added to snapshotStatus
101/01/2024Awaiting Shed
102/01/2024Awaiting Shed
103/01/2024Awaiting Shed
104/01/2024Scheduled
105/01/2024Scheduled
106/01/2024Scheduled
107/01/2024Scheduled
108/01/2024Scheduled
109/01/2024Scheduled
110/01/2024Scheduled
111/01/2024Scheduled
112/01/2024Scheduled
113/01/2024Scheduled
114/01/2024Scheduled
115/01/2024Scheduled
116/01/2024Scheduled
117/01/2024Scheduled
118/01/2024Awaiting Shed
119/01/2024Awaiting Shed
120/01/2024Awaiting Shed
121/01/2024Awaiting Shed
122/01/2024Awaiting Shed
123/01/2024Awaiting Shed
124/01/2024Awaiting Shed
125/01/2024Awaiting Shed
126/01/2024Awaiting Shed
127/01/2024Awaiting Shed

 

My question is that I need to chart the outstanding repairs over time using a line chart.  With the snapshot table I was able to do this fine as the dates were continuous.

 

However now that IT have introduced a Dimention table that has entries only when the status changes it creates lots of oddspikes in the line chart as the dates are not continuous.  Just to add , I do use a dates table.

 

Should I do one of the following

 

1- Ask the data warehouse team to start build a table to produces a snap shot of the outstanding repairs table

2-Or is there a DAX function that would solve the problem. We will be running the DAX over 250K -1 Million rows.
thank you Richard


1 ACCEPTED SOLUTION
Jonvoge
Resolver V
Resolver V

Hi Richard.

 

I think the first thing you need to consider, is whether "outstanding repairs table" is truly a dimension table, or whether it is in fact a fact table.

 

The first scenario, with the status changes, sounds a bit like a Type-2 SCD (Slowly Changing Dimension). But, this would only be the case, if all Jobs are listed in the table at all times.

 

The second scenario, a snapshot table, is actually a type of fact table.

In general, it will be way easier to accomplish your calculation using a Fact Table, such as your proposed snapshot table, and if it not too much a hassle to ask that of your data warehouse team, that would be my best practice suggestion.

 

The first table might work. But only if you make it into a true dimension table in which all Jobs are always present. In that case, you could probably get away with using the methode described in this blog post, about calculating "Active Items over time" in a Type 2 SCD Dimension: DAX formula to obtain the count from SCD type-2 dimension records – Simple SQL BI blogs (home.blog)

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.

View solution in original post

2 REPLIES 2
cottrera
Post Prodigy
Post Prodigy

Hi Thank you for you quick reponse and links Richard

Jonvoge
Resolver V
Resolver V

Hi Richard.

 

I think the first thing you need to consider, is whether "outstanding repairs table" is truly a dimension table, or whether it is in fact a fact table.

 

The first scenario, with the status changes, sounds a bit like a Type-2 SCD (Slowly Changing Dimension). But, this would only be the case, if all Jobs are listed in the table at all times.

 

The second scenario, a snapshot table, is actually a type of fact table.

In general, it will be way easier to accomplish your calculation using a Fact Table, such as your proposed snapshot table, and if it not too much a hassle to ask that of your data warehouse team, that would be my best practice suggestion.

 

The first table might work. But only if you make it into a true dimension table in which all Jobs are always present. In that case, you could probably get away with using the methode described in this blog post, about calculating "Active Items over time" in a Type 2 SCD Dimension: DAX formula to obtain the count from SCD type-2 dimension records – Simple SQL BI blogs (home.blog)

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.