I am fairly new to OLAP and SSAS but seasoned in relational data wharehouses, and my question about Reference Dimensions is are they bad, a necessary evil, or useful when used correctly? Every single post I can find references Andventure Works and the Geography dimension, but I am looking for real world experience.
My cube has a date dimension that is pretty standard, and I want to create a date metrics REFERENCE dimension that has has a FK DateId to my Date Dimension. Inside of this Date Metrics reference dimension I will add a member for AccountId and several "Action" members to summarize specific actions that I want to count by date, month, or year etc.
At the root of it my date metric reference will be uniuqe on the DateID AND the AccountId which will enable me to summarize "action" movement by the date dimension I am trying to relate it back to.
Do I Have this all wrong?
Reference dimensions: means how you reference a dimension to a cube. When dimensions are created, they exist on they own, you add them to a cube on the "Dimension Usage" tab. This is necessary to be able to browse the cube's data using the dimension.
I think you are actually asking about "attribute relationship" (second tab on the dimension configuration), and the answer is they are extremely useful. I even saw a video once with a Microsoft MVP and he was saying that its probably the most important configuration you can do on your cube.
The attribute relationship indicate how the attributes on a dimension relate among themselves. So, for example, on a date dimension you will have
day -> month -> quarter -> year
its always the "opposite" configuration as if it were an hierarchy.
Another very important configuration is the relation type, which in the date example, you should set it to rigid, because the data will never change (the 01-01-2012 member will always belong to the 2012 year) so SSAS will maintain the calculated aggregations when you process the cube (unless, of couse, you do a full processing)