(aka. Hiding in Plain Sight)
I’ve been spending a lot of time lately implementing Power BI solutions using SSAS Tabular models as the back end data source. I’m biased towards Tabular for several reasons. The level of entry to understanding and implementing is initially low, the compression and speed are outstanding, and the DAX language and relational concepts between a SSAS Tabular model and projects built within the Power BI Desktop are the same. I never jumped on the MDX/Multidimensional train because my focus was on database development and internals back then, so by the time I focused more on Business Intelligence, it was an easy choice for me to invest my time where Microsoft seemed to be investing theirs.
One of the challenges with SSAS Tabular model development, for me, has always been the need to fully vet out a new measure or calculation in the intended visuals. The current option we have in Visual Studio to visualize our data quickly is the Analyze in Excel feature.
Analyze in Excel will automatically create a pivot table of your model and expose all your tables and measures for you to test. It provides a quick and easy way to verify role level security, pivot your data, and confirm outcomes. I like it, but I find that I really wanted to develop in the Power BI Desktop since I spend the vast majority of my time using it and deploying solutions with it.
I longed for a quick way to see results when using Live Query the same way I got them when importing or using Direct Query on a database. My existing approach was to add a couple measures in Visual Studio, process my model, and deploy it for each set of changes. I would then refresh the dataset in Power BI Desktop to see if the new measure or calculation worked in the visuals the way I expected. After struggling with this, and doing a couple heavy days of DAX development at a client on their SSAS Tabular model, I had all my windows open and noticed something in SQL Management Studio that should have been apparent to me a while ago…
There is a temporary model that is spun up when developing against a Tabular model. The ugly one that looks like “model name _ Your Name _ GUID”.
Each time you open an instance of the model in Visual Studio, this temporary instance lives for the life of your session… You probably see where I’m going with this.
I immediately connected a Power BI Desktop live connection to this model instance and quickly realized I could make any change in the Visual Studio session and refresh the Desktop to see the automatic results. I could create a measure, see how it behaves, alter it, remove it, and the change popped into view after a quick refresh in the PBI Desktop. It automatically used the live connection to the temporary model the same way as the deployed version, and my sweet development nirvana had been achieved! (ok, maybe not completely, but I’m excited to discover this option even though it was in plain sight)
I love this new-found method of development because it gives me the end to end cycle of working on my Tabular model in Visual Studio. Now I get to see the instant results in all the visual use cases in Power BI. It saves a ton of time processing, and reprocessing, the model to ensure calculations are vetted. Once I’ve verified all my new updates are performing as expected, I can check in my changes to the main Tabular model in my Visual Studio session and process/deploy it a single time to apply the changes.
I always enjoyed the end to end experience when importing data into the PBI Desktop, and to be able to model and build calculations in the visuals area is great. Now, I can get almost the same development experience using my Tabular model on the back end. And all it took was a little frustration, and seeing something that was staring back at me every time I opened my SSAS instance in SQL Server Management Studio. I hope you find this solution useful, or at least get a chuckle that it took me this long to realize this was an option.
Have any tips or tricks that may be hiding in plain sight? Share in the comments section.