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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors