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.
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!
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 🙂
Reblogged this on MS Excel | Power Pivot | DAX | SSIS |SQL and commented:
Das nennt man dann wohl um die Ecke denken, sehr guter Beitrag…
Take it to the next level! : Connect Power BI to a model via https OLAP PUMP 🙂
As long as you are connecting to a similar tabular datamodel – it should be possible
BR
Erik
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
Agree with Marco – be careful and remember backups before you play with this
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.”
Did you zip the files from within the extracted folder and not the folder ? BR Erik
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.
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.
Will try as well.. thx for sharing
I have the same issue as Kirill with corrupted file error and cannot find how to solve it.
Also, not fully get what do you mean by “Deleted the file called datamodel” – is this original Demo.pbix?
If you have a pbix file where it is in imported mode it will have a file called datamodel – if you use a connection to a powerbi dataset in the service – you wont have that file. Are you trying to change that tupe of connection ?
Maybe one clarification to add: the report I am trying to move to move to another workspace is already created as direct connection to PowerBI Service. Now just need to move it to another workspace by reconnecting to the identical data model which is also located in my desired destination workspace test and production workspaces).
Hi Domantas – have a look at my blog post from yesterday – and let me know if that solves you issu – https://eriksvensen.wordpress.com/2018/09/19/powerbi-switching-a-report-from-one-powerbi-dataset-to-another-powerbi-dataset/
Life Saver! This is awesome and saved me a heap of time. I thought I was going to have to re-build the report from scratch to switch to the PowerBI Service. Phew!
Hi – good to hear that it helped you
/Erik
Hi good workaround works perfectly. I just tried this out but found that when I publish the “redefined” report it generates another version of the report, even though published with the same name as downloaded pbix file from service.
Example: I download “A.pbix” from Report “A” in service that is pointed to dataset A. I follow all steps successfully and finally I have in Power BI desktop the A.pbix wired to dataset B in service. All good works fine, I publish, expecting A.pbix will ask to replace the existing one…but it doesnt, rather publishes another instance of Report A, with a different share url, and none of the existing users the existing report A was shared with.
Am I able to explain the problem? Is there any way to keep the url and dist intact while changing the dataset? Please suggest
Hi there Erik. Many many thanks as this saved me a lot of time !
Well explained, not found any documentation on this except on your blog. Thanks again.
Hi Kevin – glad to hear it could help you
Hi Erik, great post! helped me a lot…just what I needed.
Glad to hear
Pingback: Switching from imported data to DirectQuery or Live Connection in Power BI – XXL BI
Neat and useful. thank you much Eric.
now, i wonder if same technique can be used to copy / move visuals between PBIX packages.
Hi Stefan – thx – I have played a little around with updating report content but never got it to work – I have only used the Power BI Rest API to update report content and that worked
Best Regards
Erik
Pingback: #powerbi – Switching a report from one PowerBI dataset to another PowerBI dataset – Erik Svensen – Blog about Power BI, Power Apps, Power Query
It works. Really great, It save us a lot of time.
I was triying to edit the report/layout file but any change is rejected by PowerBI
Good afternoon, Erick.
I tried to change the Connections file from within the .PBIX file using the method of unpacking the .PBIX file and then recompiling.
But when I try to open the .PBIX file a corrupted file error appears.
Can you help me?
Thank you
Hi what is your connection ? The workaroubd depends a little bit on that. If there is a security bindings file … try and delete that as well
It is a connection to Analysis Services.
I need to change the connection, because in the scenario I have several Analysis banks, so I need to change to try to publish in an automated way.
I have 12 PBIX files for 33 Analysis banks.
Hi and your pbix should be published to different workspaces with the same to a different analysis services database in each workspace?
Multiple Workspaces with Multiple Databases
Then you shouldn’t use this method but have a look at the powershell scripts here using the power bi rest api – https://github.com/DevScope/powerbi-powershell-modules
Ok. Thanks
Hi Reinaldo – Is there an updated version of this procedure? I have used it successfully in the past, but it no longer works.
I simply unzipped the PBI Desktop file that’s connected to the PBI Service (“Demo connected to service.pbix” in your example), then immediately zipped up again, and then change the extension back to .pbix. When I open, PBI Desktop reports “Demo connected to service.pbix is corrupt or an invalid report file”.
I’m using Version 2.65.5313.701 64-bit (December 2018) of Power BI desktop.
Hi Nick It should still work. What is your pbix connected to ? If its connected directly to an analysis services server there is one more file to delete. Erik
I haven’t even tried to replace any of the files inside the ZIP yet. Just the act of unzipping the PBIX files and then re-zipping it causes PBI desktop to treat this file as corrupt. I was able to get this working by using WinZip to unzip and zip the files, rather than Window’s native ZIP file handler.
Hi Erik – I’m connecting directly to an Analysis Services Server. What are the specific steps for this method since I am getting the corrupted file error?
Hi
So you are not switching from import to Analysis Services – but from one analysis server to another ?
/Erik
Hi Erik,
if Power Bi have connection versus SQL server i don’t find a connection file. Is it possible change the connection like Analysis Services Procedure?
Thanks
Marco
Hi Marco, if you are importing the data from a sql server you have to do this via the Query editor and modify the steps in your queries that connect to the SQL server – usually the first step in your queries called Source – you might even use parameters to specify the server and database name in one place and refer to these parameters in your queries instead. best regards Erik
Grazi Erik for the quick answer. My problem is that I don’t have access to the production database, so I can’t perform the steps you tell me.This operation with SSAS allows me to change the data source even if I do not have access to the production tabular. But if the connection is to SQL I can’t find a solution because the connection file is not generated and I can’t understand where it saves the connection string. Many Thanks
It saves it in the pbix file but the credentials are stored in encrypted way so it is difficult to change and doing it is not supported by MS
Grazi Erika for the quick answer. My problem is that I don’t have access to the production database, so I can’t perform the steps you tell me.This operation with SSAS allows me to change the data source even if I do not have access to the production tabular. But if the connection is to SQL I can’t find a solution because the connection file is not generated and I can’t understand where it saves the connection string. Many Thanks
Is this expected to still work today? I can extract / rezip and open a file with no problem, but trying to copy the Connections / Datamashup files over and deleted the Datamodel file always seems to yield the corrupted file error for me
Hi Will – its been a while since I have used the method myself. But I would expect it to work but it is unsupported BR Erik
It’s not working for me either. I have used this technique successfully in the past, but hasn’t been working for several months now.
This method doesn’t seem to work anymore. Here’s what you can do:
– go to the query editor (transform data)
– delete all queries
– close and apply
– now you can connect to a dataset in the power bi service.
Just did it 2 minutes ago, works like a charm.
This is very interesting, You are a very skilled blogger.
I have joined your feed and look forward to seeking
more of your great post. Also, I’ve shared your website in my social networks!