A colleague came to me recently with a problem. She was implementing a Dashboard in PPS M&A, and she was seeing some unexpected dimension names. The behavior was specifically affecting her when she was creating a report using an Analytic Grid, but it was consistent inside of PPS M&A everywhere I looked. The problem seemed to be present with several other hierarchies.
Basically, her hierarchy/dimension names were not being presented to the user as expected. For example, in her SSAS DB she had a dimension named ‘Revenue Indicator’ and a user hierarchy named ‘Revenue Indicators’. In the Analytic grid, it looked like the word ‘Indicator’ was repeating… the hierarchy was being displayed as ‘Revenue Indicator Indicators’. This naming was a bit confusing, and a little unexpected, so I dug into it a little bit.
Evaluate the Problem
My first step was to check the Analytic Grid in question, as well as the dimension in SSAS. Not that I thought my coworker was lying to me, but I wanted to make sure that what was being displayed to the user was NOT what I expected to see. Nope, I was not being lied to and deceived… my faith in humanity can continue unabated. Some transformation was definitely going on between SSAS and PPS.
Determine how PPS is getting the Hierarchy name
Next, I wanted to see how PPS retrieves the names of all the hierarchies and attributes to display to the user. I fired up Profiler, and captured a trace when I loaded up the Analytic Grid. I saw PPS using several Discover commands… so I fired off a Discover command based on what I saw in the trace. It looked something like this:
1: <Discover xmlns=“urn:schemas-microsoft-com:xml-analysis”>
2: <RequestType>MDSCHEMA_HIERARCHIES</RequestType>
3: <Restrictions />
4: <Properties>
5: <PropertyList>
6: <Catalog>MyCube</Catalog>
7: <Content>SchemaData</Content>
8: <Format>Tabular</Format>
9: </PropertyList>
10: </Properties>
11: </Discover>
That discover command gave me back the schema for the Cube. I took a look at the XML… here is the relevant section:
1: …
2: <row>
3: <CATALOG_NAME>MyCube</CATALOG_NAME>
4: <CUBE_NAME>My Cube</CUBE_NAME>
5: <DIMENSION_UNIQUE_NAME>[Revenue Indicator]</DIMENSION_UNIQUE_NAME>
6: <HIERARCHY_NAME>Revenue Indicators</HIERARCHY_NAME>
7: <HIERARCHY_UNIQUE_NAME>[Revenue Indicator].[Revenue Indicators]</HIERARCHY_UNIQUE_NAME>
8: <HIERARCHY_CAPTION>Revenue Indicators</HIERARCHY_CAPTION>
9: <DIMENSION_TYPE>3</DIMENSION_TYPE>
10: <HIERARCHY_CARDINALITY>26</HIERARCHY_CARDINALITY>
11: <DEFAULT_MEMBER>[Revenue Indicator].[Revenue Indicators].[All]</DEFAULT_MEMBER>
12: <ALL_MEMBER>[Revenue Indicator].[Revenue Indicators].[All]</ALL_MEMBER>
13: <DESCRIPTION />
14: <STRUCTURE>1</STRUCTURE>
15: <IS_VIRTUAL>false</IS_VIRTUAL>
16: <IS_READWRITE>false</IS_READWRITE>
17: <DIMENSION_UNIQUE_SETTINGS>1</DIMENSION_UNIQUE_SETTINGS>
18: <DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
19: <HIERARCHY_ORDINAL>1</HIERARCHY_ORDINAL>
20: <DIMENSION_IS_SHARED>true</DIMENSION_IS_SHARED>
21: <HIERARCHY_IS_VISIBLE>true</HIERARCHY_IS_VISIBLE>
22: <HIERARCHY_ORIGIN>1</HIERARCHY_ORIGIN>
23: <HIERARCHY_DISPLAY_FOLDER />
24: <GROUPING_BEHAVIOR>1</GROUPING_BEHAVIOR>
25: </row>
26: …
So, precisely what I would expect… but not what is coming out of PPS.
Determining how PPS is transforming the Hierarchy name
I then tried a little experiment… I added a few more hierarchies, to see how PPS would present them:
After deploying the changes, I saw the following hierarchies in my Analytic Grid:
| SSAS Hierarchy | PPS Name |
| [Revenue Indicator].[Revenue Indicators] | Revenue Indicator Indicators |
| [Revenue Indicator].[Revenue Indicator Types] | Revenue Indicator Types |
| [Revenue Indicator].[Types] | Revenue Indicator Types |
Result
PPS is retrieving the Dimension name and Hierarchy name, then concatenating them together while removing any duplicated words from the Hierarchy name. I checked this behavior out in several places inside of PPS (not just in the Analytic Chart)… it appears to be common functionality.
This is actually pretty helpful. Most of the time when we create SSAS DB’s, they’re accessible using a variety of tools… PPS M&A for Dashboards, via Excel for power users, etc. The dimensions and hierarchies are rendered in different ways for each tool. Some will concatenate the Dimension name with the Hierarchy name, and some won’t. Some will give you a nice hierarchy, some you just get an ugly list. Naming your dimension appropriately will give you the most user friendly names in each of the tools… in this case, using ‘Revenue Indicator Types’ gives you a user friendly name in both Excel and in PPS, which is what we needed.
This is some pretty handy functionality, but I didn’t find it with a quick search of the ‘net and BOL (which doesn’t mean it’s not there). If you’re surfacing your SSAS DB through PPS, I would definitely take this into account when determining your naming conventions.
Cheers,
David