Azure DevOps部署SSAS Tabular模型时自动隐藏键列的方案咨询

Absolutely! You can automate hiding key columns when deploying your SSAS Tabular model to TST while keeping all columns visible in DEV by modifying the .bim file during your Azure DevOps release pipeline. Since your resources are on-premises, this approach works perfectly with local deployment tools too. Here's a step-by-step breakdown:

Core Idea #

The SSAS Tabular .bim file is just a structured JSON file. We can add a script step in your release pipeline to tweak the IsHidden property of key columns only when deploying to TST, then deploy the modified model.

Step 1: Identify Your Key Columns #

First, define how to recognize key columns in your model. You have two common options:

• Use the IsKey property: If you've marked key columns in your model (via SSDT), the .bim file will have IsKey: true for these columns.
• Use naming conventions: If your key columns follow a pattern (e.g., end with ID or Key), you can match on column names instead.

Step 2: Add a PowerShell Script Task to Your Release Pipeline #

Insert a PowerShell task before your SSAS deployment task in the TST stage of your pipeline. This script will modify the .bim file to hide key columns.

Example Script (Using IsKey Property)

# Path to your .bim file (adjust this to match your pipeline's file structure)
$bimFile = "$(System.DefaultWorkingDirectory)/YourModelProject/Model.bim"

# Read and parse the .bim JSON
$modelContent = Get-Content $bimFile -Raw | ConvertFrom-Json

# Loop through each table to find and hide key columns
foreach ($table in $modelContent.model.tables) {
    $keyColumns = $table.columns | Where-Object { $_.IsKey -eq $true }
    foreach ($column in $keyColumns) {
        $column.IsHidden = $true
        Write-Host "Marked column '$($column.name)' in table '$($table.name)' as hidden"
    }
}

# Write the modified JSON back to the .bim file (use -Depth to avoid truncating nested structures)
$modelContent | ConvertTo-Json -Depth 15 | Set-Content $bimFile

Example Script (Using Naming Convention)

If you rely on column names instead, replace the $keyColumns line with:

$keyColumns = $table.columns | Where-Object { $_.name -match 'ID$|Key$' }

Step 3: Configure Pipeline Conditions #

Make sure this script only runs when deploying to TST:

1. In the PowerShell task's settings, go to Conditions.
2. Set the condition to something like eq(variables['Release.EnvironmentName'], 'TST') (adjust the environment name to match yours).

Step 4: Deploy the Modified Model #

Use your existing SSAS deployment task (e.g., Azure Analysis Services Deployment task, or the Tabular Model Deployment Wizard command-line tool for on-premises) to deploy the updated .bim file to your TST server.

Key Notes #

• JSON Depth: When converting back to JSON, use a sufficient -Depth value (like 15) to avoid truncating nested parts of your model.
• On-Premises Access: Ensure your Azure DevOps self-hosted agent has network access to your local SSAS server and permissions to deploy models.
• Testing: Test the script in a non-production environment first to verify it only modifies the intended columns.

Alternative: Use BISM Normalizer #

If you need more advanced model comparison/modification capabilities, consider using the BISM Normalizer tool. It's designed to handle SSAS Tabular model differences in CI/CD pipelines and can automate hiding columns based on environment rules.

内容的提问来源于stack exchange，提问作者analyzethat