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