Setting display folders in the table model SSAS 2016

Task

A large table model has been created in SSAS 2016. Most tables have many different measures and measurement fields, and as a result, it becomes difficult for users to navigate this abundance. Is there a way to combine objects into groups?

Decision

The multidimensional SSAS model has a long-standing opportunity and is called display folders (display folders). You can customize the display of dimension attributes in different folders, which makes working with them more convenient for the user. An example of the AdventureWorks test cube.


')
The tabular model SSAS 2012/2014 does not officially support this feature. However, you can add it by directly editing the XMLA model or using the BIDSHelper .
Fortunately, SSAS 2016 provides us with this functionality out of the box and makes it easy to work with it using Visual Studio (SQL Server Data Tools). In this article, we show how this is done.

Setting the display folder

Let's set up the display folder for several attributes in the WideWorldImporters tabular model. If you select one or more columns, you can set a display folder for them (Display Folder) in the properties panel.



Setting up a display folder for measures is exactly the same. When viewing the model in Excel, we will see a folder in the tree.



When setting up, you will not find a drop-down list of existing display folders, so you will need to enter their names manually (or copy, of course).

Setting up output in multiple folders

It is possible to customize the output of one measure or attribute in several folders at the same time. To do this, list the folders separated by a semicolon. Let's put the Tax Rate column in two folders:



In Excel, it will look like this:



But do not forget that the repeated display of the same measure in different folders can be misleading users.

Subfolders

A more interesting option is the creation of nested folders, and it is achieved using a backslash. Instead of placing the Tax Rate measure in two different folders, let's put it in a subfolder.



Result:

Display folders in various applications

Excel

Tabular model SSAS

The display folder is located in the Order group of measures, despite the fact that there is no such concept in the tabular model. If the folder contains different types of objects - measures and dimension attributes, then it will be displayed twice in the list. This statement also applies to the SQL Server Management Studio browser (since this program is still designed for a multidimensional model). Snapshot of SSMS version 17.2

Power BI Desktop

Power BI Desktop easily displays measures and measurement fields in one folder.

Query Builder Reporting Services

SSRS Query Designer behaves the same as Excel and SSMS



Pay attention - despite the fact that the query designer supports DAX (in the latest versions of SSDT), the list of fields is still displayed as if we are dealing with a multidimensional model.

Conclusion

Display folders are an excellent addition to the table model SSAS 2016. This functionality makes it easy to create a more structured look. With it, you can place the same objects in different folders. However, depending on the client application, the folders may look different.