THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Display Folders

Display Folders is one of those little features of Analysis Services 2005 which get many people excited. While I personally don't share this excitement, I can understand how display folders can make information more accessible by organizing different metadata objects such as hierarchies, KPIs, measures etc into logical structures. So we are going to take a deeper look into how display folders work, and what does it mean when the client tool claims support for display folders.

Let's start with the following example, showing measures organized into several display folders. There are couple of interesting things to notice about this picture. First, the display folders can have hierarchies. For example, both Net Income and Statistical folders are children of Financial folder (resembling somewhat chart of accounts). Second, perhaps more interesting, the same measure can show up under more than one display folder. For example, YTD Sales show up both under Favorites and under KPIs folder, and Profit appears three times - under Favorites, KPIs and Net Income folders.

To see how this is done, let's first understand what display folders mean to AS server. As a matter of fact - they mean nothing to it. As far as AS server is concerned, DisplayFolder property of metadata object such as measure or KPI, or DisplayFolder calculation property of calculated member is just a string. AS server doesn't interpret this string in any way - it simply reports it back in the appropriate schema rowset, be it MEASURE_DISPLAY_FOLDER column in MDSCHEMA_MEASURES for measures and calculated measures, or KPI_DISPLAY_FOLDER column in MDSCHEMA_KPIS schema rowset. It is really up to the client application to correctly interpret this string and produce visual representation similar to the one captured in the Excel 2007.

So it seems like there must be a common documented convention on the structure of this string in order for all the client tools to be organize objects in display folders consistently. Unfortunately, I wasn't able to find single place in documentation which coherently explains it. For example, description for MDSCHEMA_KPIS says:

A forward-slash (/) delimited categorization structure. Client applications use this structure to determine hierarchical presentation of KPIs. This structure is not enforced.

While the description for MDSCHEMA_MEASURES claims

The path to be used when displaying the measure in the user interface. Folder names will be separated by a semicolon. Nested folders are indicated by a backslash (\).

It is interesting to note, that one of them mentions forward slash and another one backward slash. The "this structure is not enforced" comment probably refers to the fact that neither server's metadata manager nor AMO will do any validation on the string. The comment about semicolon refers to the fact that the DisplayFolder property can contain more than one - this is how same object can appear under multiple folders. For example the DisplayFolder property for Profit measure in the example above is

KPIs;Financial;Financial\Net Income

So when the client application claims that it supports display folders, it is worth to check whether it supports forward slash, backward slash and semicolon in the display folder specification. Excel 2007 does it, but even built-in cube browser in BI Dev Studio has a little problem with organizing measures, by pushing the very first display folder on the list under the measure group name for physical measures.

Published Wednesday, December 13, 2006 6:29 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement