Friday, March 9, 2012

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

IDYearMonth08C5DC29-6E61-4625-BA20-CFDFE5D3232D2006Jan

KeyColumns is binded to Year/Month and so is NameColumn. The MDX representation of the year 2006 is now [Budgeting Time].[Budgeting Time].[Year].&[2006] but is there anyway to have the ID as the member identifier so the MDX looks like [Budgeting Time].[Budgeting Time].[Year].&[08C5DC29-6E61-4625-BA20-CFDFE5D3232D]?

If your entity can equally be uniquely identified either by ID or { Year, Month } pair you can choose ID to be the key column of your attribute. MDX code refers to unique names, which often use the key columns of attributes. If you choose ID to be the key column of the attribute then you will have the desired representation of the unique name.

When you browse your dimension the user interface will show captions, which will be resolved through the NameColumn binding. I do not know how you succeeded to bind Name to Year/Month since NameColumn can only be one and not a collection like KeyColumns. Probably you created a calculated column on DSV and provided a formula joining the name of the month and year together. This can stay if you choose ID to be the key column of the attribute.

I am not sure about your goal to have specific unique names, but key columns are chosen by the relationships with a fact table or a dimension table. If we are talking about the granularity attribute then it will participate in some relationship with either a fact table (regular relationship) or another dimension table (referenced relationship). If it is ID, which participates in that relationship then you would better choose ID for the key column. If your fact table or referenced dimension table contains Year/Month pair then you would better choose Year/Month to be the key columns.

No comments:

Post a Comment