PowerBI/Analysis Services Datasets Checks

28 December 2021

Recently we have we migrated successfully our on-premises data warehouse to the Azure platform. During this project we improved many things, e.g. the way we develop a dataset in Tabular Editor. We are using scripts as part of the advanced scripting option for creating relations, role playing date dimensions and setting formatting strings. With scripting we ensure that all datasets follow the same technichal conventions and that the end user has the same experience when using several datasets. In the daily rush it might happens that a developer e.g. did not set a proper formatting string because it forgot to use the script or set a property incorrectly. So, we decided to check several things on a regular basis and if needed to fix the issue.

We have multiple datasets so it will be a hassle to check all measures by hand. In Tabular Editor you can use a script to export some properties to a text file. The downside is the script exports only the current dataset open in Tabular Editor and not all datasets we have. I decided to write a simple Powershell script to fetch all measures formatting strings and the table properties IsHidden and IsPrivate of all datasets. We store our datasets as json files and with Powershell it is very easy to read its content.

All fetched properties are exported to a text file and the table properties are translated into a meaningful description rather than the value true, false or <empty> string. In Excel we can analyze the results and set the right property value where needed. In the generated text file it is possible to see an entry when a fact table does not contain any measures. Of course you can add more checks to the script.

Have fun!

Below the Powershell script: 


Clear-Host;
Push-Location $PSScriptRoot;

[string]$fldPBI = "C:\Users\e.schoemaker_ym\source\repos\Integraal%20Data%20Platform%20Ymere%20(IDPY)\PowerBI\premium\YRIS2.0 - Datasets";
[string]$Drive = "z:"
[string]$ScreenResult = "";
[array]$Path = "";
[string]$ResultFile = Join-Path -Path $PWD.Path -ChildPath "DataSet_$(Get-Date -format "yyyyMMdd").txt";

# Assign a new drive to the dataset folder,this prevents issues with long names
subst $Drive $fldPBI | Out-Null;

# Delete result file and create a new one
if (Test-Path -Path $ResultFile) {
    Remove-Item -Path $ResultFile | Out-Null;
};
Add-Content -Path $ResultFile -Value "DataSet`tTable`tObject`tProperty`tMessage" -Encoding Unicode;

Get-ChildItem -Path $Drive | ForEach-Object {
    Write-Host "Start: " $_.FullName -ForegroundColor Green;

    # Get all measerus of all datasets and get format string
    # Export format string to result file
    Get-ChildItem -Path (Join-path $_.FullName -ChildPath "tables") | `
        Where-Object { $_.Name.StartsWith("FCT") } | `
        ForEach-Object {
        Get-ChildItem -Path (Join-path $_.FullName -ChildPath "measures") -ErrorVariable ErrVar -ErrorAction SilentlyContinue | `
            ForEach-Object {

            $ScreenResult = "";
            $ScreenResult = $(Get-Content -path $_.FullName -Encoding UTF8 | ConvertFrom-Json | Select-Object -Property "formatString").formatString;

            If ([string]::IsNullOrEmpty($ScreenResult)) {
                $ScreenResult = "";
            };
                        
            $Path = $_.FullName.Split("\");

            Add-Content -Path $ResultFile -Value "$($Path[1])`t$($Path[3])`t$($_.BaseName)`tFormatString`t$ScreenResult" -Encoding Unicode;
        };
        
        # Write an error line, no measures are found
        If (!([string]::IsNullOrEmpty($ErrVar[0]))) {
            $Path = $_.FullName.Split("\");

            Add-Content -Path $ResultFile -Value "$($Path[1])`t$($Path[3])`t$($_.BaseName)`tError`tNo Measures" -Encoding Unicode;           
        };
    };

    # Get all tables and fecht the IsHidden and IsPrivate property
    # Write result to result file
    Get-ChildItem -Path (Join-path $_.FullName -ChildPath "tables") | `
        ForEach-Object {
        Get-ChildItem -Path $_.FullName -Filter "*.json" | `
            ForEach-Object {
     
            $ScreenResult = "";
            $ScreenResult = $(Get-Content -path $_.FullName -Encoding UTF8 | ConvertFrom-Json | Select-Object -Property "isHidden").isHidden;

            if ($ScreenResult -eq "True" ) {
                $ScreenResult = "is Hidden";
            }
            else {
                $ScreenResult = "is not Hidden";
            };                        
            
            $Path = $_.FullName.Split("\");

            Add-Content -Path $ResultFile -Value "$($Path[1])`t$($Path[3])`t$($_.BaseName)`tisHidden`t$ScreenResult" -Encoding Unicode;    
   
            $ScreenResult = "";
            $ScreenResult = $(Get-Content -path $_.FullName -Encoding UTF8 | ConvertFrom-Json | Select-Object -Property "isPrivate").isPrivate;

            if ($ScreenResult -eq "True" ) {
                $ScreenResult = "Is Private";
            }
            else {
                $ScreenResult = "Is not private";
            };
            Add-Content -Path $ResultFile -Value "$($Path[1])`t$($Path[3])`t$($_.BaseName)`tisPrivate`t$ScreenResult" -Encoding Unicode;   
        };
    };
        
    Write-Host "End: " $_.FullName -ForegroundColor Green;
};

Write-Host "ResultFile: " $ResultFile -ForegroundColor White;