I have a dimension issue that I need help to resolve.
I have the following tables:
I want to group F_Contract based on ActivatedDate (in F_Contract). This is simple because I have a direct connection from ActivatedDate to D_date (this creates a ActivatedDate-dimension)
I want to group F_Case based on ActivatedDate (in F_Contract).
Here is my problem: I start of by counting F_Case but since ActivatedDate is a member of F_Contract I can't get to it.
Off course, I could be using D_contract to get to ActivatedDate, but I still miss the ActivatedDate-dimension from F_Contract...
It would be nice to be able to use the exisiting ActivatedDate-dimension from F_Contract...
Hope someone out there can help me?
Marius H Enerud
I think you should consider creating a fact dimension in this case. Fact dimensions, frequently referred to as degenerate dimensions, are standard dimensions that are constructed from attribute columns in fact tables instead of from attribute columns in dimension tables.
Here are some articles for your reference, please see:
Defining a Fact Relationship: http://technet.microsoft.com/en-us/library/ms167409.aspx
How to design a degenerate dimension for a fact table with million rows: http://social.technet.microsoft.com/Forums/fr-FR/6daa477d-ebbb-4d9f-8a03-4bd45d8f7ef1/how-to-design-a-degenerate-dimension-for-a-fact-table-with-million-rows
TechNet Community Support
Thanks for you tips!
I didn't quite understand how to use your suggestions in my example, but I seem to have come to a possible solution. But I need to know if this is a "Right" thing to do:
I have manually created a new dimension consisting of F_Contract og D_Date tables.
Here is the attribute relationships to achieve the dimension hiarcy:
The dimension hiearcy:
Now I should be able to use this dimension on every Fact-table that have a reference to Contract.
But again: I need to know if this is a decent way of doing it. What is the "cost" of doing it this way?