Split an existing Power BI file into a model and report

Split PBIX using PowerShell

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.

6 Comments

  1. 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.

  2. 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.

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.