A customer of mine is in the midst of a proof of concept using SQL Server and Power BI. During the POC, all the modeling was done in Power BI Desktop. Now that the POC is coming to the next phase, the customer is ready to move the Power BI data model to Analysis Services. But the problem is that all the visualizations in the Power BI Desktop file based on the imported data model will need to be recreated in a new Power BI Desktop file using a Live Query connection to Analysis Services. If the visualizations and reports are extensive, this could be quite a bit of work.
In this blog post, I’m going to walk you through modifying a Power BI Desktop file with an imported data model to use an external data model hosted in Azure Analysis Services or SQL Server Analysis Services 2017. This isn’t supported by any stretch of the imagination but if you’re in a pinch and have to convert a Power BI Desktop file from an imported data model to Live Query then this may be helpful to you. Also, this method works as of the January 2018 release of Power BI Desktop but there’s no guarantee that this method will work in future releases of Power BI Desktop.
I was inspired to write this blog post after reading this post in the Power BI community forums by odegarun. It’s a great post with some good instructions, but I wanted to provide a clearer walk through as well as validate a couple other things with the process for my customers and anyone else that might be interested.
Let’s Get Started
I’ve got a Power BI Desktop file (created with the January 2018 release of Power BI Desktop) that includes an imported data model. The source data is a single .csv file featuring NYC drug crime stats. The data model features a few implicit measures, a calculated measure, and a user defined hierarchy. There is also one page in the report featuring a few visualizations.
Let’s say that I want to convert my data model to an Azure Analysis Services model. Easy enough. I head over to the Azure portal, create a new Analysis Services model and then use the new web browser designer to create the AS model using the existing Power BI Desktop file.
Now that the model is deployed to Analysis Services, how do we repoint the existing Power BI Desktop file to the new model so I can retain all my existing visualizations?
The Tricky Part
First, open a new instance of Power BI Desktop and create a Live Query connection to your Azure Analysis Services model you just deployed. Then save the Power BI Desktop file. Change the extension for the first Power BI Desktop file with the imported data model to .zip. Do the same for the new Power BI Desktop file with the Live Query connection.
This is where things get tricky. We need to download a file management tool that will let us edit the contents of a compressed .zip file. In odegarun’s post previously mention, he recommended Total Commander, which you can download for free. I used Total Commander and can verify that it worked for this purpose. There may be other tools available that will also work.
In Total Commander, explore the .zip files side by side as seen below. When you explore the contents of the .zip files, you can see the components of the Power BI Desktop files. On the left I have the first file with the imported data model. On the right I have the Power BI Desktop file with the Live Query connection.
Delete the DataModel file from the original imported Power BI Desktop file. Copy the DataMashup file from the new Power BI Desktop file to the original Power BI Desktop file. Then rename the original Power BI Desktop file and replace the .zip extension with the .pbix extension. Now open the original Power BI Desktop file.
After opening the file, you may see a couple error messages related to rending the reports. I just clicked the x to close the boxes. The visualizations should fail to render as seen here. The fields list should also be empty with no tables included in the Power BI Desktop file.
Click Get Data in the Home ribbon, select the Azure Analysis Services database (beta). Enter in the Server name of the Azure Analysis Services server you provisioned earlier. Make sure you also select Connect Live and click OK. Select the model you deployed using the Power BI Desktop file.
Once you click OK, the visualizations should render. You’ve now successfully converted your Power BI Desktop file with an imported data set to a Live Query data model and successfully retained all the original visualizations. Also, the hierarchies and calculated measure were also retained.
Feedback?
Again, this is totally not supported by Microsoft or the Power BI team, but this could be helpful if you need to do this without having to recreate all your visualizations. It works for now as of the January 2018 Power BI Desktop release, but there’s no guarantees that it will continue.
I hope you found this helpful. Leave a comment below and let me know if you had luck with this approach.
HI Dustin, great post!
I’ve been using another technique for this conversion, let me know your thoughts:
https://ruiromanoblog.wordpress.com/2018/02/01/convert-a-power-bi-desktop-report-from-import-to-live-query/
Basically you just delete all the queries and then execute the “Get-Data” again but point to Analysis Services and select “Connect Live” after the deletion of the queries the “Connect Live” is enabled again 🙂
Thanks
Rui, I am not seeing in your explanation how you get the queries back.
I’d recommend trying Rui Romano’s approach before going down the route of this blog post. I’ve used Dustin’s approach with success in the past many times, but Rui’s approach is truly a simpler solution.
Thank you Dustin and Rui.
Yes Rui’s approach is good. I personally think the best supported way to change the data source is to rebind the published report to another dataset using PowerShell and then you could download the desktop file if you needed to.