PowerBIMetaDataAssessment

<#
created by Claude AI!
to be reviewed!!!
#>

# ============================================================================
# Power BI Governance Assessment - COMPREHENSIVE HTML REPORT (FIXED)
# ============================================================================

$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$reportDate = Get-Date -Format "MMMM dd, yyyy HH:mm"

# Import your scan results
$workspaces = Import-Csv "Workspaces_*.csv"
$datasets = Import-Csv "Datasets_*.csv"
$reports = Import-Csv "Reports_*.csv"
$datasources = Import-Csv "DataSources_*.csv"
$measures = Import-Csv "Measures_*.csv"
$lineage = Import-Csv "Lineage_*.csv"
$userAccess = Import-Csv "UserAccess_*.csv"

# ============================================================================
# PERFORM ALL ANALYSES (capture results in variables)
# ============================================================================

# 1. ENDORSEMENT ANALYSIS
$endorsementBreakdown = $datasets | Group-Object Endorsement | 
    Select-Object @{N='Status';E={$_.Name}}, Count, @{N='Percentage';E={[Math]::Round(($_.Count / $datasets.Count) * 100, 1)}}
$unendorsed = $datasets | Where-Object { $_.Endorsement -eq "None" }
$certified = $datasets | Where-Object { $_.Endorsement -eq "Certified" }
$promoted = $datasets | Where-Object { $_.Endorsement -eq "Promoted" }

# 2. SENSITIVITY LABELS
$labelBreakdown = $datasets | Group-Object SensitivityLabel | 
    Select-Object @{N='Label';E={$_.Name}}, Count, @{N='Percentage';E={[Math]::Round(($_.Count / $datasets.Count) * 100, 1)}}
$unlabeled = $datasets | Where-Object { $_.SensitivityLabel -eq "None" }

# 3. ORPHANED CONTENT
$datasetIds = $datasets | Select-Object -ExpandProperty DatasetId
$orphanedReports = $reports | Where-Object { 
    $_.DatasetId -and $datasetIds -notcontains $_.DatasetId 
}

$datasetIdsWithSources = $datasources | Select-Object -ExpandProperty DatasetId -Unique
$datasetsNoSources = $datasets | Where-Object { 
    $datasetIdsWithSources -notcontains $_.DatasetId -and 
    $_.ContentProviderType -ne "PbixInImportMode"
}

# 4. GATEWAY DEPENDENCIES
$gatewayDatasets = $datasets | Where-Object { $_.IsOnPremGatewayRequired -eq "True" }
$gatewayBreakdown = $datasources | Where-Object { $_.GatewayId } | 
    Group-Object GatewayId | 
    Select-Object @{N='GatewayId';E={$_.Name}}, Count | 
    Sort-Object Count -Descending

# 5. DATA SOURCE TYPES
$sourceTypeBreakdown = $datasources | 
    Group-Object DatasourceType | 
    Select-Object @{N='Type';E={$_.Name}}, Count | 
    Sort-Object Count -Descending

# Categorize datasets by provider type
$pbixImportDatasets = $datasets | Where-Object { $_.ContentProviderType -eq "PbixInImportMode" }
$pbixWithSources = $pbixImportDatasets | Where-Object { $datasetIdsWithSources -contains $_.DatasetId }
$pbixWithoutSources = $pbixImportDatasets | Where-Object { $datasetIdsWithSources -notcontains $_.DatasetId }

$excelDatasets = $datasets | Where-Object { $_.ContentProviderType -eq "Excel" }

$fileBasedTypes = @("File", "Folder", "SharePoint.Files", "SharePoint.Folder", "Web")
$fileBasedSources = $datasources | Where-Object { $fileBasedTypes -contains $_.DatasourceType }
$fileBasedDatasetIds = $fileBasedSources | Select-Object -ExpandProperty DatasetId -Unique
$fileBasedDatasets = $datasets | Where-Object { $fileBasedDatasetIds -contains $_.DatasetId }

$databaseTypes = @("Sql", "Oracle", "MySql", "PostgreSql", "Teradata", "Snowflake", "AzureSqlDatabase")
$databaseSources = $datasources | Where-Object { $databaseTypes -contains $_.DatasourceType }
$databaseDatasetIds = $databaseSources | Select-Object -ExpandProperty DatasetId -Unique

