src/report/renderers/Export-PowerBiProject.ps1
|
#Requires -Version 7.0 Set-StrictMode -Version Latest $ErrorActionPreference = 'Stop' <# .SYNOPSIS Emit a real Power BI project (PBIP) — semantic model in TMDL plus authored report pages. .DESCRIPTION AB#6882, clauses B-01 through B-05. What this replaces, and why. Scout's Power BI output was four flat CSVs and a generated `.pbit` whose model had one text key, no measures, no date dimension and no report pages. Phase 0 measured the consequence: `report.pbit` was 4,689 bytes and its authored `Report/Layout` was 2,190 of them — opening it presented a blank canvas and four tables. The owner's verdict on that artefact was "a waste of my time", and it was correct: a model with no measures asks the reader to build the analysis the tool was supposed to do. A PBIP project is the fix rather than a nicer `.pbit` because it is TEXT. The semantic model is TMDL and the report is JSON, so both diff, both review, and both can be regenerated by a later run without losing hand edits to the parts this file does not own. Shape emitted: powerbi/ AzureScout.pbip AzureScout.SemanticModel/ .platform definition.pbism definition/database.tmdl model.tmdl expressions.tmdl relationships.tmdl definition/tables/{Findings,Areas,Frameworks,Gaps,Date}.tmdl AzureScout.Report/ .platform definition.pbir report.json The model imports the same star-schema CSVs the run already emits, through a `DataFolder` parameter — so moving the run folder is a one-field fix in Power BI Desktop rather than a re-import. .NOTES TMDL is INDENTATION-SENSITIVE and the indent character is a TAB. Every here-string below is tab-indented on purpose; converting them to spaces produces a project Power BI Desktop refuses to open with an unhelpful parse error. That is the single most likely way to break this file. #> # Compatibility level 1567 is the floor for TMDL-serialised models in Power BI Desktop. $Script:ScoutPbipCompatibilityLevel = 1567 function Get-ScoutPbipLineageTag { <# .SYNOPSIS A stable GUID for a model object, derived from its name. .DESCRIPTION Lineage tags identify a column or measure across model versions. Generating fresh GUIDs every run would make every regenerated project a whole-file diff and would break report visuals that reference the previous tag. Deriving them from the object's name instead means a re-run of the same model is byte-identical, which is what makes this artefact reviewable in git at all. #> [OutputType([string])] param([Parameter(Mandatory)][string]$Name) $md5 = [System.Security.Cryptography.MD5]::Create() try { $bytes = $md5.ComputeHash([System.Text.Encoding]::UTF8.GetBytes("azure-scout:$Name")) return ([guid]::new($bytes)).ToString() } finally { $md5.Dispose() } } function New-ScoutPbipColumnTmdl { <# .SYNOPSIS One TMDL column block. #> [OutputType([string])] param( [Parameter(Mandatory)][string]$Table, [Parameter(Mandatory)][string]$Name, [string]$DataType = 'string', [string]$SourceColumn = $null, [string]$FormatString = $null, [string]$SummarizeBy = 'none', [switch]$IsKey ) $src = if ($SourceColumn) { $SourceColumn } else { $Name } $tag = Get-ScoutPbipLineageTag -Name "$Table.$Name" $sb = [System.Text.StringBuilder]::new() [void]$sb.AppendLine("`tcolumn '$Name'") [void]$sb.AppendLine("`t`tdataType: $DataType") if ($IsKey) { [void]$sb.AppendLine("`t`tisKey") } if ($FormatString) { [void]$sb.AppendLine("`t`tformatString: $FormatString") } [void]$sb.AppendLine("`t`tlineageTag: $tag") [void]$sb.AppendLine("`t`tsummarizeBy: $SummarizeBy") [void]$sb.AppendLine("`t`tsourceColumn: $src") [void]$sb.AppendLine('') [void]$sb.AppendLine("`t`tannotation SummarizationSetBy = Automatic") [void]$sb.AppendLine('') return $sb.ToString() } function New-ScoutPbipMeasureTmdl { <# .SYNOPSIS One TMDL measure block. .DESCRIPTION Clause B-03. A model with zero measures is non-conformant, and it is also useless: every question a reader actually has ("what percentage are we passing", "how many high-severity gaps") is a measure, and without them the reader has to write DAX before they can read their own report. #> [OutputType([string])] param( [Parameter(Mandatory)][string]$Table, [Parameter(Mandatory)][string]$Name, [Parameter(Mandatory)][string]$Expression, [string]$FormatString = '#,0', [string]$Description = $null ) $tag = Get-ScoutPbipLineageTag -Name "$Table.measure.$Name" $sb = [System.Text.StringBuilder]::new() # A TMDL `///` description PRECEDES the object it documents and sits at the object's own # indent -- it is a doc comment, not a property. Emitting it as the first line INSIDE the # measure fails the parser with "Unexpected line type: Property!" pointing at the NEXT line, # which sends you looking at formatString when the fault is the line above it. Verified # against Power BI Desktop 2.156.951.0. if ($Description) { [void]$sb.AppendLine("`t/// $Description") } [void]$sb.AppendLine("`tmeasure '$Name' = $Expression") [void]$sb.AppendLine("`t`tformatString: $FormatString") [void]$sb.AppendLine("`t`tlineageTag: $tag") [void]$sb.AppendLine('') return $sb.ToString() } function New-ScoutPbipCsvPartitionTmdl { <# .SYNOPSIS The M partition importing one star-schema CSV through the DataFolder parameter. #> [OutputType([string])] param([Parameter(Mandatory)][string]$Table, [Parameter(Mandatory)][string]$CsvName) return @" `tpartition '$Table' = m `t`tmode: import `t`tsource = `t`t`t`tlet `t`t`t`t Source = Csv.Document(File.Contents(DataFolder & "$CsvName"), [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]), `t`t`t`t Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true]) `t`t`t`tin `t`t`t`t Headers `tannotation PBI_ResultType = Table "@ } function New-ScoutPbipTableTmdl { [OutputType([string])] param( [Parameter(Mandatory)][string]$Name, [Parameter(Mandatory)][string[]]$ColumnBlocks, [string[]]$MeasureBlocks = @(), [Parameter(Mandatory)][string]$PartitionBlock ) $sb = [System.Text.StringBuilder]::new() [void]$sb.AppendLine("table '$Name'") [void]$sb.AppendLine("`tlineageTag: $(Get-ScoutPbipLineageTag -Name "table.$Name")") [void]$sb.AppendLine('') foreach ($m in $MeasureBlocks) { [void]$sb.Append($m) } foreach ($c in $ColumnBlocks) { [void]$sb.Append($c) } [void]$sb.Append($PartitionBlock) return $sb.ToString() } function New-ScoutPbipRelationshipsTmdl { <# .SYNOPSIS The star-schema relationships. .DESCRIPTION Clause B-02. The previous model joined everything on one lowercased "framework|area" text key, which is not a star schema — it is four tables that happen to share a string. These are real many-to-one relationships from the findings fact onto the framework, area and date dimensions, which is what makes a slicer on one visual filter the others. #> [OutputType([string])] param() $rels = @( @{ Name = 'Findings_Areas'; From = "'Findings'.AreaKey"; To = "'Areas'.AreaKey" } @{ Name = 'Findings_Frameworks'; From = "'Findings'.Framework"; To = "'Frameworks'.Framework" } @{ Name = 'Gaps_Areas'; From = "'Gaps'.AreaKey"; To = "'Areas'.AreaKey" } @{ Name = 'Findings_Date'; From = "'Findings'.ScanDate"; To = "'Date'.Date" } ) $sb = [System.Text.StringBuilder]::new() foreach ($r in $rels) { [void]$sb.AppendLine("relationship $($r.Name)") [void]$sb.AppendLine("`tfromColumn: $($r.From)") [void]$sb.AppendLine("`ttoColumn: $($r.To)") [void]$sb.AppendLine('') } return $sb.ToString() } function New-ScoutPbipReportJson { <# .SYNOPSIS Three authored report pages. .DESCRIPTION Clause B-05. "The project contains authored report pages. Opening it must not present a blank canvas." Phase 0's `.pbit` presented exactly that, which is the whole reason the Power BI output was dismissed. The pages mirror the document's argument rather than the model's tables — executive summary first, then the detail — because a reader who opens the report and a reader who opens the Word file should come away with the same conclusion. Each visual's `config` is itself a JSON STRING inside the report JSON. That is the Power BI report format, not an accident of this generator: the layout is a serialised document and the visual definitions are serialised again inside it. #> [OutputType([string])] param() # ---- Visual builders ------------------------------------------------------------------- # # AB#6886. A Power BI visual needs THREE serialised blobs on its container, not one, and the # first version of this generator emitted only `config`. The result opened without error and # rendered three pages of empty placeholders, because nothing told Desktop which field goes in # which well: # # config the visual type, its layout box, and the projections (which queryRef # belongs to which role: Values / Category / Y) # query the semantic query -- the actual SELECT, plus the Binding that says how # the result set maps onto the visual's groupings # dataTransforms the field-well mapping: one `selects` entry per field, carrying its # display name, its expression, its role, and the projection ordering # # `queryName` in dataTransforms, `Name` in the query Select, and `queryRef` in the config # projections must all be the SAME string. If they diverge the visual silently renders empty, # which is exactly the failure this comment exists to stop happening twice. function New-ScoutPbiFieldExpr { # The expression for one field. A measure and a column are different node types, and # using the wrong one produces a visual that binds to nothing. param([string]$Source, [string]$Property, [switch]$IsMeasure) if ($IsMeasure) { return [ordered]@{ Measure = [ordered]@{ Expression = @{ SourceRef = @{ Source = $Source } }; Property = $Property } } } return [ordered]@{ Column = [ordered]@{ Expression = @{ SourceRef = @{ Source = $Source } }; Property = $Property } } } function New-ScoutPbiVisual { <# .SYNOPSIS One visual container, with config, query and dataTransforms all agreeing. .PARAMETER Fields Ordered list of @{ Table; Name; Role; IsMeasure; Display }. Role is the field well — Values for a card or table, Category/Y for a chart. #> param( [int]$X, [int]$Y, [int]$W, [int]$H, [string]$Name, [string]$VisualType, [string]$Title, [array]$Fields ) # One query source alias per distinct table. $sources = [ordered]@{} $from = [System.Collections.Generic.List[object]]::new() $si = 0 foreach ($f in $Fields) { if (-not $sources.Contains($f.Table)) { $alias = "s$si"; $si++ $sources[$f.Table] = $alias $from.Add([ordered]@{ Name = $alias; Entity = $f.Table; Type = 0 }) } } $select = [System.Collections.Generic.List[object]]::new() $selects = [System.Collections.Generic.List[object]]::new() $projections = [ordered]@{} $ordering = [ordered]@{} $idx = 0 foreach ($f in $Fields) { $alias = $sources[$f.Table] $queryName = "$($f.Table).$($f.Name)" $expr = New-ScoutPbiFieldExpr -Source $alias -Property $f.Name -IsMeasure:([bool]$f.IsMeasure) $entry = [ordered]@{} foreach ($k in $expr.Keys) { $entry[$k] = $expr[$k] } $entry['Name'] = $queryName $select.Add($entry) $selects.Add([ordered]@{ displayName = $(if ($f.Display) { $f.Display } else { $f.Name }) queryName = $queryName roles = @{ $f.Role = $true } expr = $expr }) if (-not $projections.Contains($f.Role)) { $projections[$f.Role] = [System.Collections.Generic.List[object]]::new() $ordering[$f.Role] = [System.Collections.Generic.List[int]]::new() } $projections[$f.Role].Add(@{ queryRef = $queryName }) $ordering[$f.Role].Add($idx) $idx++ } $query = [ordered]@{ Version = 2; From = @($from); Select = @($select) } # The binding tells Desktop how the flat result set folds onto the visual. Categorical # visuals group by their category projections; a card has a single value and no grouping. $groupings = [System.Collections.Generic.List[object]]::new() $catIdx = @() for ($i = 0; $i -lt $Fields.Count; $i++) { if ($Fields[$i].Role -in @('Category', 'Values') -and -not $Fields[$i].IsMeasure) { $catIdx += $i } } if ($catIdx.Count -gt 0) { $groupings.Add(@{ Projections = @($catIdx) }) } else { $groupings.Add(@{ Projections = @(0) }) } $binding = [ordered]@{ Primary = [ordered]@{ Groupings = @($groupings) } DataReduction = [ordered]@{ DataVolume = 4; Primary = @{ Top = @{ Count = 1000 } } } Version = 1 } $cfg = [ordered]@{ name = $Name layouts = @(@{ id = 0; position = [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H } }) singleVisual = [ordered]@{ visualType = $VisualType projections = $projections prototypeQuery = $query drillFilterOtherVisuals = $true vcObjects = [ordered]@{ title = @(@{ properties = [ordered]@{ text = @{ expr = @{ Literal = @{ Value = "'$Title'" } } } show = @{ expr = @{ Literal = @{ Value = 'true' } } } } }) } } } $queryBlob = [ordered]@{ Commands = @( [ordered]@{ SemanticQueryDataShapeCommand = [ordered]@{ Query = $query; Binding = $binding } } ) } $transforms = [ordered]@{ objects = @{} selects = @($selects) projectionOrdering = $ordering } return [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H config = ($cfg | ConvertTo-Json -Depth 40 -Compress) query = ($queryBlob | ConvertTo-Json -Depth 40 -Compress) dataTransforms = ($transforms | ConvertTo-Json -Depth 40 -Compress) filters = '[]' } } function New-CardVisual { param([int]$X, [int]$Y, [int]$W, [int]$H, [string]$Name, [string]$Table, [string]$Measure) return New-ScoutPbiVisual -X $X -Y $Y -W $W -H $H -Name $Name -VisualType 'card' -Title $Measure ` -Fields @(@{ Table = $Table; Name = $Measure; Role = 'Values'; IsMeasure = $true; Display = $Measure }) } function New-ChartVisual { param([int]$X, [int]$Y, [int]$W, [int]$H, [string]$Name, [string]$VisualType, [string]$CategoryTable, [string]$CategoryColumn, [string]$MeasureTable, [string]$Measure, [string]$Title) return New-ScoutPbiVisual -X $X -Y $Y -W $W -H $H -Name $Name -VisualType $VisualType -Title $Title ` -Fields @( @{ Table = $CategoryTable; Name = $CategoryColumn; Role = 'Category'; IsMeasure = $false; Display = $CategoryColumn } @{ Table = $MeasureTable; Name = $Measure; Role = 'Y'; IsMeasure = $true; Display = $Measure } ) } function New-TableVisual { param([int]$X, [int]$Y, [int]$W, [int]$H, [string]$Name, [string]$Table, [string[]]$Columns, [string]$Title) $fields = foreach ($c in $Columns) { @{ Table = $Table; Name = $c; Role = 'Values'; IsMeasure = $false; Display = $c } } return New-ScoutPbiVisual -X $X -Y $Y -W $W -H $H -Name $Name -VisualType 'tableEx' -Title $Title -Fields @($fields) } $pages = @( [ordered]@{ name = 'ExecutiveSummary' displayName = 'Executive summary' ordinal = 0 width = 1280 height = 720 displayOption = 1 filters = '[]' config = (@{ visibility = 0 } | ConvertTo-Json -Compress) visualContainers = @( (New-CardVisual -X 24 -Y 24 -W 280 -H 150 -Name 'cardControls' -Table 'Findings' -Measure 'Controls assessed') (New-CardVisual -X 328 -Y 24 -W 280 -H 150 -Name 'cardCompliance' -Table 'Findings' -Measure 'Compliance rate') (New-CardVisual -X 632 -Y 24 -W 280 -H 150 -Name 'cardHigh' -Table 'Findings' -Measure 'High severity gaps') (New-CardVisual -X 936 -Y 24 -W 280 -H 150 -Name 'cardNotAssessed' -Table 'Findings' -Measure 'Not assessed') (New-ChartVisual -X 24 -Y 200 -W 590 -H 480 -Name 'chartByArea' -VisualType 'barChart' ` -CategoryTable 'Areas' -CategoryColumn 'Area' -MeasureTable 'Areas' -Measure 'Area alignment score' ` -Title 'Alignment score by area') (New-ChartVisual -X 638 -Y 200 -W 578 -H 480 -Name 'chartByStatus' -VisualType 'donutChart' ` -CategoryTable 'Findings' -CategoryColumn 'Status' -MeasureTable 'Findings' -Measure 'Controls assessed' ` -Title 'Controls by status') ) } [ordered]@{ name = 'FindingsDetail' displayName = 'Findings by area' ordinal = 1 width = 1280 height = 720 displayOption = 1 filters = '[]' config = (@{ visibility = 0 } | ConvertTo-Json -Compress) visualContainers = @( (New-ChartVisual -X 24 -Y 24 -W 400 -H 300 -Name 'chartSeverity' -VisualType 'columnChart' ` -CategoryTable 'Findings' -CategoryColumn 'Severity' -MeasureTable 'Findings' -Measure 'Controls failed' ` -Title 'Failures by severity') (New-TableVisual -X 448 -Y 24 -W 808 -H 672 -Name 'tblFindings' -Table 'Findings' ` -Columns @('Id', 'Framework', 'Area', 'Severity', 'Status', 'Title') -Title 'All findings') (New-ChartVisual -X 24 -Y 348 -W 400 -H 348 -Name 'chartFrameworks' -VisualType 'barChart' ` -CategoryTable 'Frameworks' -CategoryColumn 'Framework' -MeasureTable 'Frameworks' -Measure 'Framework score' ` -Title 'Score by framework') ) } [ordered]@{ name = 'Gaps' displayName = 'Prioritised gaps' ordinal = 2 width = 1280 height = 720 displayOption = 1 filters = '[]' config = (@{ visibility = 0 } | ConvertTo-Json -Compress) visualContainers = @( (New-CardVisual -X 24 -Y 24 -W 280 -H 150 -Name 'cardGaps' -Table 'Gaps' -Measure 'Open gaps') (New-TableVisual -X 328 -Y 24 -W 928 -H 672 -Name 'tblGaps' -Table 'Gaps' ` -Columns @('Severity', 'Framework', 'Area', 'Title', 'EvidenceCount') -Title 'Prioritised gaps') ) } ) # The report-level config is where the THEME lives, and Power BI Desktop dereferences it # unconditionally while building the ribbon. Omitting themeCollection does not fall back to a # default -- Desktop throws "Cannot read properties of undefined (reading 'customTheme')" from # inside desktop.min.js and the canvas never renders, even though the model has loaded fine # and the project is otherwise valid. Verified against Power BI Desktop 2.156.951.0. # # baseTheme names a theme Desktop ships, so nothing has to be packaged alongside the report. $reportConfig = [ordered]@{ version = '5.43' activeSectionIndex = 0 themeCollection = [ordered]@{ baseTheme = [ordered]@{ name = 'CY24SU10' version = '5.61' type = 2 reportVersion = '5.43' } } objects = [ordered]@{ section = @( @{ properties = @{ verticalAlignment = @{ expr = @{ Literal = @{ Value = "'Top'" } } } } } ) } } $report = [ordered]@{ '$schema' = 'https://developer.microsoft.com/json-schemas/fabric/item/report/definition/report/1.0.0/schema.json' config = ($reportConfig | ConvertTo-Json -Depth 20 -Compress) layoutOptimization = 0 resourcePackages = @( [ordered]@{ resourcePackage = [ordered]@{ disabled = $false items = @(@{ name = 'CY24SU10'; path = 'BaseThemes/CY24SU10.json'; type = 202 }) name = 'SharedResources' type = 2 } } ) sections = $pages } return ($report | ConvertTo-Json -Depth 40) } function Export-ScoutPowerBiProject { <# .SYNOPSIS Write the PBIP project next to the star-schema CSVs. .PARAMETER PowerBiDir The run's powerbi/ folder — the CSVs are already there and the project reads them. .OUTPUTS The path to the .pbip file. #> [OutputType([string])] param([Parameter(Mandatory)][string]$PowerBiDir, [string]$ProjectName = 'AzureScout') $modelDir = Join-Path $PowerBiDir "$ProjectName.SemanticModel" $defDir = Join-Path $modelDir 'definition' $tablesDir = Join-Path $defDir 'tables' $reportDir = Join-Path $PowerBiDir "$ProjectName.Report" foreach ($d in @($modelDir, $defDir, $tablesDir, $reportDir)) { New-Item -ItemType Directory -Path $d -Force | Out-Null } # ---- .pbip ------------------------------------------------------------------------- $pbipPath = Join-Path $PowerBiDir "$ProjectName.pbip" # These four $schema URLs are NOT decorative. Power BI Desktop validates each one against a # regex before it will open the project, and rejects the whole thing with # "UnrecognizedSchemaVersion" if it does not match -- which is how the first version of this # generator failed: every file was structurally correct and Desktop would not open any of it. # Verified by opening the generated project in Power BI Desktop 2.156.951.0, not by reading # the docs. Do not "tidy" these into a consistent-looking family; they genuinely differ # (pbip/pbipProperties, item/<type>/definitionProperties, gitIntegration/platformProperties). ([ordered]@{ '$schema' = 'https://developer.microsoft.com/json-schemas/fabric/pbip/pbipProperties/1.0.0/schema.json' version = '1.0' artifacts = @(@{ report = @{ path = "$ProjectName.Report" } }) settings = [ordered]@{ enableAutoRecovery = $true } } | ConvertTo-Json -Depth 10) | Set-Content -LiteralPath $pbipPath -Encoding utf8 # ---- semantic model ---------------------------------------------------------------- ([ordered]@{ '$schema' = 'https://developer.microsoft.com/json-schemas/fabric/item/semanticModel/definitionProperties/1.0.0/schema.json' version = '4.2' settings = @{} } | ConvertTo-Json -Depth 10) | Set-Content -LiteralPath (Join-Path $modelDir 'definition.pbism') -Encoding utf8 ([ordered]@{ '$schema' = 'https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json' metadata = [ordered]@{ type = 'SemanticModel'; displayName = $ProjectName } config = [ordered]@{ version = '2.0'; logicalId = (Get-ScoutPbipLineageTag -Name 'platform.model') } } | ConvertTo-Json -Depth 10) | Set-Content -LiteralPath (Join-Path $modelDir '.platform') -Encoding utf8 "database`n`tcompatibilityLevel: $Script:ScoutPbipCompatibilityLevel`n" | Set-Content -LiteralPath (Join-Path $defDir 'database.tmdl') -Encoding utf8 -NoNewline # DataFolder is a real M parameter, not a hardcoded path, so a moved run folder is a # one-field fix in Power BI Desktop rather than a rebuild of every query. $folderLiteral = ($PowerBiDir -replace '\\', '\\' -replace '"', '""') $expressions = @" expression DataFolder = "$folderLiteral\\" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true] `tlineageTag: $(Get-ScoutPbipLineageTag -Name 'expression.DataFolder') `tannotation PBI_NavigationStepName = Navigation "@ $expressions | Set-Content -LiteralPath (Join-Path $defDir 'expressions.tmdl') -Encoding utf8 (New-ScoutPbipRelationshipsTmdl) | Set-Content -LiteralPath (Join-Path $defDir 'relationships.tmdl') -Encoding utf8 $model = @" model Model `tculture: en-GB `tdefaultPowerBIDataSourceVersion: powerBI_V3 `tdiscourageImplicitMeasures `tsourceQueryCulture: en-GB `tannotation PBI_QueryOrder = ["Findings","Areas","Frameworks","Gaps"] ref table Findings ref table Areas ref table Frameworks ref table Gaps ref table Date "@ $model | Set-Content -LiteralPath (Join-Path $defDir 'model.tmdl') -Encoding utf8 # ---- tables ------------------------------------------------------------------------- # Clause B-03. Every measure here answers a question a reader actually asks; a model that # made them write the DAX themselves is why the previous output was dismissed. $findingsMeasures = @( (New-ScoutPbipMeasureTmdl -Table 'Findings' -Name 'Controls assessed' -Expression "COUNTROWS('Findings')" -Description 'Every control evaluated in this run, including those pending manual review.') (New-ScoutPbipMeasureTmdl -Table 'Findings' -Name 'Controls passed' -Expression "CALCULATE(COUNTROWS('Findings'), 'Findings'[Status] = ""Pass"")") (New-ScoutPbipMeasureTmdl -Table 'Findings' -Name 'Controls failed' -Expression "CALCULATE(COUNTROWS('Findings'), 'Findings'[Status] = ""Fail"")") (New-ScoutPbipMeasureTmdl -Table 'Findings' -Name 'Not assessed' -Expression "CALCULATE(COUNTROWS('Findings'), 'Findings'[Status] IN {""Manual"", ""Unknown""})" -Description 'Clause W-17: pending manual review is a state of its own, never a zero and never a pass.') (New-ScoutPbipMeasureTmdl -Table 'Findings' -Name 'Compliance rate' ` -Expression "DIVIDE([Controls passed], [Controls assessed] - [Not assessed])" -FormatString '0.0%' ` -Description 'Passes as a share of what could actually be evaluated -- manual controls are excluded from the denominator rather than counted as failures.') (New-ScoutPbipMeasureTmdl -Table 'Findings' -Name 'High severity gaps' ` -Expression "CALCULATE(COUNTROWS('Findings'), 'Findings'[Severity] = ""high"", 'Findings'[Status] = ""Fail"")") ) $findingsColumns = @( (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'AreaKey') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Id') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Framework') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Area') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Severity') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Status') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'EvidenceCount' -DataType 'int64' -SummarizeBy 'sum' -FormatString '#,0') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Title') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Remediation') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'Manual') (New-ScoutPbipColumnTmdl -Table 'Findings' -Name 'ScanDate' -DataType 'dateTime' -FormatString 'Long Date') ) (New-ScoutPbipTableTmdl -Name 'Findings' -ColumnBlocks $findingsColumns -MeasureBlocks $findingsMeasures ` -PartitionBlock (New-ScoutPbipCsvPartitionTmdl -Table 'Findings' -CsvName 'fact_findings.csv')) | Set-Content -LiteralPath (Join-Path $tablesDir 'Findings.tmdl') -Encoding utf8 (New-ScoutPbipTableTmdl -Name 'Areas' ` -ColumnBlocks @( (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'AreaKey') (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'Framework') (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'Area') (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'Score' -DataType 'double' -SummarizeBy 'average' -FormatString '#,0.0') (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'Pass' -DataType 'int64' -SummarizeBy 'sum' -FormatString '#,0') (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'Partial' -DataType 'int64' -SummarizeBy 'sum' -FormatString '#,0') (New-ScoutPbipColumnTmdl -Table 'Areas' -Name 'Fail' -DataType 'int64' -SummarizeBy 'sum' -FormatString '#,0') ) ` -MeasureBlocks @( (New-ScoutPbipMeasureTmdl -Table 'Areas' -Name 'Area alignment score' -Expression "AVERAGE('Areas'[Score])" -FormatString '#,0.0') (New-ScoutPbipMeasureTmdl -Table 'Areas' -Name 'Weakest area score' -Expression "MIN('Areas'[Score])" -FormatString '#,0.0') ) ` -PartitionBlock (New-ScoutPbipCsvPartitionTmdl -Table 'Areas' -CsvName 'fact_area_scores.csv')) | Set-Content -LiteralPath (Join-Path $tablesDir 'Areas.tmdl') -Encoding utf8 (New-ScoutPbipTableTmdl -Name 'Frameworks' ` -ColumnBlocks @( (New-ScoutPbipColumnTmdl -Table 'Frameworks' -Name 'Framework') (New-ScoutPbipColumnTmdl -Table 'Frameworks' -Name 'Score' -DataType 'double' -SummarizeBy 'average' -FormatString '#,0.0') ) ` -MeasureBlocks @( (New-ScoutPbipMeasureTmdl -Table 'Frameworks' -Name 'Framework score' -Expression "AVERAGE('Frameworks'[Score])" -FormatString '#,0.0') ) ` -PartitionBlock (New-ScoutPbipCsvPartitionTmdl -Table 'Frameworks' -CsvName 'fact_framework.csv')) | Set-Content -LiteralPath (Join-Path $tablesDir 'Frameworks.tmdl') -Encoding utf8 (New-ScoutPbipTableTmdl -Name 'Gaps' ` -ColumnBlocks @( (New-ScoutPbipColumnTmdl -Table 'Gaps' -Name 'AreaKey') (New-ScoutPbipColumnTmdl -Table 'Gaps' -Name 'Framework') (New-ScoutPbipColumnTmdl -Table 'Gaps' -Name 'Area') (New-ScoutPbipColumnTmdl -Table 'Gaps' -Name 'Severity') (New-ScoutPbipColumnTmdl -Table 'Gaps' -Name 'Title') (New-ScoutPbipColumnTmdl -Table 'Gaps' -Name 'EvidenceCount' -DataType 'int64' -SummarizeBy 'sum' -FormatString '#,0') ) ` -MeasureBlocks @( (New-ScoutPbipMeasureTmdl -Table 'Gaps' -Name 'Open gaps' -Expression "COUNTROWS('Gaps')") (New-ScoutPbipMeasureTmdl -Table 'Gaps' -Name 'Resources affected' -Expression "SUM('Gaps'[EvidenceCount])") ) ` -PartitionBlock (New-ScoutPbipCsvPartitionTmdl -Table 'Gaps' -CsvName 'dim_gaps.csv')) | Set-Content -LiteralPath (Join-Path $tablesDir 'Gaps.tmdl') -Encoding utf8 # Clause B-04. A calculated date dimension rather than an imported one: it needs no CSV, it # cannot go stale, and it gives the model a real date table to mark so time intelligence # works when someone adds it. $dateTag = Get-ScoutPbipLineageTag -Name 'table.Date' @" table Date `tlineageTag: $dateTag `tdataCategory: Time $(New-ScoutPbipColumnTmdl -Table 'Date' -Name 'Date' -DataType 'dateTime' -FormatString 'Long Date' -IsKey -SourceColumn '[Date]') `tcolumn Year = YEAR('Date'[Date]) `t`tdataType: int64 `t`tlineageTag: $(Get-ScoutPbipLineageTag -Name 'Date.Year') `t`tsummarizeBy: none `tcolumn Month = FORMAT('Date'[Date], "yyyy-MM") `t`tdataType: string `t`tlineageTag: $(Get-ScoutPbipLineageTag -Name 'Date.Month') `t`tsummarizeBy: none `tpartition Date = calculated `t`tmode: import `t`tsource = CALENDAR(DATE(YEAR(TODAY()) - 2, 1, 1), DATE(YEAR(TODAY()) + 1, 12, 31)) `tannotation PBI_ResultType = Table "@ | Set-Content -LiteralPath (Join-Path $tablesDir 'Date.tmdl') -Encoding utf8 # ---- report -------------------------------------------------------------------------- ([ordered]@{ # version 1.0 is the CLASSIC report definition -- a definition.pbir alongside a single # report.json. The 4.x versions signal the enhanced report format (PBIR), where the # report is a definition/pages/ tree instead; declaring 4.x while shipping report.json # tells Desktop to look for files that are not there. '$schema' = 'https://developer.microsoft.com/json-schemas/fabric/item/report/definitionProperties/1.0.0/schema.json' version = '1.0' datasetReference = [ordered]@{ byPath = [ordered]@{ path = "../$ProjectName.SemanticModel" } } } | ConvertTo-Json -Depth 10) | Set-Content -LiteralPath (Join-Path $reportDir 'definition.pbir') -Encoding utf8 ([ordered]@{ '$schema' = 'https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json' metadata = [ordered]@{ type = 'Report'; displayName = "$ProjectName assessment" } config = [ordered]@{ version = '2.0'; logicalId = (Get-ScoutPbipLineageTag -Name 'platform.report') } } | ConvertTo-Json -Depth 10) | Set-Content -LiteralPath (Join-Path $reportDir '.platform') -Encoding utf8 (New-ScoutPbipReportJson) | Set-Content -LiteralPath (Join-Path $reportDir 'report.json') -Encoding utf8 return $pbipPath } |