Cube Member ID, Name, and Value

Question

Hello;
What is the best way to provide an end user the ability to see view a dimension attribute member by either its Key, Name, or Value at their discretion. I'm using Excel pivot table for the front end to access the SSAS cube.

For example for a given Category, it has the following identifiers:
Key:10056
Name: CAT100
Value: Bikes

Any solution is on the table whether it is a SSAS configuration or part of a VSTO Addin or anything else.

I'm not sure if I have to present three hierarchies (a hierarchy for Key, Name, and Value) or if there is a way to take advantage of the fact that the dimension attributes have an KeyColumn, NameColumn, and ValueColumn property.

Maybe related, In poking around, I see the $SYSTEM.MDSCHEMA_MEMBERS has a "Description" column. How does this get populated? Does an Excel Pivot table have a way to display contents in this column?

Many thanks!

Answer 1

Hi,

The user could view a dimension member's NameColumn directly in PivotTable. If you want to view the KeyColumn or ValueColumn content, one easy way is to add two more attributes in the dimension. It is not mandatory to hierarchize them.

E.g. add "CategoryKey" attribute to the dimension and set its NameColumn with "Key" from the source table.

If you find confusion in this , you could read : SQL Server Analysis Server (SSAS) KeyColumn vs NameColumn vs ValueColumn for better understanding.

It is the by design behavior of SSAS OLAP, I don't think VSTO could help on this.

The $SYSTEM.MDSCHEMA_MEMBERS table is database system schema, it could not help in this case. Also normally we would not change this directly. The description column you mentioned :

DESCRIPTION

This column always returns a NULL value.

This column exists for backwards compatibility

You could find this in MDSCHEMA_MEMBERS Rowset

Hope this helps.
Regards.
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.