Stripping Off AS Connection

I recently completed a project to migrate 45 Power BI reports from analysis services to Power BI Premium datasets. If you have a Power BI file (*.pbix) that's connected to AS instance, and if you don't have access to AS, opening the Power BI file give you an error message below.

The solution to this is to use a PowerShell script developed and shared by Steve Campbell. This script works perfectly. But in my case, I had 45 reports so instead of manually applying the script to 45 reports, I just modified it a bit to strip off the connection, save the pbix and also copy the original pbix files to another folder for backup. Please note that Steve gets 100% credit for this script, I merely modified it to suit my purposes. Please refer to blog for his instructions to use the original script.

Steps

  1. Save all the pbix files to a folder. Copy the folder path.

  1. Replace the folder path for $dir variable as shown below. Note that the folder path must end with \

  1. Run the PowerShell

  2. That's it. If you now go back to the above folder, you will see another folder created called original_pbix. This folder has the original pbix files with AS connections. Files in the base folder should now be devoid of AS connection.

  1. Open the pbix and point to the Power BI dataset. As long as all the column, table, measure names etc are the same as the AS, you should be able to successfully map the report to the Power BI dataset.

Script 


##### Script to strip-off AS Connection and save the PBIX files 
##### Original script: https://powerbi.tips/2020/05/hot-swap-power-bi-report-connections/
##### Input from Bryan Campbell, Hitachi Solutions America
##### Modified the original by Sandeep Pawar to apply en-masse to many files
##### Ver 1.0 | Last Update: 10/26/2022

<#
Instructions: 

1. Save the PBIX with Analysis Services connections to a folder
2. Change the $dir variable below to above folder path

#> 


$dir = "C:\Users\<path>\AS PBI Files\" #Be sure to have a \ at the end

"Checking if $dir  exists"
if (Test-Path -Path $dir) {

    #Copy files to a new folder for backup
    New-Item -Path $dir'original_pbix' -ItemType Directory
    Copy-Item -Path $dir'*.pbix' -Destination $dir'original_pbix'


    $pbi_files = Get-ChildItem -Path $dir -Filter *.pbix


    # Loop over each file, strip off and save the file as a zip file

    foreach ($file in $pbi_files) {

    $pathn =  $dir+$file.Name 

        Write-Output "Extracting " $file
        #Error check function
        function IsFileLocked([string]$filePath){
            Rename-Item $filePath $filePath -ErrorVariable errs -ErrorAction SilentlyContinue
            return ($errs.Count -ne 0)
        }


        #Choose file
        try {$pathn}
        catch { "Incompatible File" }


        #Check for errors
        If([string]::IsNullOrEmpty($pathn )){            
            exit } 

        elseif ( IsFileLocked($pathn) ){
            exit } 

        #Run Script
        else{    

            #Unzip pbix
            [Reflection.Assembly]::LoadWithPartialName('System.IO.Compression')
            $zipfile = $pathn.Substring(0,$pathn.Length-4) + "zip"
            Rename-Item -Path $pathn -NewName  $zipfile

            #Delete files
            $files   = 'Connections', 'SecurityBindings'
            $stream = New-Object IO.FileStream($zipfile, [IO.FileMode]::Open)
            $mode   = [IO.Compression.ZipArchiveMode]::Update
            $zip    = New-Object IO.Compression.ZipArchive($stream, $mode)
            ($zip.Entries | ? { $files -contains $_.Name }) | % { $_.Delete() }

            #Close zip
            $zip.Dispose()
            $stream.Close()
            $stream.Dispose()


        }

    }


    # Change the *.zip to *.pbix

    $stripped_models = Get-ChildItem -Path $dir -Filter *.zip
    $stripped_models | Rename-Item -newname { $_.name -replace '.zip','.pbix' }




    } 
else {
    ">>>>>>> ERROR Path:  $dir doesn't exist   <<<<<<<<<<<"
}

Thanks to my colleague Bryan Campbell for his help.