$dataflowDatasets = $datasets | Where-Object { 
    $_.ContentProviderType -eq "Dataflow" -or $_.UpstreamDataflowCount -gt 0
}

# 6. WORKSPACE HEALTH
$sharedCapacity = $workspaces | Where-Object { $_.CapacityId -eq "" -or $_.CapacityId -eq $null }
$premiumCapacity = $workspaces | Where-Object { $_.CapacityId -ne "" -and $_.CapacityId -ne $null }
$inactiveWorkspaces = $workspaces | Where-Object { $_.State -ne "Active" }
$emptyWorkspaces = $workspaces | Where-Object { 
    $_.DatasetCount -eq 0 -and $_.ReportCount -eq 0 -and $_.DashboardCount -eq 0 
}

$workspaceTypeBreakdown = $workspaces | Group-Object Type | 
    Select-Object @{N='Type';E={$_.Name}}, Count

# 7. REFRESH CONFIGURATION
$refreshableDatasets = $datasets | Where-Object { $_.IsRefreshable -eq "True" }
$nonRefreshable = $datasets | Where-Object { $_.IsRefreshable -eq "False" }

# 8. RLS
$rlsDatasets = $datasets | Where-Object { $_.IsEffectiveIdentityRequired -eq "True" }

# 9. DATASET REUSE
$datasetUsage = $reports | Group-Object DatasetId | 
    Select-Object @{N='DatasetId';E={$_.Name}}, Count | 
    Sort-Object Count -Descending

$wellReusedDatasets = $datasetUsage | Where-Object { $_.Count -ge 3 }
$singleUseDatasets = $datasetUsage | Where-Object { $_.Count -eq 1 }

$potentialDuplicates = $datasets | 
    Group-Object DatasetName | 
    Where-Object { $_.Count -gt 1 } |
    Select-Object @{N='DatasetName';E={$_.Name}}, Count |
    Sort-Object Count -Descending

# 10. OWNERSHIP
$ownerBreakdown = $datasets | 
    Group-Object ConfiguredBy | 
    Select-Object @{N='Owner';E={$_.Name}}, Count | 
    Sort-Object Count -Descending

$singlePointOfFailure = $ownerBreakdown | Where-Object { $_.Count -gt 10 }

# 11. CALCULATE GOVERNANCE SCORE
$score = 0
$maxScore = 10

if (($unendorsed.Count / $datasets.Count) -lt 0.5) { $score++ }
if (($unlabeled.Count / $datasets.Count) -lt 0.3) { $score++ }
if ($orphanedReports.Count -eq 0) { $score++ }
if ($emptyWorkspaces.Count -lt ($workspaces.Count * 0.1)) { $score++ }
if (($sharedCapacity.Count / $workspaces.Count) -lt 0.5) { $score++ }
if ($inactiveWorkspaces.Count -eq 0) { $score++ }
if (($wellReusedDatasets.Count / $datasets.Count) -gt 0.2) { $score++ }
if (($singlePointOfFailure.Count / $ownerBreakdown.Count) -lt 0.2) { $score++ }
if ($fileBasedDatasets.Count -lt ($datasets.Count * 0.2)) { $score++ }
if (($refreshableDatasets.Count / $datasets.Count) -gt 0.5) { $score++ }

$percentage = ($score / $maxScore) * 100
$gradeInfo = switch ($percentage) {
    {$_ -ge 90} { @{Grade="A"; Description="Excellent"; Color="#28a745"} }
    {$_ -ge 70} { @{Grade="B"; Description="Good"; Color="#17a2b8"} }
    {$_ -ge 50} { @{Grade="C"; Description="Fair"; Color="#ffc107"} }
    default { @{Grade="D"; Description="Needs Improvement"; Color="#dc3545"} }
}

# ============================================================================
# BUILD HTML REPORT USING STRING BUILDER
# ============================================================================

$sb = New-Object System.Text.StringBuilder

# Add CSS and header
[void]$sb.AppendLine(@"
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Power BI Governance Assessment - $reportDate</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; background: #f5f5f5; color: #333; line-height: 1.6; }
        .container { max-width: 1400px; margin: 0 auto; padding: 20px; }
        header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 40px; border-radius: 10px; margin-bottom: 30px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
        header h1 { font-size: 2.5em; margin-bottom: 10px; }
        header p { font-size: 1.1em; opacity: 0.9; }
        .scorecard { background: white; padding: 30px; border-radius: 10px; margin-bottom: 30px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); text-align: center; }
        .score-display { font-size: 4em; font-weight: bold; color: $($gradeInfo.Color); margin: 20px 0; }
        .grade-label { font-size: 1.5em; color: #666; margin-bottom: 10px; }
        .section { background: white; padding: 30px; border-radius: 10px; margin-bottom: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .section h2 { color: #667eea; border-bottom: 3px solid #667eea; padding-bottom: 10px; margin-bottom: 20px; font-size: 1.8em; }
        .section h3 { color: #764ba2; margin-top: 25px; margin-bottom: 15px; font-size: 1.3em; }
        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
        th { background: #667eea; color: white; padding: 12px; text-align: left; font-weight: 600; }
        td { padding: 10px 12px; border-bottom: 1px solid #e0e0e0; }
        tr:hover { background: #f8f9fa; }
        .metric-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 20px; margin: 20px 0; }
        .metric-card { background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%); padding: 20px; border-radius: 8px; border-left: 4px solid #667eea; }
        .metric-card h4 { color: #555; font-size: 0.9em; margin-bottom: 10px; text-transform: uppercase; letter-spacing: 0.5px; }
        .metric-card .value { font-size: 2em; font-weight: bold; color: #333; }
        .metric-card .subvalue { color: #666; font-size: 0.9em; margin-top: 5px; }
        .alert { padding: 15px 20px; border-radius: 8px; margin: 15px 0; border-left: 4px solid; }
        .alert-danger { background: #f8d7da; border-color: #dc3545; color: #721c24; }
        .alert-warning { background: #fff3cd; border-color: #ffc107; color: #856404; }
        .alert-success { background: #d4edda; border-color: #28a745; color: #155724; }
        .alert-info { background: #d1ecf1; border-color: #17a2b8; color: #0c5460; }
        .priority-list { list-style: none; counter-reset: priority-counter; }
        .priority-list li { counter-increment: priority-counter; padding: 15px; margin: 10px 0; background: #f8f9fa; border-radius: 5px; border-left: 4px solid #667eea; }
        .priority-list li::before { content: counter(priority-counter); background: #667eea; color: white; font-weight: bold; padding: 5px 12px; border-radius: 50%; margin-right: 15px; }
        .status-badge { display: inline-block; padding: 4px 12px; border-radius: 12px; font-size: 0.85em; font-weight: 600; }
        .badge-success { background: #d4edda; color: #155724; }
        .badge-warning { background: #fff3cd; color: #856404; }
        .badge-danger { background: #f8d7da; color: #721c24; }
        .badge-info { background: #d1ecf1; color: #0c5460; }
        .progress-bar { width: 100%; height: 30px; background: #e0e0e0; border-radius: 15px; overflow: hidden; margin: 10px 0; }
        .progress-fill { height: 100%; background: linear-gradient(90deg, #667eea 0%, #764ba2 100%); display: flex; align-items: center; justify-content: center; color: white; font-weight: bold; transition: width 0.3s ease; }
        footer { text-align: center; padding: 30px; color: #666; font-size: 0.9em; }
    </style>
</head>
<body>
    <div class="container">
        <header>
            <h1>🔍 Power BI Governance Assessment</h1>
            <p>Generated on: $reportDate</p>
            <p>Analysis of $($workspaces.Count) workspaces, $($datasets.Count) datasets, $($reports.Count) reports</p>
        </header>
        
        <div class="scorecard">
            <div class="grade-label">Overall Governance Score</div>
            <div class="score-display">$($gradeInfo.Grade)</div>
            <div class="progress-bar">
                <div class="progress-fill" style="width: $percentage%">$percentage%</div>
            </div>
            <p style="margin-top: 20px; font-size: 1.2em; color: #666;">
                $score out of $maxScore criteria met - <strong style="color: $($gradeInfo.Color)">$($gradeInfo.Description)</strong>
            </p>
        </div>
        
        <div class="section">
            <h2>📊 Executive Summary</h2>
            
            <div class="metric-grid">
                <div class="metric-card">
                    <h4>Total Workspaces</h4>
                    <div class="value">$($workspaces.Count)</div>
                    <div class="subvalue">$($premiumCapacity.Count) on Premium/Fabric</div>
                </div>
                <div class="metric-card">
                    <h4>Total Datasets</h4>
                    <div class="value">$($datasets.Count)</div>
                    <div class="subvalue">$($certified.Count) certified</div>
                </div>
                <div class="metric-card">
                    <h4>Total Reports</h4>
                    <div class="value">$($reports.Count)</div>
                    <div class="subvalue">$($orphanedReports.Count) orphaned</div>
                </div>
                <div class="metric-card">
                    <h4>Data Sources</h4>
                    <div class="value">$($datasources.Count)</div>
                    <div class="subvalue">$($sourceTypeBreakdown.Count) unique types</div>
                </div>
            </div>
            
            <h3>Key Findings</h3>
"@)

# Add key findings
if ($unendorsed.Count -gt ($datasets.Count * 0.7)) {
    [void]$sb.AppendLine("            <div class='alert alert-danger'><strong>⚠️ Critical:</strong> $($unendorsed.Count) datasets ($([Math]::Round(($unendorsed.Count / $datasets.Count) * 100, 1))%) lack endorsement.</div>")
}

if ($unlabeled.Count -gt ($datasets.Count * 0.5)) {
    [void]$sb.AppendLine("            <div class='alert alert-warning'><strong>⚠️ Warning:</strong> $($unlabeled.Count) datasets ($([Math]::Round(($unlabeled.Count / $datasets.Count) * 100, 1))%) have no sensitivity labels.</div>")
}

if ($orphanedReports.Count -gt 0) {
    [void]$sb.AppendLine("            <div class='alert alert-warning'><strong>⚠️ Warning:</strong> $($orphanedReports.Count) orphaned reports detected.</div>")
}

if ($excelDatasets.Count -gt 0) {
    [void]$sb.AppendLine("            <div class='alert alert-danger'><strong>🚨 Action Required:</strong> $($excelDatasets.Count) Excel-based datasets found.</div>")
}

if ($wellReusedDatasets.Count -gt 0) {
    [void]$sb.AppendLine("            <div class='alert alert-success'><strong>✓ Good Practice:</strong> $($wellReusedDatasets.Count) datasets are well-reused.</div>")
}

[void]$sb.AppendLine("        </div>")

# Section 1: Endorsement
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>🏆 1. Endorsement Status</h2>
            <table>
                <thead><tr><th>Status</th><th>Count</th><th>Percentage</th></tr></thead>
                <tbody>
"@)

foreach ($item in $endorsementBreakdown) {
    [void]$sb.AppendLine("                    <tr><td>$($item.Status)</td><td>$($item.Count)</td><td>$($item.Percentage)%</td></tr>")
}

[void]$sb.AppendLine("                </tbody></table>")

if ($unendorsed.Count -gt 0) {
    [void]$sb.AppendLine("            <h3>Top 10 Unendorsed Datasets</h3>")
    [void]$sb.AppendLine("            <table><thead><tr><th>Workspace</th><th>Dataset</th><th>Owner</th></tr></thead><tbody>")
    
    foreach ($ds in ($unendorsed | Select-Object -First 10)) {
        [void]$sb.AppendLine("                <tr><td>$($ds.WorkspaceName)</td><td>$($ds.DatasetName)</td><td>$($ds.ConfiguredBy)</td></tr>")
    }
    
    [void]$sb.AppendLine("            </tbody></table>")
}

[void]$sb.AppendLine("        </div>")

# Section 2: Sensitivity Labels
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>🔒 2. Sensitivity Labels</h2>
            <table>
                <thead><tr><th>Label</th><th>Count</th><th>Percentage</th></tr></thead>
                <tbody>
"@)

foreach ($item in $labelBreakdown) {
    [void]$sb.AppendLine("                    <tr><td>$($item.Label)</td><td>$($item.Count)</td><td>$($item.Percentage)%</td></tr>")
}

[void]$sb.AppendLine("                </tbody></table></div>")

# Section 3: Data Sources
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>🔌 3. Data Source Configuration</h2>
            <div class="metric-grid">
                <div class="metric-card">
                    <h4>PBIX Import Mode</h4>
                    <div class="value">$($pbixImportDatasets.Count)</div>
                    <div class="subvalue">$($pbixWithSources.Count) with metadata</div>
                </div>
                <div class="metric-card">
                    <h4>Excel Workbooks</h4>
                    <div class="value">$($excelDatasets.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Database-backed</h4>
                    <div class="value">$($databaseDatasetIds.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Dataflows</h4>
                    <div class="value">$($dataflowDatasets.Count)</div>
                </div>
            </div>
"@)

if ($excelDatasets.Count -gt 0) {
    [void]$sb.AppendLine("            <div class='alert alert-danger'><strong>Critical:</strong> $($excelDatasets.Count) Excel datasets require migration.</div>")
    [void]$sb.AppendLine("            <table><thead><tr><th>Workspace</th><th>Dataset</th><th>Owner</th></tr></thead><tbody>")
    
    foreach ($ds in ($excelDatasets | Select-Object -First 10)) {
        [void]$sb.AppendLine("                <tr><td>$($ds.WorkspaceName)</td><td>$($ds.DatasetName)</td><td>$($ds.ConfiguredBy)</td></tr>")
    }
    
    [void]$sb.AppendLine("            </tbody></table>")
}

[void]$sb.AppendLine("        </div>")

# Section 4: Workspace Health
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>🏢 4. Workspace Health</h2>
            <div class="metric-grid">
                <div class="metric-card">
                    <h4>Shared Capacity</h4>
                    <div class="value">$($sharedCapacity.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Premium/Fabric</h4>
                    <div class="value">$($premiumCapacity.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Empty Workspaces</h4>
                    <div class="value">$($emptyWorkspaces.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Inactive</h4>
                    <div class="value">$($inactiveWorkspaces.Count)</div>
                </div>
            </div>
        </div>
"@)

# Section 5: Dataset Reuse
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>♻️ 5. Dataset Reuse & Duplication</h2>
            <div class="metric-grid">
                <div class="metric-card">
                    <h4>Well-Reused (3+ reports)</h4>
                    <div class="value">$($wellReusedDatasets.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Single-Use</h4>
                    <div class="value">$($singleUseDatasets.Count)</div>
                </div>
                <div class="metric-card">
                    <h4>Duplicate Names</h4>
                    <div class="value">$($potentialDuplicates.Count)</div>
                </div>
            </div>
        </div>
"@)

# Section 6: Ownership
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>👤 6. Content Ownership</h2>
            <h3>Top 10 Dataset Owners</h3>
            <table>
                <thead><tr><th>Owner</th><th>Dataset Count</th></tr></thead>
                <tbody>
"@)

foreach ($owner in ($ownerBreakdown | Select-Object -First 10)) {
    [void]$sb.AppendLine("                    <tr><td>$($owner.Owner)</td><td>$($owner.Count)</td></tr>")
}

[void]$sb.AppendLine("                </tbody></table></div>")

# Section 7: Priorities
[void]$sb.AppendLine(@"
        <div class="section">
            <h2>🎯 7. Recommended Actions</h2>
            <ul class="priority-list">
"@)

# Build priorities
$priorities = @()
if ($excelDatasets.Count -gt 0) {
    $priorities += "Migrate $($excelDatasets.Count) Excel-based datasets"
}
if ($orphanedReports.Count -gt 0) {
    $priorities += "Clean up $($orphanedReports.Count) orphaned reports"
}
if ($unendorsed.Count -gt ($datasets.Count * 0.7)) {
    $priorities += "Implement endorsement for $($unendorsed.Count) datasets"
}
if ($emptyWorkspaces.Count -gt 5) {
    $priorities += "Archive $($emptyWorkspaces.Count) empty workspaces"
}

foreach ($priority in $priorities) {
    [void]$sb.AppendLine("                <li>$priority</li>")
}

[void]$sb.AppendLine(@"
            </ul>
        </div>
        
        <footer>
            <p>Power BI Governance Assessment Report</p>
            <p>Generated: $reportDate</p>
        </footer>
    </div>
</body>
</html>
"@)

# Save and open
$reportPath = "PowerBI_Governance_Report_$timestamp.html"
$sb.ToString() | Out-File -FilePath $reportPath -Encoding UTF8

Write-Host "`n✓ HTML Report Generated: $reportPath" -ForegroundColor Green
Start-Process $reportPath