Prototype to Azure in less than 60 minutes
Migrating Your Power BI Desktop Prototypes to Azure
Prototyping has always been a useful way to get something in front of your end users quickly. It can help you obtain constructive feedback and demonstrate the benefits of a more comprehensive solution that you intend to produce going forward. But what happens when you’ve produced a fantastic prototype product and the stakeholder wants to put this into production and therefore a more robust architecture? Nobody wants to repeat the same effort again (or end up copying and pasting everything from window to window…)
The Problem
This scenario was exactly what I came up against recently. I had created a Power BI desktop file which had an on-premises SQL Server database as its data source. There was some Power Query based ETL and some quite complex report designs but the stakeholder wanted to get an early version of the report out to users quickly as the infrastructure was going through the company’s various approval processes and was unlikely to be ready in the near future. However, once this infrastructure was up and running the natural progression of the report was to move it to the new environment which was:
Azure SQL Data Warehouse, Azure Analysis Services & Power BI Service
I really didn’t want to have to go through the process of creating my pbix again using this new infrastructure so instead embarked on a mission to find a way to move my model out of the pbix file into Azure SSAS and then re-point my report to look at this new model instead of using its own internal version.
Exporting Your Model
For those that don’t know; Power BI Desktop actually runs an analysis services instance in the background when you use it in import mode. Connecting to this instance is the first step in unshackling your model from Power BI’s clutches.
When Power BI starts, it creates the instance and assigns it a, from what i can tell, random port number. So the first thing to do is find out yours.
The easiest way to do this is to use Dax Studio. If you write DAX at all then it is a really good tool for you to have and as a bonus makes what we are doing a lot easier.
Once you have opened your Power BI file open up Dax Studio and select PBI/SSDT. In the drop-down menu you should see your Power BI file.
Select it and click connect. The port number should then appear in the bottom right of the window as shown below:
Note: There are other ways to find this (using command prompt etc.) but I find this way the most straightforward and the majority of developers would have DAX Studio already…
Now you have your instance name, you can open SQL Management Studio (SSMS) and connect to the instance and you should see something like this:
Getting your model definition is really simple from here by right clicking the database and using “Create To”.
It’s worth at this point changing the name of your database to something more relevant.
Once you are ready run the script against the Analysis Services instance you want to create your model on (I’m using Azure SSAS but this should work the same for an on-premises instance).
Important: When I tried to deploy my model there were some issues to do with the compatibility level. To work around this I changed the compatabilityLevel property to 1200 and removed the JSON tags which were not allowed (isPrivate and variations). I would try to deploy your model “As is” first then use this if you run into the same problems. I found that the default compatabilityLevel of 1465 also caused me issues later on when trying to export the code to VS.
Once this is complete you will see some XMLA like in the image below. If not then check your results to see if you had any compatibility issues like I did.
Your Power BI model is now deployed to your SSAS instance as shown below.
The next steps you will most likely want to do are:
- Update your data sources
- Export your code for future editing and source control
Editing your data sources can be done on your deployed model or within Visual Studio once you have exported your code. My preference is to do this in VS so let’s get our code next.
Load up Visual Studio and create a new project. Select Import from Server (Tabular)
Select your database which we deployed in the previous step:
You should now see your database and model in the solution explorer.
And there you have it, a source controlled version of your Power BI file’s SSAS model ready for DEV, UAT and production.
Things to still consider:
- Your model is currently empty as we only deployed the definition. You need to update your data sources. I won’t go through this here but it is essentially clicking “Existing connections” in VS and entering your new data source
- You will need a process to keep your model up to date (trigger processing etc.)
Connecting Your Report
I’ve seen a few ways of doing this. Some people have deployed their report to the Power BI service and used Power Shell to edit how the model connects but the method I have found is so much easier:
Open your Power BI file and edit queries
Delete all of your queries and click apply (yes, we are breaking your report)
You should now see a mass of errors as your visuals now point to invalid query data
Now choose Get Data, select Analysis Services and connect to the instance where your new model exists.
Boom!
And just like that Power BI connects all the broken end points to your new model. Didn’t think that would work did you?
You can now edit, publish and hopefully source control your new live connection Power BI report.
You should now have turned a self-contained pbix file prototype into a source-controlled, development-ready model and report.
It’s also worth noting that these techniques aren’t supported by the Power BI team and although they work at time of writing they may not in future versions. Microsoft did try to implement a Web Designer in Azure that you could import your pbix model into but it didn’t make it past preview and was deprecated.
