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

[Anonymous]

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

[lbendlin]

7Zip still works for the extract but the internal format keeps changing rapidly. Better to use BIM or the new toy TMDL.

Chris Webb's BI Blog: Power BI TMDL And Version Control Announcements From SQLBits (crossjoin.co.uk)

[Anonymous]

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

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]

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.

[Anonymous]

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

[mcarranza]

Is this still supposed to work? I am getting errors when uncompressing the renamed file (.zip). I am trying all methods (7zip, Expand-Archive) and all fail