Forum Discussion
Post ProdigyDimension Vs Snap shot table question
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'.
Most Valuable ProfessionalHi 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'.