Overview: At a user group meeting recently, I was asked by someone how to view Member Properties (Dimension Properties) when running an MDX query in SQL Server Management Studio. He could see them in Excel, but hadn’t discovered where to view them in SSMS. And with that, inspiration for this blog entry was born.
Level: Assumes a basic familiarity with SSAS attributes, MDX queries, & attribute relationships.
And Just What are Member Properties?
As stated in BOL, Member Properties “cover the basic information about each member in each tuple.” There are two types of Member Properties:
- Intrinsic Member Properties. These are your standard properties such as UniqueName, Caption, Level, etc.
- User-Defined Member Properties. These are the attributes available via attribute relationships.
Querying Intrinsic Member Properties in Management Studio
Let’s start with a really straightforward query – no Dimension Properties are being requested in this first example query. Yet, when we double-click on the Australia cell, we do indeed see the standard Intrinsic Member Properties – without even needing to ask for them in the query syntax.
Just for comparison sake, in the following example we have added some of the standard Intrinsic Member Properties to the query syntax. Note that these are retrieved using syntax of “DIMENSION PROPERTIES” within the “ROWS” section. Now if we double-click on the Australia cell again, we see the 3 Member Properties we requested (duplicates of the standard ones above, just to keep things simple).
Querying User-Defined Member Properties in Management Studio
In this 3rd example things get interesting. Sales Territory Group is related to Sales Territory Country via the SSAS attribute relationships.
Due to this relationship, we can choose to retrieve Sales Territory Group via the “DIMENSION PROPERTIES” syntax if we desire. Note that in the following query, Sales Territory Group does not come back within the “visible” query results. Rather, it’s being returned as a Member Property which can be seen with a double click:
Here’s how that attribute relationship looks when browsing the metadata in SSMS. Note that Sales Territory Country is shown under the Member Properties folder:
In the above example, the “Sales Territory Group” is indeed a visible attribute. However, if Sales Territory Group weren’t visible for browsing (yet still enabled), one way to access it would be via the “DIMENSION PROPERTIES” syntax shown above. (Another way would be through a calculated member – see example here.)
Viewing User-Defined Member Properties in Excel
Let’s put an additional frame of reference around our previous Sales Territory Group example by comparing what we saw in SSMS to what we’d see in Excel. Let’s say we’re browsing Excel as follows:
…and we right-click on the Australia cell to see that Sales Territory Group is visible under the “Show Properties in Report” menu:
Finding More Information
MSDN – Using Member Properties