SQL Server Analysis Services – Assign a Default Measure in SSAS Tabular Model Solution

It has been rather long time since I had posted any content online. Perhaps I was lazy. Well this post is on SQL Server Analysis Services (SSAS) and precisely here we will be discussing an issue which I observed today while randomly browsing various properties and options in the process of creating a tabular model in SSDT – SQL Server Data Tools.

As one of the better practices followed, while designing a multi-dimensional OLAP is to ensure that there is a default measure already assigned. This will ensure that such queries (case where explicit measure has been used) return result, as expected (use the default measure).

1_SQL_Server_Assign_a_Default_Measure_in_SSAS_Tabular_Model_Solution

As shown above the user can comfortable use the UI and edit/alter the default measure.

Again, there are many who will also argue that users will never encounter such ad-hoc scenarios. If you are of the same belief, I suggest you must read this excellent article by Jorg;

Getting back to the tabular model, there is no provision for the model developer to assign any measure as the default measure of the solution. Though there is a hack, which will be illustrated later in the section.

This is the default behaviour when you will execute such as MDX on the tabular model

2_SQL_Server_Assign_a_Default_Measure_in_SSAS_Tabular_Model_Solution

The output will look like this

   

3_SQL_Server_Assign_a_Default_Measure_in_SSAS_Tabular_Model_Solution

So, what is the measure [__No measures defined]? Obviously, you did not create this measure and moreover why the value 1 is being repeated for all the rows?

The answer lies in the model itself. You need to go back to the tabular model solution and edit the .BIM file

4_SQL_Server_Assign_a_Default_Measure_in_SSAS_Tabular_Model_Solution

This block of code is added automatically and there is no direct method to alter the default measure from the designer window. If you go by the ideal case scenario that the users/developers are intelligent enough to use at least one valid measure, you will never encounter the default case.

Again this is no blocker, you can still go on and deploy your model and it will work absolutely fine. But just to save yourselves from an overwhelming situation, go ahead and assign a meaningful measure as the default measure by editing the code in the BIM file.

I have also raised a MS Connect issue to have a provision to assign a default measure from the properties window while developing the model and the URL for the same is

Thank you for your time!
Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

4 Comments on “SQL Server Analysis Services – Assign a Default Measure in SSAS Tabular Model Solution”

  1. I noticed that a couple of sites reference this page as the solution to the problem but have you actually tried doing this and succeeded? I have tried numerous times and different ways but when you open the cube in designer mode aftersuch a change, you run into an error. Note that the portion of the cide shown on the screenshot above is PRIOR to any measure definition code so when parsing the resulting .bim file, SSDT will not have the measures defined yet and thus fail. If you try replacing the definition of the [__No measures defined] measure above with the definition of the desired Default Measure you run into the “Qualified name is not allowed in this context”. I don’t know if there is a way of doing this at all.

  2. Thanks for article. I updated tabular models into new Compatibility Level 1200. Unfortunately MDX command got lost and models are based on TMSL json-based scripting language now. Is it possible to create default measure for new compatibility level same way? Thanks

Leave a Reply

Your email address will not be published.