Is it possible to have a parent-child dimension which is referenced from the fact table via an integer key but shows a string key in it's MDX unique member name
Here is the background to my question:
I have a dimension with a single parent child hierarchy. The key column is test_key and the parent column is test_key_parent, both of type integer. This works beautifully except for the MDX member names which are similar to "[Dim Test].[Hierarchy].&[10]". This project has a requirement that the member unique names are readable so this is not acceptable for me.
My next approach was to add a couple of string columns: test_code and test_code_parent. I then set the KeyColumns property for test_key to be test_code and the KeyColumns property for test_key_parent to be test_code_parent. Looked promising but unfortunately this was a dead end as it meant the fact table would require a column for test_code, not test_key (this is not desireable but if it is the only way, I will do it).
Next I tried to set the usage and make the test_code the Key and test_code_parent the Parent Key. This looked extremely promising as I could still use test_key as the granularity attribute so long as it was and Attribute relation to test_code. The cube even processed but alas the fact values did not aggregate correctly (every member showed the sum of all members). I can't see why this should not work. The granularity attribute is related to the Key attribute as required and all processes well.
Anybody with any tips / experiences in this area, I would love to hear from you.
Parent-child hierarchy: how to get readable member unique names?
Bochica
Thanks for the answer Deepak, thats excellent. Almost exactly what I need.
I say almost because for all the non parent-child dimensions, we have a Code & Description set up. That is, each level of the hierarchy has two columns, one like "test_code" and one like "test_name". The _code column contributes to the hierarchy but it's NameColumn property is set to the matching _name column. This setup gives us the ability to have a standard set of names (ie the same across each implementation) in the MDX and an entirely different set of names visible to the user. So the reason I was posting here was because I would like to achieve the same thing using parent-child hierarchies. Currently it looks to me like the only way is to reference the actual _code column from the fact table, as outlined in my previous post.
Don't suppose you can think of anything else
Thanks
8lu8llz
If you take the Adventure Works Organization Parent-Child dimension, and change its MemberUniqueNameStyle property to NamePath, then the Unique Names shown in the MDX Query Builder by dragging a member should show a path with names:
http://msdn2.microsoft.com/en-us/library/ms174900.aspx
>>
MemberUniqueNameStyle
Determines how unique names are generated for members of hierarchies contained within the cube dimension. This property can have the following values:
Native
Analysis Services automatically determines the unique names of members. This is the default value.
NamePath
Analysis Services generates a compound name consisting of the name of each level and the caption of the member.