Cube Member ID, Name, and Value

Dave Schmitz 21 Reputation points
2020-09-22T15:42:55.933+00:00

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!

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,282 questions
0 comments
Accepted answer
  1. Lukas Yu -MSFT 5,821 Reputation points
    2020-09-23T03:06:16.453+00:00

    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.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.