Organize SSAS measures in folders with MDX

How to hide and organize SSAS measures in folders with MDX code ? And allow the possibility to group them for Analysis Services end-users? Indeed, browsing an SSAS cube from Microsoft Excel or Power BI for Ad-Hoc Reporting is very convenient.

However, if the list of measures calculated in MDX is several tens or hundreds of items, they must be classified. Ideally, they should be classified by functional groups. And by sub-groups organised by the time axis.

MDX code to organize SSAS measures into folders

First, all measures calculated in YTD will be close to the current value. Here is a deliberately simple but explicit example for classifying SSAS indicators in folders:

Sales
- Sales YTD (Year-To-Date)
- Sales MTD (Month-To-Date)
- Sales LY (Last Year)
- Sales LM (Last Month)
Stock
- Stock YTD (Year-To-Date)
- Stock MTD (Month-To-Date)
- Stock LY (Last Year)
- Stock LM (Last Month)

This is because financial data is often associated with the past year, the past quarter and the past month.

On the other hand, it is preferable to classify the measures in a functional way rather than in technical files. For greater usability in Reporting from cubes, the following properties are very useful:

MDX DISPLAY_FOLDER
VISIBLE
FORMAT_STRING

Secondly, this example of MDX code creates a calculated indicator based on a group of measures in our “.cube” file. This group of measures usually contains one or more fields from a table previously created in the Data View named “Example_View.dsv”:

CREATE MEMBER CURRENTCUBE.[Measures].[Area - Sales] AS [Measures].[Amount - Group],
FORMAT_STRING = "#,##0.00 €;-#,##0.00 €",
VISIBLE = 1 ,
DISPLAY_FOLDER = 'Sales';

Explanation of the MDX code to structure calculated measures in a folder

Finally, this example MDX script allows you to easily hide and organize SSAS measures into folders. Code explanation :

Start by creating the indicator in the cube and indicating the group of measures on which the SSAS indicator is based with the command “MDX CREATE MEMBER <…> AS “.
Then indicate the data format which will be separated by a comma every 3 digits. Or with 2 digits after the comma and in euro currency (€ sign) using the FORMAT_STRING property.
Set the MDX property VISIBLE to 1 to display the indicator to users. To be checked from the explorer and the SSAS connection from Excel or Power BI for example.
Indicate which folder will contain the calculated indicator with the DISPLAY_FOLDER property. In our cube example, the indicator will be visible in the “Sales” folder.

Finally, this short SSAS tutorial explains step by step how to organize SSAS measures in folders with a simple MDX script. To go further in Microsoft OLAP learning, here is another article on XMLA language.