Modeling Related Fact Tables
Page 1 of 1
Modeling Related Fact Tables
I'm working on modeling a star schema which has related facts and ultimately will be turning it into a SSAS cube. I want to make sure I'm thinking about this right.
Atleast that is the general idea, because of the wierd sometimes relationships I've made a few "different" design decisions around some of the data which would traditionally be a degenerate dimension for the ability to more easily tie the measure groups together in the SSAS cube. There is likely a little more cost during the ETL process, but my thought is the cube will process more quickly and will be a more user friendly experience.
The other interesting thing is I have some measures that are calculated accross fact tables when different facts are in different states, I've opted to keep those fact values as NULL until the records are in the correct state to not throw off aggregations and allow SSAS to handle the nulls appropriately (as nulls instead of 0).
Does anyone see any glaring issues with this approach?
- Everything starts off with a product inquiry (factProductInquiry)
- Some inquiries spawn a complaint (factComplaint)
- Some complaints spawn an investigation (factInvestigation)
- Some complaints spawn a recall (factRecall)
Atleast that is the general idea, because of the wierd sometimes relationships I've made a few "different" design decisions around some of the data which would traditionally be a degenerate dimension for the ability to more easily tie the measure groups together in the SSAS cube. There is likely a little more cost during the ETL process, but my thought is the cube will process more quickly and will be a more user friendly experience.
- I created a dimension to store information on product inquiries (dimProductInquiry) including the product inquiry id and short reason for the inquiry. It's a small dimension which as I mentioned before I typically would make a degenerate, however in this case it's an attribute on all four fact tables.
- I did the same for complaints (dimComplaint) and the dimension is related to factComplaint, factInvestigation, and factRecall
- For factInvestigation I did the more traditional approach and made the investigation id and short descriptions degenerates
- And did the same for factRecall
The other interesting thing is I have some measures that are calculated accross fact tables when different facts are in different states, I've opted to keep those fact values as NULL until the records are in the correct state to not throw off aggregations and allow SSAS to handle the nulls appropriately (as nulls instead of 0).
Does anyone see any glaring issues with this approach?
Last edited by amattas on Mon Jul 09, 2012 10:04 pm; edited 1 time in total (Reason for editing : Added comment about null measures)
Re: Modeling Related Fact Tables
Another thought is I also have with the "process sensative" measures is to make them calculated measures in the cube that way I'm not updating fact records.
Similar topics
» use of degenerate dimension to physically join two logically related fact tables
» Modeling Fact tables for a Hierarchy
» modeling multiple fact tables
» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer
» Modeling deeply related dimension
» Modeling Fact tables for a Hierarchy
» modeling multiple fact tables
» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer
» Modeling deeply related dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum