Related Fact Table Design RRS feed

  • Question

  • 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?

    Wednesday, July 11, 2012 2:02 AM

All replies

  • Hi Anthony, 

    Thank you for your question.  

    I am currently looking into this issue and will give you an update as soon as possible. 

    Thank you for your understanding and support. 

    Bin Long

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Bin Long

    TechNet Community Support

    Thursday, July 12, 2012 10:25 AM