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).
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
The output will look like this
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
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 Twitter | Follow me on FaceBook
Thanks for this article,Very useful 🙂
Ever tried adding a default dimension member?
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.
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