Shared datasets are a great way to share data models across the organization. This enables users to maintain one source of the truth and increase efficiency. However, models should be built separate to reports. Meaning it can be difficult to transform an existing report into a shared dataset.
This post describes how to split a pbix file with report and model, into two separate files One for the model and one for the report. This is accomplished using a PowerShell Script. By using this technique any report can quickly be split into a data model file and live connection report file.
Note: that this script is not officially supported by Microsoft. This code is provided as is without any guarantees. The code will alter the internal files, so please keep a backup if you are unsure of anything.
Think like the Business, Act like I.T
Being a great Power BI developer can often mean more than just building visually impressive reports. Focus should be given to efficiency, reusing design and data modelling where possible. In addition, there should be “one source of the truth” – different reports should not have different methods to calculate the same KPI.
This is accomplished by the creation of Shared Datasets. Users can publish a report that contains no visuals, only a data model. Multiple reports can then be built off this model by using a live connection and use the same data model. The model contains global measures. A Global measure is written and stored in the model file. Then the Global measures are re-used by other reports via the Live Connection. This ensures all reports have the same data model, logic and refresh status.
There are many reasons you should consider this approach, which is out of scope for this article. If you are less familiar with shared datasets, I encourage you to visit the following resources:
Think like the Business, Act like I.T user group – global models
Power BI datasets: A Method to the Madness article
Planning A Power BI Enterprise Deployment whitepaper DOWNLOAD
( Chris Webb and Melissa Coates) – section 9
Use Cases
Shared datasets are great, but what if you already have a file with a model and report in one? Currently in order to split a file this would need to be manual, by either copying all the visuals over to a new report (you would also need to re-record any bookmarks, filters, layouts etc. ) or to copy all the Power Query queries (you would then need to set up relationships and re write all measures). This can be time consuming, especially on a large report or model.
Luckily for you, this code will do all the hard work for you. Simply run the code and select a PBIX file. It will create two new files, a report and a model.
Running the code
Right click and select the option Run with PowerShell in the menu.
A menu will open up. Select the power bi file that you wish to split.
Click the button OPEN to allow the script to modify your file.
The script will then create two copies of the file and add the suffix _model and _report. Feel free to rename these, if you desire.
Publishing to the Service
Model File
Open the _model file. It’s a good idea to add some text to the report explaining this is only a model file, so others understand the purpose. Example:
Publish this file to the desired workspace.
Report File
Open the _report file. The script will leave all visualizations and report features intact, but all connections will be removed. When you open the report in power bi desktop, all visuals will appear broken:
If prompted, make sure you discard changes – this will completely detach the report from the source.
Click Get Data and Select Power BI Datasets.
In the pop up window, select the model report you published in the previous step. This will now restore all visuals to display again.
Publish the report to the desired location.
That’s it! you can now share your new model file and continue to build reports off it.
Download the Script
You can download the script described below
Download the PowerShell file and save it to your local machine.
Script
#This script was designed by Steve Campbell and provided by PowerBI.tips
#BE WARNED this will alter Power BI files so please make sure you know what you are doing, and always back up your files!
#This is not supported by Microsoft and changes to future file structures could cause this code to break
#--------------- Released 6/2/2020 ---------------
#--- By Steve Campbell provided by PowerBI.tips ---
#Choose pbix funtion
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "PBIX (*.pbix)| *.pbix"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#Error check function
function IsFileLocked([string]$filePath){
Rename-Item $filePath $filePath -ErrorVariable errs -ErrorAction SilentlyContinue
return ($errs.Count -ne 0)
}
#Function to Modify files
Function Modify-PBIX([string]$inputpath, [string[]]$filestoremove){
#Make temp folder
$temppth = $env:TEMP + "\PBI TEMP"
If(!(test-path $temppth))
{New-Item -ItemType Directory -Force -Path $temppth}
#Unpackage pbix
$zipfile = ($inputpath).Substring(0,($inputpath).Length-4) + "zip"
Rename-Item -Path $inputpath -NewName $zipfile
#Initialise object
$ShellApp = New-Object -COM 'Shell.Application'
$InputZipFile = $ShellApp.NameSpace( $zipfile )
#Move files to temp
foreach ($fn in $filestoremove){
$InputZipFile.Items() | ? { ($_.Name -eq $fn) } | % {
$ShellApp.NameSpace($temppth).MoveHere($_) }
}
#Delete temp
Remove-Item ($temppth) -Recurse
#Repackage
Rename-Item -Path $zipfile -NewName $inputpath
}
#Choose file
try {$pathn = Get-FileName}
catch { "Incompatible File" }
#Check for errors
If([string]::IsNullOrEmpty($pathn )){
exit }
elseif ( IsFileLocked($pathn) ){
exit }
#Run Script
else{
#set variables
$modelfiles = @( 'SecurityBindings', 'Report')
$reportfiles = @('Connections','DataModel', 'SecurityBindings')
#Copy files
$pathf = Get-ChildItem $pathn
$reportname = [io.path]::GetFileNameWithoutExtension($pathn)
$model = ($pathf).toString().Replace('.pbix', '_model.pbix')
$report = ($pathf).toString().Replace('.pbix', '_report.pbix')
Copy-Item $pathn -Destination $model
Copy-Item $pathn -Destination $report
#modify files
Modify-PBIX $model $modelfiles
Modify-PBIX $report $reportfiles
}
Script Usage License
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Check out the new Merch!
Like it
Good one. There is another power shell script to convert the queries inside your pbix file as dataflows. It would be good to have that also in your arsenal.
This is Bananas 😉
??
Hi Steve, I’ve got a quick question that has been puzzling me regarding using Shared Datasets.
It is my understanding that when using Live Connection>Shared Datasets you cannot rename tables/columns/measures in the master dataset because all reports using the dataset will break. Is there any way around this?
This is correct.
Imagine you have a visual and you insert a measure named “YTD Sales”. Internally, the report will contain two separate bits of metadata. The measure will be defined (in an internal model file) and the visual will refer to this measure definition (in an internal layout file). This means we have a measure definition (which contains the dax query and data type etc), then visuals that refer to this definition so Power BI knows what numbers to display.
Now say you update this measure to be named “Sales Year to Date”. When you have the model in the same file, the measure definition will update, and power BI will also go through your report and update the metadata of any references contained in the visual (in the internal layout file)- it will see you had a visual referencing “YTD Sales” and change this to “Sales Year to Date”. The definition is updated and everything referring to this definition is also updated.
Separating these out changes this behavior. Imagine I split to two files – PBI_Model and PBI_Report. If you update the measure in the PBI_Model, it will update this in the definition (in an internal model file).
However, the PBI_Report file does not know you have made this change as they are separate files. Instead, it still has the old name referenced in the visual. As the internal layout has not been updated it will send a query to the PBI_Model to get “YTD Sales”, but this no longer exists. It has no idea that the new name is the same measure so it will break the visual.
Important to note – it references the full path – e.g. tableName/measureName . so even if you move the measure to another table it will break – despite the fact it has the same name and does not really change anything.
Of course you can simply drag the new measure into the visual, but no way of automating this.
thanks for sharing!!! the tool is triple awesome. Very useful for the editing of reports with large datasets and to re-use exiting datasets with modified reports for different end-users.
Very interesting. I am only a part time developer so have a tough time keeping up with change. However, I recently was kicking myself because I have a monster Report and Dataset together, and have found other uses for the dataset. The report is tied into an App, and many users have hyperlinks or shortcut right to it. If I go thru this split process will that force me to rebuild that app and all the users shortcuts?
Alternately, is there a downside to leaving this report in with the dataset, and just link to the dataset for other new reports?
Lastly, assuming things are split as they should be, if then I do define a change needs to happen to the dataset, I still have to download that dataset pbx, make the change and rePublish, correct?
Hmm, now I’m getting carried away. Is there a best practice as to when a new dataset should be created vs continually expanding a single dataset? I assume the entire dataset loads into memory even if the report is using a small subset of that data.
Once you republish, the report ids would be new and require new links.
Really depends on your ogranization and sharing strategy. I would always recommend setting these strategies early with the correct departments. An advantage of splitting is that models can be maintained individually at an enterprise level solution.
Single Model + Report files are better for ad-hoc analysis, one offs or smaller / personal reports. See:
https://docs.microsoft.com/en-us/power-bi/guidance/report-separate-from-model
You would have download both to make changes. You can use hot swap connections to connect to the file locally though.
The live connection does not import the data, rather works similar to direct query. Therefore it will only query the actual data used for the view.
Great.
The downloaded file didn’t work for me. But when I copied the code directly to Powershell, it worked just fine 🙂
It is 4:30 on a Friday and I have to split 4 massive combined .pbix files by the end of day. You, sir, are my hero and I owe you a beer!
Hi Steve,
After splitting the reports from the model, RLS feature is disabled in the reports pbix. Does this mean that for this approach of splitting model and reports, RLS can only be defined in the model file and will be shared by all reports that connect to it?
Yes.
RLS is defined on the dataset and filters the data. As the report file will not contain any data, there is nothing to apply RLS too
I’m used to splitting data and presentation into two layers, It helps to avoid silos, let reports developing run in parallel, and improve data consistency.
By disgrace, I moved a dataset to a different workspace, and all reports linked to it got broken. Opening those pbix didn’t provide any way to change the link to the new workspace, Power BI just hanged. I tried to modify the Connection section in the pbix with no luck. Thanks Steve Campbell! This script let me recover all the work!!!
I know this is an old post, but wanted to ask would it be appropriate in the data model instance visuals for “check” purposes? Things tables to check measures and related items. Nothing designed for the end user but something to give you point to check form. Expense YTD or Prior year Revenue/Expense. Having your major measures on the data model instance of a PBI file would allow one to validate they are good. Later on if there is “issue” with a Power BI file that calls data from the data model this would allow one to re-validate.
Are there any issues with doing this?
Alan
Yes – that is a fantastic idea and something I often do. A “validation” report could be a separate report with just simple tables that you can compare to existing data. That way, you can validate your work, but also show to others so they know the numbers are correct and will trust your work.
The beauty is that many reports can use the same model. That way if you need to make updates, simply update the model and the reports will pull the correct information.
Great tool – just what I have been looking for!
One issue I have ran into: The report file is compressed in a Zip file and I don’t have access to extract it. Any ideas?
You might have the report open. Check it is closed and try again
Thank you very much, I was able to split my pbix into separate model and report. I started with Power BI few months back and my report has been growing, I prefer making edits online by sometimes new dataset need to be added and then I have to download pbix, open in Power BI Desktop, add dataset, and republish the report. This sometimes breaks few visuals (smart narration) that I had created online. Now with split strategy, I will be doing all report modifications online and any additions to data model will not affect the report visuals.
I am facing an issue.. splitting the report and model works fine. I am able to publish the report fine. However, if I export the report pbix file from Power BI later, the exported pbix again contains the report + model combined into one file. Is this the expected behavior? Do I need to split the report every time I export from Power BI for making any changes in Power BI Desktop?