cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sau001
Helper IV
Helper IV

How to do a code review of a Power BI dataset before publishing

Hi All,

Consider the following scenario

  • 2 employees working remotely
  • working on the same dataset which queries on SQL views in import mode
  • making iterative changes 
  • Publishing online for the wider company
  • The dataset does not carry any visualizations. The visualizations are in different reports which directly invoke the Power BI online dataset that was published in the previous steps

Challenge

  • Employee1 adds a calculated field (as an example)
  • Employee1 changes the DateTime format of a field to Date
  • How does Employee 2 review the changes to the model?
  • How does Employee 2 compare with the earlier version and find out what in the model has changed?
  • How does Employee 2 spot any inadvertent/undesired changes to the report model (e.g. broken relationship)

Any best practices?

Is it feasible to export a template out of the PBIX  --> rename to PBIT to ZIP --> and then do a GIT compare on 'DataModelSchema' file ?

 

Thanks,

Sau

 

3 REPLIES 3
sau001
Helper IV
Helper IV

Hi @lbendlin ,

 

Slightly modified version of your PS script 

  • I am using Expand-Archive
  • The paths are relative because I have the PBIT file and the PS under Git control

 

Visual Studio folder hiearchy

sau001_0-1614690017650.png

 

PowerShell script - ExtractPbitTemplate.ps1

 

Set-StrictMode -Version "2.0"
$ErrorActionPreference="Stop"
cls

$scriptFolder=$PSScriptRoot
"Current folder is $scriptFolder"

$pathToPowerBiTemplateFile=Join-Path -Path $scriptFolder -ChildPath "..\MssqlParticipants.pbit"
if ((Test-Path -Path $pathToPowerBiTemplateFile) -eq $false)
{
    Write-Error "Could not find file $pathToPowerBiTemplateFile"
}

$now=get-date
$tempSubFolder=("PowerBI-{0}" -f $now.tostring("dd-MMM-YYYY-HH-mm-ss"))
$tempWorkingFolder=Join-Path -Path $env:TEMP -ChildPath $tempSubFolder

if (Test-Path $tempWorkingFolder) 
{
    Write-Error ("The temp folder already  exists {0}" -f $tempWorkingFolder)
}
else
{
    New-Item -Path $tempWorkingFolder -ItemType Directory
    "Temp folder $tempWorkingFolder was created"
}


#
#Copy PBIT with a ZIP extension and extract all contents
#
$newPbitFile=Join-Path -Path $tempWorkingFolder -ChildPath "NewPbit.zip"
Copy-Item -Path $pathToPowerBiTemplateFile -Destination $newPbitFile
$folderForTemplateExtraction=Join-Path -Path $tempWorkingFolder -ChildPath "TemplateContents"
Expand-Archive -Path $newPbitFile -DestinationPath $folderForTemplateExtraction
#
#Read the content of the Model and save as a readable text
#

$contentsOfDataModelSchema=Get-Content -Path "$folderForTemplateExtraction\DataModelSchema" -Encoding Unicode

$folderVsNetTemplateContents=Join-Path -Path $scriptFolder -ChildPath "..\CorlifeMssql"
$fullPathToVsNetTemplate=Join-Path -Path $folderVsNetTemplateContents -ChildPath "DataModelSchema"
[System.IO.File]::WriteAllLines($fullPathToVsNetTemplate, $contentsOfDataModelSchema)

 

 

 

lbendlin
Super User III
Super User III

I strongly recommend you look into ALM Toolkit.  But even then your best weapon is communication.

 

Your git based approach definitely doesn't hurt  (btw you can export to PBIT directly).

 

Here's a little script I use to extract the PBIT contents into a git friendly version:

 

# Alias for 7-zip 
if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"} 
set-alias sz "$env:ProgramFiles\7-Zip\7z.exe" 
# set location
Set-Location -Path C:\Users\xxx\Documents\GitHub\yyy
# get .pbit files
$files = Get-ChildItem -Recurse -Include *.pbit | Select Name,FullName,LastWriteTime
for ($i = 0; $i -lt $files.Count; $i++) {
    if($files[$i].LastWriteTime -gt (Get-Date).AddDays(-2)) {
        Write-Host $files[$i].Name
        $p = $files[$i].FullName
        $e = $p.LastIndexOf(".")
        #target folder
        $d = $p.Substring(0,$e)
        #Write-Host $d
        # remove entire folder, so we don't have to care about removing individual items
        Remove-Item -LiteralPath $d -Recurse -Force 
        # 7Zip doesn't need the renaming
        sz x "$p" -o"$d" -aoa | Out-Null
        Rename-Item -LiteralPath ($d + "\Report\Layout") -NewName "Layout.json" -Force -ErrorAction Ignore
        Rename-Item -LiteralPath ($d + "\Connections") -NewName "Connections.json" -Force -ErrorAction Ignore
        Rename-Item -LiteralPath ($d + "\DiagramLayout") -NewName "DiagramLayout.json" -Force -ErrorAction Ignore
        Rename-Item -LiteralPath ($d + "\DiagramState") -NewName "DiagramState.json" -Force -ErrorAction Ignore
        #extract DataMashup
        #sz x "$d\DataMashup" -o"$d\Mashup" -aoa | Out-Null
        #clean up DataModelSchema
        $DMS =  Get-Content -Raw -Path ($d + "\DataModelSchema") -Encoding Unicode  | ConvertFrom-Json
        #new meta format doesn't have datasources. Now $DMS.model.tables
        if($DMS.compatibilityLevel -lt 1520) {
            $DMS.model.dataSources | ForEach-Object -Process { 
                #find mashup part of connectionString
                $c1 = $_.connectionString.IndexOf("Mashup=", [StringComparison]"CurrentCultureIgnoreCase")
                #no mashup for direct query
                if( $c1 -gt 1 ) {
                    $c2 = $_.connectionString.LastIndexOf("Location=", [StringComparison]"CurrentCultureIgnoreCase")
                    $m = $_.connectionString.Substring($c1 + 7,$c2-$c1-8)
                    #remove quotes if needed
                    $m = $m -replace '"',""
                    #$m.Length
                    $_.connectionString = $_.connectionString.Substring(0,$c1 + 7) + "..." + $_.connectionString.Substring($c2-1,$_.connectionString.Length - $c2 +1)
                    #$_.name 
                    # write connection zip file - Base64 encoded mashup string
                    #$bytes = [Convert]::FromBase64String($m) 
                    #[IO.File]::WriteAllBytes($d + "\" + $_.name + ".zip", $bytes)
                }
            }
        }
        # write corrected Json
        $DMS | ConvertTo-Json -Depth 3 | Add-Content -LiteralPath ($d + "\DataModelSchema.json")
    }
}

 

Keep in mind that the PBIX/PBIT format is constantly changing, so you need to factor in code maintenance.

 

Thank you @lbendlin . I am trying your PowerShell idea. Looks like that a sound approach - under the current limitations.

 

I am still experimenting.

 

BTW - I was able to use Expand-Archive instead of 7z.exe

 

Cheers,

Sau

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors