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() # A prototypeQuery Select entry for one measure on one table. function New-Projection { param([string]$Table, [string]$Measure, [string]$Alias) return @{ queryRef = "$Table.$Measure" entity = $Table measure = $Measure alias = $Alias } } function New-CardVisual { param([int]$X, [int]$Y, [int]$W, [int]$H, [string]$Name, [string]$Table, [string]$Measure) $cfg = [ordered]@{ name = $Name layouts = @(@{ id = 0; position = [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H } }) singleVisual = [ordered]@{ visualType = 'card' projections = [ordered]@{ Values = @(@{ queryRef = "$Table.$Measure" }) } prototypeQuery = [ordered]@{ Version = 2 From = @(@{ Name = 't'; Entity = $Table; Type = 0 }) Select = @( [ordered]@{ Measure = [ordered]@{ Expression = @{ SourceRef = @{ Source = 't' } } Property = $Measure } Name = "$Table.$Measure" } ) } drillFilterOtherVisuals = $true vcObjects = [ordered]@{ title = @(@{ properties = [ordered]@{ text = @{ expr = @{ Literal = @{ Value = "'$Measure'" } } }; show = @{ expr = @{ Literal = @{ Value = 'true' } } } } }) } } } return [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H config = ($cfg | ConvertTo-Json -Depth 30 -Compress) filters = '[]' } } 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) $cfg = [ordered]@{ name = $Name layouts = @(@{ id = 0; position = [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H } }) singleVisual = [ordered]@{ visualType = $VisualType projections = [ordered]@{ Category = @(@{ queryRef = "$CategoryTable.$CategoryColumn" }) Y = @(@{ queryRef = "$MeasureTable.$Measure" }) } prototypeQuery = [ordered]@{ Version = 2 From = @( @{ Name = 'c'; Entity = $CategoryTable; Type = 0 } @{ Name = 'm'; Entity = $MeasureTable; Type = 0 } ) Select = @( [ordered]@{ Column = [ordered]@{ Expression = @{ SourceRef = @{ Source = 'c' } } Property = $CategoryColumn } Name = "$CategoryTable.$CategoryColumn" } [ordered]@{ Measure = [ordered]@{ Expression = @{ SourceRef = @{ Source = 'm' } } Property = $Measure } Name = "$MeasureTable.$Measure" } ) } drillFilterOtherVisuals = $true vcObjects = [ordered]@{ title = @(@{ properties = [ordered]@{ text = @{ expr = @{ Literal = @{ Value = "'$Title'" } } }; show = @{ expr = @{ Literal = @{ Value = 'true' } } } } }) } } } return [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H config = ($cfg | ConvertTo-Json -Depth 30 -Compress) filters = '[]' } } function New-TableVisual { param([int]$X, [int]$Y, [int]$W, [int]$H, [string]$Name, [string]$Table, [string[]]$Columns, [string]$Title) $from = @(@{ Name = 't'; Entity = $Table; Type = 0 }) $select = foreach ($c in $Columns) { [ordered]@{ Column = [ordered]@{ Expression = @{ SourceRef = @{ Source = 't' } }; Property = $c } Name = "$Table.$c" } } $cfg = [ordered]@{ name = $Name layouts = @(@{ id = 0; position = [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H } }) singleVisual = [ordered]@{ visualType = 'tableEx' projections = [ordered]@{ Values = @($Columns | ForEach-Object { @{ queryRef = "$Table.$_" } }) } prototypeQuery = [ordered]@{ Version = 2; From = $from; Select = @($select) } drillFilterOtherVisuals = $true vcObjects = [ordered]@{ title = @(@{ properties = [ordered]@{ text = @{ expr = @{ Literal = @{ Value = "'$Title'" } } }; show = @{ expr = @{ Literal = @{ Value = 'true' } } } } }) } } } return [ordered]@{ x = $X; y = $Y; z = 0; width = $W; height = $H config = ($cfg | ConvertTo-Json -Depth 30 -Compress) filters = '[]' } } $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 } |