I'm working on modeling a star schema which has related facts and ultimately will be turning it into a cube. I want to make sure I'm thinking
about this right.
- 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 "sometimes" relationships I've made a few differen" 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 think the best option here is to actually calculate the measures on the fly in the cube opposed to materializing them.
For example one of the metrics is the duration from the open date on the product inquiry to the close date on the complaint. When the complaint is still open they want the duration from the product inquiry open date to the current date, and when it closes
it natrually switches.
Does anyone see any glaring issues with these approaches?
Related Fact Table Design
