Skip to content
October 3, 2017 / Erik Svensen

How to change the #PowerBI desktop file connection from data model to a Power BI Service dataset or #AzureAS

In April 2017 we got the ability to connect our Power BI Reports to datasets in the service (link) and that is really cool.

Today I got a question from a colleague on how to change a reports dataset in order to separate the reports from the data model – thereby having a pbix file with data model and then design reports by connecting to the dataset in the Power BI service and I came up with this workaround.

This technique can also be used if you have reports that you want to change the connection of a report to an Azure Analysis Services or copying a report to another workspace and modify the connection to a dataset in that report.

Let’s see how we can do this

In this example I have designed a data model and report that is connected to data in a SQL database

And deployed the pbix file to the service

This gives us a dataset and a report.

Now if we open the report and chose to export as pbix

Now I named the report – Demo PBI Exported.pbix

This will include the data model including all the queries etc.

If we deploy the this to the service again we will end up with 2 datasets – not a good idea – we will end up with two datamodels 😦

The best thing would be that this report was tied to the dataset in the service – but the “Get Data” doesn’t give us the option to change the connection in a file with a data model

 

So, I decided to create another pbix file with a connection to the Power BI

This gives me the same fields but is connected to the service

Notice that you can’t see the data and relationship in the panel to the left

 

So now I have 2 pbix files – one connected to the service and one with the report and the datamodel (and the original model)

First thing is to change the 2 files extension to zip – as the pbix files is just a zip file with different files within.

Now the exported pbix files listed in the picture to the right has a “large” datamodel as it includes the data and queries.

I then extracted both zip files to separate folders

I then copied the 2 files

Connections

Datamashup

From the pbix file connected to the service

And pasted them into the folder containing the extracted pbix file Exported from the service

I choose to overwrite the existing

Deleted the file called datamodel and zip all the files in the container to a new zip file – in this case called magic

Now change the extension to pbix and opened the file.

This will give us this look of our reports

Not exactly Magic you might say – but wait – It actually is – now go the get data and connect to the Power BI Service

 

And voila your report is now connected to the Power BI service

 

When publishing this to the service you won’t get another dataset but a new report connected to the dataset.

 

And even better when/if users download the report as a pbix file its connected to the service and not the data model.

Using this method also enables you to copy reports between workspaces and just point to the correct power bi service dataset.

 

Can it be done if we want to change connection to Azure Analysis Services – It sure can 🙂

 

So, I uploaded the pbix file to Azure Analysis Services to create a copy of the data model in Azure using the web-designer.

 

I then repeated the steps of overwriting the Connections and Mashup files in the extracted zip folder with the files from the extracted service folder.

And created a new zip file – Magic Azure AS.zip – then change the extension to pbix and opened the file.

Opened the Get Data experience and switched to Azure Group and choose Azure Analysis services database

 

And entered the server and database information

And the report is now connected to an Azure Analysis Services 😀

This file can now be published to any workspace as it isn’t connected to the Power BI service – dataset.

This will of course give us a new dataset in the service as it is pointing to the Azure Analysis Services

You should also check out what you can do with the Power BI Rest API if you are interested in automating the creation of reports and changing connection to data set – but you can’t do the rebinding of a report to a power bi dataset using the API’s (but it would be nice if we could)

Link to documentation about the Power BI Rest API’s – find them here

Please Please

 

Please let me know if you find this useful by adding a comment or a like on this post.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

12 Comments

Leave a Comment
  1. Marco Russo (@marcorus) / Oct 4 2017 7:03 pm

    Did you try doing that saving the file as a PBIT before? You might get a smaller file without data, which should be simpler. However, this should be a feature made by MS in Power BI, definitely!

    • Erik Svensen / Oct 5 2017 8:47 am

      Just tried it – and it gave me a smaller file as the data isn’t included in the pbit and you can use the same method.

      And lets hope that MS adds the feature to change a pbix files connection to the service instead – that would be a lot nicer than this hack 🙂

  2. selfservicebi / Oct 4 2017 10:16 pm

    Reblogged this on MS Excel | Power Pivot | DAX | SSIS |SQL and commented:
    Das nennt man dann wohl um die Ecke denken, sehr guter Beitrag…

  3. pato lobos / Oct 4 2017 11:26 pm

    Take it to the next level! : Connect Power BI to a model via https OLAP PUMP 🙂

    • Erik Svensen / Oct 5 2017 8:48 am

      As long as you are connecting to a similar tabular datamodel – it should be possible
      BR
      Erik

      • Marco Russo (@marcorus) / Oct 5 2017 9:11 am

        Be careful – both methods (changing the PBIX/PBIT file and modifying the Power BI Desktop model connecting through OLAP port) are not supported by MS. The safest way is to change PBIT and open it in PBI Desktop, then save it. At that point, the file probably works well. Changes applied to PBIX are more risky (you can lose your data). Changes applied through OLAP port might produce unexpected results, because the power BI UI is not aware of what you are doing and any action could result in many errors (you are navigating in unexplored code execution paths).
        I hope MS will provide a *supported* way to change the data model outside from Power BI (I’m personally pushing on this a lot).
        You can vote this if you want to support that: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7345565-power-bi-designer-api

      • Erik Svensen / Oct 5 2017 9:46 am

        Agree with Marco – be careful and remember backups before you play with this

  4. Kirill Kost / Oct 10 2017 11:02 pm

    Tried to use this approach to replace one PBI service dataset by another PBI service dataset, but copying the files from one pbix to another pbix creates corrupted file. So I am getting the message “Unable to open document. This file is corrupted or was created by an unrecognized version of Power BI Desktop. It can’t be opened.”

    • Erik Svensen / Oct 11 2017 6:41 am

      Did you zip the files from within the extracted folder and not the folder ? BR Erik

      • Kirill Kost / Oct 11 2017 10:46 am

        Erik, Yes, I unzipped, zipped correctly. I know it because when I did the same without modifying the files, I was able to open pbix file.

      • kirill533 / Oct 11 2017 7:11 pm

        Finally found out the solution for replacing online dataset. It was enough just to delete the Connections file from inside of pbix file downloaded from the Power BI Service. Maybe, it would simplify also your case: just deleting Connections file instead of replacing it.

      • Erik Svensen / Oct 11 2017 7:17 pm

        Will try as well.. thx for sharing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: