Using SSAS 2005/2008 as PowerPivot Data Source: Query Designer

The query designer in PowerPivot has a few limitations that require the manual modification of the MDX query to get data that are often needed to create a PowerPivot model that compares data from cube with data from other sources (like Excel workbooks or external databases). For example, it is not possible to import the attribute key (the MDX query designer import just the attribute description). These limitations can be overcome by modifying the cube structure, but the point is that a well-designed cube following all the existing best practices results in a hard to use data source for PowerPivot, because it is not easy to import some data that are usually hidden in PivotTables, but important to create a model that relates data from different sources.

Import Dimension

It is not so clear how to import just a dimension without any measure. When you select some attributes from a dimension without selecting any measure, you get a message like the one shown in Figure 1.

Figure 1 – Import dimension without measure seems not working

However, it is not clear that it is sufficient to click on the “Show Empty Cells” button (which is pressed in Figure 2) to show import all the data from a dimension without any measure. While this is clear for a BI developer, it is not intuitive for the Excel user who is trying to import data in PowerPivot.

Figure 2 – Show Empty Cells enables the import of a dimension without measures

A possible improvement could be a suggestion in the message shown in Figure 1, by adding the message “Enable Show Empty Cells to import dimension without measures” on the row below “No rows found. Click to execute the query”.

Import Attribute Keys

A well-designed cube uses the attribute key to store the application key of an item. Sometimes it could be the surrogate key of the lower granularity item of a dimension (this is the case of the Product dimension in Adventure Works, which is not a best practice in cube design even because the application key is not exposed in any dimension attribute in that sample cube). In this way, end user browsing data in PivotTable only sees descriptive information, while all the MDX queries generated underneath make use of the attribute keys to uniquely identify dimension members. With the introduction of PowerPivot, having access to these “internal” data (like the product code, the key for a sales zone and so on) becomes fundamental to make the user able to merge data coming from different sources. It is often the case that the description (like product name) might have some differences in different systems, while the key (like product code) is likely identical even on different systems.

There are a number of scenario where it could be interesting to merge data from different cubes. More generally, it is also interesting to merge data from a cube with data coming from other sources (not only from Analysis Services). A typical example is to compare sales of products with reparations made on the same products. PowerPivot seemed the best way to do that, but the safest way to relate data was using the product code, which is identical in both data sources. Unfortunately, the sales cube does not expose this information because it is used in the attribute key of the Product attribute, just as Adventure Works does.

A possible workaround would be to include the MEMBER_KEY in properties dimension of the query that is created by the designer. Manually adding this clause in the MDX query inhibits any further use of the designer for the same query. Unfortunately, it is hard to explain to Excel users how to make this modification in a safe way for any MDX query. A best workaround would be the automatic creation of calculated members that displays the desired key. A user can do that by hand, but the skill required to write the right formula is still advanced, like the calculated member necessary to display the product key in Adventure Works that you can see in Figure 3.

Figure 3 – Calculated member to include product key in MDX query

At the minimum, the creation of such calculated members could be handled by a wizard included in the query designer. The result of using the new calculated member is shown in Figure 4.

Figure 4 – Import of dimension members including keys through a calculated member

A good wizard would also propose valid names (like Key<attributename>) for calculated measures used to import item keys. But until we don’t get new versions of PowerPivot, you might consider adding attributes to existing dimension just to expose the keys you need to make the relationship with data from other sources.

Import Ragged Hierarchies

When you import ragged hierarchies, the MDX generated by the query designer doesn’t show members that are hidden in the lowest levels of the hierarchy. For example, consider the hierarchy Sales Territory which is shown in Figure 5: the ragged hierarchy only browse data up to a certain level, depending on the data that are excluded by the Hide Member If condition.

Figure 5 – Sales Territory ragged hierarchy browsed using SQL Server Management Studio

Now, if you try to import this same hierarchy in the MDX query designer, you can only see rows corresponding to hierarchies which don’t hide any member at any level. As you can see in Figure 6, only the North America group is imported in this way.

Figure 6- Import ragged hierarchy Sales Territory

A possible workaround is to include the single attributes in the dimension without any hierarchy, importing them instead of hierarchy levels, as you can see in Figure 7. However, this requires a change in model design, which is not something that the Excel user can do.

Figure 7- Import single attributes from Sales Territory dimension

Another workaround is to manually change the generated MDX: each hierarchy level corresponds to an attribute, which can be invisible in metadata showed to the user. Thus, knowing the right name of the underlying attribute, it is possible to create the MDX query by hand, but this is not something that an Excel user can do without any help by the IT department.

The best solution in this case would be a different MDX generated by the query designer, but this is a hard issue to solve in MDX. The query actually generated by the selection made in Figure 5 is:

SELECT { } ON COLUMNS,
{ ([Sales Territory].[Sales Territory].[Region].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works] CELL PROPERTIES VALUE

One possible workaround (thanks to Tomislav Piasevoli who find that solution) is the following MDX query:

WITH
SET OriginalSet AS [Sales Territory].[Sales Territory].[Region].ALLMEMBERS
SET ParentsSet AS OriginalSet.Item(0).Hierarchy.Levels(OriginalSet.Item(0).Level.Ordinal – 1).ALLMEMBERS
SET ExpandedOriginalSet AS
Generate( ParentsSet,
IIF(Descendants(ParentsSet.Current.Item(0),1).Count = 0,
{ParentsSet.Current.Item(0)},
Descendants(ParentsSet.Current.Item(0),1)
)
)
SELECT
{ } ON COLUMNS,
{ ExpandedOriginalSet }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works] CELL PROPERTIES VALUE

The result produced by this MDX query is shown in Figure 8: as you can see, the result is not really the same as in Figure 7, but it might be good enough in many cases (PowerPivot calculated columns can be used to fix the NULL values in Region and the missing NA row can be ignored in PowerPivot, since it is automatically added for unrelated data in PivotTable).

Figure 8 – Ragged hierarchy imported by using a custom MDX query

This MDX query is not something that an Excel user will never write, but it could be eventually implemented in MDX generation to handle these special cases. Of course, this is just another suggestion for future improvements of PowerPivot.