When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.
The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.
Any ideas?
CLG3,
I too have been trying to solve this issue. It seems that using a non-key granularity attribute to link to the measure group was never intended to work with Parent-Child dimensions. Like you say, doing this causes conflicts with the restrictions on the dimension's attribute relationships.
What I have done solves the issue but it is a really nasty hack:
Alter your Fact table to store the dimension's Parent-Child key rather than the dimension's primary key. In the dimension usage tab, link to the measure group via the Parent-Child key rather than the dimension's key.|||Is there a reason preventing you from using the dimension primary key as the member key, even if it means having to add a new parent key onto the dimension?|||Hi John,
I describe the reasons I need the member key to be different to the primary key in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1635466&SiteID=1
My latest approach is:
Use the dimension's primary key to reference the dimension from the fact. Create a view on the fact that joins to the dimension and get's the dimension's "Business Key" (which is what I use as the member key for the dimension) Build the Measure Group on the fact view rather than the actual fact.