Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Setting Display Folders in Analysis Services Tabular 2016


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Attend these FREE MSSQLTips webcasts >> click to register


Problem

I have a large Tabular model created in Analysis Services 2016. In most tables I have many different measures or columns and it’s becoming really hard for the users to navigate the model. Is there any way I can group objects together?

Solution

Analysis Services Multidimensional has the solution to this problem for years: display folders. You can assign measures or dimensions attributes to a specific folder which makes browsing the model more user friendly. An example from the AdventureWorks sample cube:

display folders in SSAS Multidimensional

Analysis Services Tabular 2012/2014 didn’t officially support these display folders. However, you could add them by either manipulating the XMLA code of the model directly or by using BIDSHelper.

Luckily Analysis Services 2016 now officially supports display folders, which mean you can configure them easily through the Visual Studio (SQL Server Data Tools) interface. In this tip, we’ll give an overview of this new feature.

Setting the Display Folder

Let’s set the display folder for some measures in the WideWorldImporters Tabular model (see the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3 for more info). When you select one or more columns, you can set the Display Folder in the property pane:

Set the display folder in Analysis Services Tabular 2016

Setting the display folder for a measure is exactly the same. When we browse the model with Excel, we can find the display folder listed under the table node:

Analysis Services Tabular 2016 display folder in Excel

Unlike SSAS Multidimensional, there is no dropdown with existing display folders; you have to type everything manually (or copy and paste of course).

Assigning Multiple Display Folders

It is possible to assign one single measure or column to multiple display folders. You can do this by concatenating them using the semicolon. Let’s put the Tax Rate column in two display folders:

Multiple display folders in Analysis Services Tabular 2016

This gives the following result in Excel:

Analysis Services Tabular 2016 multiple display folders in Excel

It might be a bit confusing though if the same measures pops up multiple times in the field list.

Nesting Display Folders

A bit more interesting scenario is the ability to nest display folders. This is done by concatenating the display folders with a backslash. Instead of putting the Tax Rate in two distinct display folders, let’s nest them instead:

Nesting display folders in Analysis Services Tabular 2016

Which gives:

Nested display folders in Excel

Behavior in Different Client Tools

Excel

Excel supports display folders as you have noticed previously. However, it will display them in different places depending if the items in the display folder are measures or columns. For example, if some measures are added to a display folder called “Derived Measures”, we get the following:

Measures & display folders in Excel from Analysis Services Tabular 2016

SSAS Tabular

The display folder is moved to the “Order measure group”, although there’s no such concept in SSAS Tabular. If a display folder contains mixed objects – measures and columns- it will be repeated twice in the field list. The same is true for the browser in SQL Server Management Studio (since that one is still complete based on the Multidimensional layout):

Display folders in SSMS from Analysis Services Tabular 2016

Power BI Desktop

This screenshot was taken with version 17.2 of SSMS. Power BI Desktop however gladly mixes measures and columns together in its display folders:

Display folders in Power BI desktop in Analysis Services Tabular 2016

Reporting Services Query Designer

The Reporting Services Query Designer behaves the same way as Excel and SSMS:

Display folders in SSRS from Analysis Services Tabular 2016

Notice that while the query editor can produce DAX queries (available in the latest version of SSDT), the field list still displays all objects as if we’re dealing with a multidimensional model.

Conclusion

Display folders are nice addition to Analysis Services Tabular 2016 models. They allow you to easily create more structure in your models. It is possible to put objects in multiple display folders or to nest display folders. Depending on the client tool, display folders behave a bit differently for measures.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools