Home > Tutorials > On-Prem SSAS / SSIS / SSRS to Fabric Migration

📚 Tutorial 45: On-Prem SSAS / SSIS / SSRS → Microsoft Fabric Migration¶

Last Updated: 2026-04-27 | Phase: 14 (Wave 4) | Feature: 4.10 — Legacy MSBI Stack Migration Status: ✅ Final | Maintainer: Platform Team

📊 Tutorial 45: On-Prem SSAS / SSIS / SSRS → Microsoft Fabric Migration¶

📊 Progress Tracker¶

📖 Overview¶

The legacy Microsoft BI stack — SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) — has powered enterprise reporting since the early 2000s. Many of these deployments are still running on Windows Server 2012/2016 hosts, against SQL Server 2014/2016/2019 source databases, with cubes refreshed nightly and RDL reports distributed via email subscription.

This tutorial walks through migrating each MSBI component into its Fabric equivalent, with conversion patterns for cubes, packages, and reports — and the hard-won guidance about MDX, custom SSIS components, and pixel-perfect RDL fidelity.

Why Migrate the Legacy MSBI Stack?¶

What This Tutorial Covers¶

1. Inventory the MSBI estate (SSAS DBs, SSIS catalog, SSRS reports, SQL Server sources)
2. Plan migration waves (sources → ETL → semantic → reports)
3. Set up SHIR or VNet Data Gateway for hybrid data movement
4. Migrate SQL Server source data via Mirroring, Copy Job CDC, or pipelines
5. Convert SSAS Tabular databases to Power BI semantic models (TMDL)
6. Convert SSAS Multidimensional cubes (the hard one — Tabular pivot + MDX→DAX)
7. Convert SSIS packages to Fabric Pipelines (or Azure-SSIS interim bridge)
8. Convert SSRS RDL reports to Fabric Paginated Reports
9. Migrate row-level security and column-level encryption
10. Run BI consumers on both stacks during coexistence
11. Decommission MSBI servers safely

🎯 Learning Objectives¶

By the end of this tutorial, you will be able to:

• Inventory an MSBI estate (SSAS Tabular & Multidimensional, SSIS catalog, SSRS server)
• Plan a wave-ordered migration that respects dependencies between source DBs, packages, cubes, and reports
• Stand up SHIR or VNet Data Gateway for on-prem to Fabric hybrid connectivity
• Choose between Mirroring, Copy Job CDC, and SHIR pipeline patterns for source data
• Convert SSAS Tabular models to Power BI semantic models using TMDL
• Convert SSAS Multidimensional cubes (in-place to Tabular, then to Power BI) and translate MDX to DAX
• Convert SSIS packages to Fabric Pipelines using Microsoft's SSIS Migration Assessment tool
• Convert SSRS RDL files to Fabric Paginated Reports with full subscription parity
• Migrate row-level security (SSAS roles → Power BI RLS) and column-level encryption (→ sensitivity labels + OneLake Security)
• Establish a coexistence period with parallel SSRS and Power BI delivery for user validation
• Decommission the MSBI servers safely

🏗️ Reference Architecture¶

flowchart LR
    subgraph OnPrem["🏢 Source: On-Prem MSBI Stack"]
        SQL[(SQL Server<br/>Source DBs)]
        SSAST[(SSAS Tabular)]
        SSASM[(SSAS Multidimensional)]
        SSIS[SSIS Catalog<br/>SSISDB]
        SSRS[SSRS Server<br/>RDL + Subscriptions]
        AD[Active Directory]
    end

    subgraph Bridge["🔗 Hybrid Bridge"]
        SHIR[Self-Hosted IR]
        VNG[VNet Data Gateway]
    end

    subgraph Fabric["⚡ Target: Microsoft Fabric"]
        WH[(Fabric Warehouse<br/>+ Mirroring)]
        OL[(OneLake)]
        SM[Power BI<br/>Semantic Model<br/>Direct Lake]
        FPIPE[Fabric Pipelines]
        PR[Paginated Reports]
        ENTRA[Entra ID]
    end

    subgraph Migration["🔄 Migration Layer"]
        Assess[MSBI Inventory]
        TMDL[TMDL Converter<br/>SSAS → PBI Model]
        MDX[MDX → DAX<br/>Translation]
        SSISA[SSIS Migration<br/>Assessment Tool]
        RDLConv[RDL → PBI<br/>Report Builder]
    end

    SQL --> SHIR
    SHIR --> WH
    SQL -->|Mirroring| WH
    SSAST --> Assess
    SSASM --> Assess
    SSIS --> Assess
    SSRS --> Assess
    Assess --> TMDL
    Assess --> MDX
    Assess --> SSISA
    Assess --> RDLConv
    TMDL --> SM
    MDX --> SM
    SSISA --> FPIPE
    RDLConv --> PR
    WH --> SM
    OL --> SM
    AD --> ENTRA
    SM --> PR

🧭 Component Mapping (the canonical reference)¶

This is the most important table in the tutorial. Every component on the left maps to a Fabric equivalent on the right.

Source Data Layer¶

Semantic Layer¶

ETL Layer¶

Reporting Layer¶

Security and Identity¶

📋 Prerequisites¶

• Tutorial 00 (Environment Setup) complete
• Tutorials 01-03 (medallion) understood
• Tutorial 22 — Networking and/or Tutorial 23 — SHIR & Data Gateways for hybrid connectivity
• Source: Read access to on-prem SSAS instances, SSIS catalog (SSISDB), SSRS server, and SQL Server source DBs
• Target: Fabric F64+ capacity provisioned
• Bridge: SHIR host VM (Windows Server 2019+, 4 vCPU / 16 GB RAM minimum) OR VNet Data Gateway for VNet-injected sources
• Tooling:
• SQL Server Management Studio (SSMS) 19+ for SSAS introspection
• SQL Server Data Tools (SSDT) 17+ for Multidim → Tabular conversion
• Power BI Desktop (latest)
• Power BI Report Builder (latest)
• Microsoft SSIS Migration Assessment tool (download)
• PowerShell 7.x with SqlServer and Az.Fabric modules
• Service Principal with Workspace Admin on target Fabric workspace
• Entra ID tenant with AD groups synced (Entra Connect)
• Estimated 4-6 hours active time per workload + multi-week coexistence period
• Hash salt env var set: export FABRIC_POC_HASH_SALT=$(openssl rand -hex 32)

🚀 Step-by-Step¶

Step 1 — Assess the MSBI Estate¶

Run the MSBI inventory script to catalog every cube, package, and report.

cd tutorials/45-onprem-ssas-ssis-ssrs/

python 01_msbi_inventory.py \
    --ssas-server "tcp://onprem-ssas01" \
    --ssis-server "onprem-sql01" \
    --ssis-catalog "SSISDB" \
    --ssrs-url "http://onprem-ssrs01/reportserver" \
    --output-dir "./assessment-output"

✅ Verification: Output directory contains: - ssas_inventory.csv — every database, cube/model, measure group, partition, role, last-process-time - ssis_inventory.csv — every package, project, parameter, environment reference, last-execution-time - ssrs_inventory.csv — every report, shared dataset, subscription, last-render-time - mdx_dax_translation_report.md — auto-flagged MDX expressions that need DAX rewrite - complexity_scores.csv — per-artifact "Easy / Medium / Hard / Very Hard" classification - dependency_graph.json — what depends on what (used for migration ordering) - unsupported_features.md — MSBI features without a clean Fabric equivalent

💡 Tip: Run inventory monthly during the migration project. New reports show up here, and stale reports flagged for last-render-time > 365d are decommissioning candidates instead of migration candidates.

⚠️ Gotcha: The script connects to SSAS via XMLA, SSIS via T-SQL against SSISDB.catalog.*, and SSRS via the SOAP ReportService2010 endpoint. Each may require a separate service account with appropriate read permissions.

Step 2 — Plan Migration Waves¶

Use the dependency graph to plan migration waves. The order is non-negotiable: data sources first, then ETL, then semantic, then reports.

python 01_msbi_inventory.py --command wave-plan \
    --inventory ./assessment-output/ \
    --max-wave-effort-days 15

The tool produces migration-waves.md with this canonical order:

• Wave 1 — Source Data: SQL Server DBs migrated to Fabric (Mirroring or Copy Job CDC)
• Wave 2 — ETL: SSIS packages converted to Fabric Pipelines (or Azure-SSIS bridge for hard ones)
• Wave 3 — Semantic (Tabular): SSAS Tabular models converted to Power BI semantic models
• Wave 4 — Semantic (Multidimensional): SSAS Multidim cubes converted (Tabular pivot first)
• Wave 5 — Reports: SSRS reports converted to Paginated Reports
• Wave 6 — Coexistence: Run both stacks side by side for 4-8 weeks
• Wave 7 — Cutover: Decommission MSBI servers

⚠️ Gotcha: If any SSRS report queries an SSAS cube directly, that cube must finish Wave ¾ before the report is migrated. The wave-plan tool flags these tight couplings.

Step 3 — Set Up SHIR or VNet Data Gateway¶

For hybrid connectivity, follow Tutorial 23 — SHIR & Data Gateways.

# Quick reference — install and register SHIR on a Windows Server VM
$AuthKey = "YOUR-AUTH-KEY-FROM-FABRIC"

# 1. Download SHIR installer
Invoke-WebRequest -Uri "https://download.microsoft.com/download/E/4/7/E4771905-1079-445B-8BF9-8A1A075D8A10/IntegrationRuntime_5.39.8836.1.msi" -OutFile "shir.msi"

# 2. Silent install
msiexec /i shir.msi /quiet /qn /norestart

# 3. Register with Fabric
& "C:\Program Files\Microsoft Integration Runtime\5.0\Shared\dmgcmd.exe" -RegisterNewNode $AuthKey

✅ Verification: In Fabric portal → Workspace settings → Connections → On-premises data gateway, the SHIR shows as "Online".

💡 Tip: For high-throughput workloads, run two SHIR nodes in HA mode so cube refreshes survive a node reboot.

Step 4 — Migrate Source Data¶

Choose the right pattern per source database. Don't use one pattern for everything.

Option A — Mirroring (preferred for OLTP / low-latency BI)¶

Mirroring delivers near-real-time replication from SQL Server to Fabric Warehouse with no ETL code.

# Create a Mirrored database via Fabric REST API
curl -X POST \
    "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/mirroredDatabases" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
      "displayName": "mirrored_sales_db",
      "definition": {
        "source": {
          "type": "SqlServer",
          "connection": {
            "server": "onprem-sql01.contoso.com",
            "database": "Sales",
            "gatewayId": "'$GATEWAY_ID'"
          }
        }
      }
    }'

✅ Verification: After 5-10 minutes, query the mirrored DB SQL endpoint and compare a row count to the source.

-- Run in Fabric SQL endpoint
SELECT COUNT(*) FROM dbo.SalesOrderHeader;
-- Should match SELECT COUNT(*) FROM Sales.dbo.SalesOrderHeader on the source

💡 Tip: Mirroring is free under your F-SKU CU pool. It's almost always the right choice for SQL Server sources — see Mirroring.

Option B — Copy Job CDC (batch incremental)¶

For sources where Mirroring isn't feasible (e.g., SQL Server 2012 — too old for change tracking), use Copy Job CDC.

# Create a Copy Job that reads CDC tables hourly
python 02_create_copy_job.py \
    --source-server "onprem-sql01" \
    --source-database "Sales" \
    --tables "SalesOrderHeader,SalesOrderDetail,Customer" \
    --target-warehouse "$FAB_WH_ID" \
    --schedule "hourly"

See Copy Job CDC for the full pattern.

Option C — SHIR + Pipeline (lift-and-shift the nightly batch)¶

For legacy SSIS ETL that you want to temporarily preserve before rewriting, run a Fabric Pipeline that mirrors the existing nightly job.

{
  "name": "nightly_sales_load",
  "activities": [
    {
      "type": "Copy",
      "linkedService": { "referenceName": "shir_onprem_sql01" },
      "source": { "type": "SqlServerSource", "sqlReaderQuery": "SELECT * FROM Sales.SalesOrderHeader" },
      "sink": { "type": "LakehouseTableSink", "tableName": "bronze_sales_order_header" }
    }
  ]
}

⚠️ Gotcha: Choose per source database, not globally. A single estate often has all three patterns running simultaneously during migration.

Step 5 — Convert SSAS Tabular → Power BI Semantic Model¶

SSAS Tabular and Power BI semantic models are the same engine under the hood (Vertipaq + Tabular Object Model). Migration is mostly a re-target exercise.

5.1 Export TMDL from SSAS Tabular¶

TMDL (Tabular Model Definition Language) is the modern source-control-friendly authoring format for semantic models. SSAS Tabular models export cleanly to TMDL.

# Use Microsoft.AnalysisServices.Tabular .NET SDK
Install-Module -Name SqlServer
Import-Module SqlServer

$Server = "tcp://onprem-ssas01"
$Database = "SalesTabularModel"
$OutputDir = ".\tmdl-export\$Database"

Invoke-ASCmd -Server $Server -Query @"
<Backup xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'>
  <Object><DatabaseID>$Database</DatabaseID></Object>
  <File>$OutputDir\$Database.abf</File>
</Backup>
"@

# Convert ABF to TMDL using Tabular Editor 3 (or open in PBI Desktop)
& "C:\Program Files\Tabular Editor 3\TabularEditor3.exe" `
    "$OutputDir\$Database.abf" `
    -SAVE-TO-FOLDER "$OutputDir\tmdl"

5.2 Re-target the data source¶

In the exported TMDL, change every dataSource block from on-prem SSAS to your Fabric Warehouse / Lakehouse SQL endpoint.

 dataSource salesDB
-    type: sqlServer
-    connectionString: "Server=onprem-sql01;Database=Sales;Integrated Security=SSPI;"
+    type: sqlServer
+    connectionString: "Server=<workspace-id>.datawarehouse.fabric.microsoft.com;Database=wh_sales;"

5.3 Import TMDL into Power BI¶

# Open the TMDL folder in Power BI Desktop
& "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe" `
    -Folder ".\tmdl-export\$Database\tmdl"

# Or use Tabular Editor 3 to publish directly
& "C:\Program Files\Tabular Editor 3\TabularEditor3.exe" `
    ".\tmdl-export\$Database\tmdl" `
    -DEPLOY -SERVER "powerbi://api.powerbi.com/v1.0/myorg/$WORKSPACE_NAME" `
    -DATABASE "$Database"

5.4 Recommend Direct Lake (when model fits)¶

If the migrated model has < 1 billion rows per fact table and source data is in OneLake (after Step 4), switch the connection mode to Direct Lake:

• No import step
• No refresh schedule
• Real-time visibility into the warehouse
• Massive CU savings vs Import mode

# In Power BI Desktop → Transform data → Source → switch from Import to Direct Lake
# Or programmatically via TMDL:

 partition Sales-Partition
-    mode: import
-    source = let Source = Sql.Database(...) in Source
+    mode: directLake
+    source = entity { schemaName: "dbo", entityName: "FactSales" }

✅ Verification: Open the published semantic model in Power BI Service. DAX measures should compute identical results to the source SSAS Tabular model. Use DAX Studio to compare measure outputs side by side.

💡 Tip: DAX measures and roles port directly. Calculated columns are the gotcha — Direct Lake does not support traditional calculated columns. Convert to DAX measures or push computation into the Warehouse layer (e.g., a Gold-layer view).

⚠️ Gotcha: SSAS Tabular running in compatibility level 1200 uses TOM but not TMDL-natively. Upgrade to compatibility level 1500+ before exporting (right-click DB → Properties → Compatibility Level in SSMS).

Step 6 — Convert SSAS Multidimensional (the hard one)¶

Multidimensional cubes do not convert directly to Power BI. The path is two hops: Multidim → Tabular → Power BI.

6.1 Convert Multidim to Tabular (in-place via SSDT)¶

# 1. Open the existing Multidim project in SQL Server Data Tools (SSDT)
# 2. Use the "Migrate to Tabular" wizard:
#    File → New → Project → Analysis Services Tabular Project
#    Choose "From existing model" → Select Multidim cube
#    SSDT generates a Tabular shell — measure groups become tables

⚠️ Gotcha: The "Migrate to Tabular" wizard handles structure only. Every MDX expression (calculated members, named sets, scope assignments) must be rewritten as DAX. Budget 2-8 hours per cube for non-trivial calculations.

6.2 MDX → DAX Translation Patterns¶

This is the hardest part of MSBI migration. Common patterns:

6.3 Use the auto-translation report¶

The 01_msbi_inventory.py script produces mdx_dax_translation_report.md with: - Every MDX expression in the cube - Auto-translated DAX where the pattern is unambiguous (about 60% of cases) - Flagged for manual review where it isn't (the remaining 40%)

cat ./assessment-output/mdx_dax_translation_report.md | head -50

6.4 Then follow Step 5 (Tabular → Power BI)¶

Once the Multidim cube is a working Tabular model, the rest of the migration is identical to Step 5.

✅ Verification: Pick 10 representative MDX queries from your historical query log. Run each MDX query against the source cube and the equivalent DAX query against the migrated semantic model. Compare results within 0.001% tolerance.

💡 Tip: Some Multidim features (writeback, custom rollup operators, action calculations) have no Tabular or Power BI equivalent. Inventory these early and decide: re-design, drop, or postpone migration of that cube.

Step 7 — Convert SSIS Packages → Fabric Pipelines¶

Microsoft publishes the SSIS Migration Assessment tool that scans your SSISDB and reports per-package migration feasibility.

7.1 Run the SSIS Migration Assessment Tool¶

# Download and run from:
# https://learn.microsoft.com/sql/ssis/lift-shift/ssis-azure-lift-shift

.\SsisMigrationAssessment.exe `
    -SourceServer "onprem-sql01" `
    -SourceCatalog "SSISDB" `
    -OutputPath ".\ssis-assessment-report.html"

The report classifies each package: - Green — Direct migration to Fabric Pipelines - Yellow — Migration possible with modifications (custom Script tasks, deprecated components) - Red — Lift-and-shift to Azure-SSIS recommended (complex custom components, third-party tasks)

7.2 Map activities one-by-one¶

For Green packages, run the conversion:

python 03_ssis_to_pipeline.py \
    --source-folder "./ssis-packages-export/" \
    --target-workspace "$FABRIC_WS_ID" \
    --output-dir "./fabric-pipelines/"

Refer to the Component Mapping table above for the full activity-by-activity translation.

7.3 Special: Lift-and-shift to Azure-SSIS (interim bridge)¶

For Red packages (or any where the rewrite cost exceeds 40 hours), use Azure-SSIS Integration Runtime as an interim bridge.

# Provision Azure-SSIS IR in Azure Data Factory
az datafactory integration-runtime managed create \
    --factory-name "adf-msbi-bridge" \
    --resource-group "rg-fabric-migration" \
    --name "azure-ssis-ir" \
    --node-size "Standard_D4_v3" \
    --number-of-nodes 2 \
    --catalog-server-endpoint "fabric-sql.database.windows.net" \
    --catalog-pricing-tier "S1"

Then deploy your existing SSIS packages unchanged to Azure-SSIS, and trigger them from Fabric Pipelines via the Execute SSIS Package activity.

💡 Tip: Azure-SSIS is a bridge, not a destination. Plan to retire each lifted-and-shifted package within 12-18 months as Fabric Pipeline / Notebook rewrites complete. Otherwise you've just moved the on-prem MSBI bill to Azure.

⚠️ Gotcha: Custom SSIS Components written in C# do not run in Fabric Pipelines or Dataflow Gen2. They must be either: 1. Rewritten as a Notebook (PySpark or Python) — preferred long-term 2. Lifted-and-shifted to Azure-SSIS — preferred interim 3. Replaced with an off-the-shelf Fabric activity if the custom logic was minor

See the Special Section on Custom SSIS Components below.

Step 8 — Convert SSRS RDL → Fabric Paginated Reports¶

Paginated Reports in Fabric use the same RDL standard as SSRS. Migration is mostly a re-target operation.

8.1 Open RDL in Power BI Report Builder¶

# 1. Download the RDL files from SSRS
# 2. Open each RDL in Power BI Report Builder (free download)
& "C:\Program Files (x86)\Microsoft Power BI Report Builder\PBIReportBuilder.exe" `
    "C:\reports\sales_summary.rdl"

8.2 Re-target the data source¶

In Report Builder → Report Data → Data Sources → right-click the data source → Properties:

• Change Connection Type from Microsoft SQL Server (or Microsoft SQL Server Analysis Services) to:
• Power BI semantic model — for reports that previously hit an SSAS cube (now migrated to PBI)
• Microsoft Fabric SQL Database — for reports that previously hit a SQL Server source DB (now mirrored)
• Microsoft Fabric Lakehouse — for reports against Lakehouse data
• Update the connection string to your Fabric workspace endpoint

8.3 Test pixel-perfect output¶

Critical for any report subject to regulatory or legal review. Render the RDL in both source and target, then compare PDFs visually and via pdf-diff.

# Bulk-render and compare 100 representative reports
python 04_rdl_pixel_compare.py \
    --source-ssrs "http://onprem-ssrs01/reportserver" \
    --target-fabric-workspace "$FABRIC_WS_ID" \
    --report-list "./reports-to-compare.csv" \
    --output ./pixel-diff-report.html

8.4 Migrate subscriptions¶

# Export SSRS subscriptions to a portable manifest
python 04_export_ssrs_subscriptions.py \
    --ssrs-url "http://onprem-ssrs01/reportserver" \
    --output ./ssrs-subscriptions.json

# Apply to Power BI Service
python 04_create_pbi_subscriptions.py \
    --workspace "$FABRIC_WS_ID" \
    --manifest ./ssrs-subscriptions.json

✅ Verification: All migrated reports render in Fabric within ±2% of SSRS render time. All subscriptions deliver to recipients within their existing schedule.

⚠️ Gotcha: Data-driven subscriptions in SSRS (one subscription that fans out to a list of users from a SQL query) do not have a 1:1 equivalent in Power BI. Two options: (1) use Power Automate flow with For each over the recipient list, or (2) use Power BI bursting (Premium only).

💡 Tip: PBIRS-converted RDLs are already most of the way there. If you'd already migrated SSRS to Power BI Report Server (PBIRS) on-prem, the jump to Fabric Paginated Reports is just an endpoint change.

Step 9 — Migrate Row-Level Security¶

9.1 SSAS Roles → Power BI RLS¶

SSAS roles map directly to Power BI RLS roles. The DAX filter expression is the same.

// SSAS Tabular role: SalesRegionRLS
[Region] = USERNAME()

// Power BI RLS role: SalesRegionRLS (no change)
[Region] = USERPRINCIPALNAME()

⚠️ Gotcha: USERNAME() returns DOMAIN\user in SSAS but the UPN (user@contoso.com) in Power BI. Use USERPRINCIPALNAME() for Power BI; the value comes from Entra ID.

9.2 Multidim Cell Security → Power BI OLS + RLS¶

Multidim cell security (which restricted specific cells of the cube based on dimension membership) has no direct Power BI equivalent. Re-design:

• Most use cases collapse into RLS (filter rows based on user identity)
• Sensitive column cases collapse into OLS (Object-Level Security, hides specific columns or measures)
• True cell-level cases (e.g., "this user can see only YTD totals, not transaction detail") become two semantic models with different audiences

Step 10 — Migrate Column-Level Encryption¶

SQL Server Always Encrypted (column-level encryption) doesn't directly translate to Fabric. Instead, layer protection:

• OneLake Security restricts who can read the underlying data files — see OneLake Security
• Sensitivity labels (Microsoft Information Protection / Purview) classify and audit access — see Tutorial 07
• Power BI OLS hides specific columns from users who shouldn't see them
• Dynamic data masking (Fabric SQL Database) masks the value at query time for unauthorized users

Most regulated workloads need a combination of all four.

💡 Tip: Column-level encryption was often used to satisfy a single audit checkbox. Talk to the audit team — sensitivity labels + OneLake Security + DDM together usually pass the same audit at lower complexity.

Step 11 — Cut Over BI Consumers¶

This is change management, not technical work. Plan in waves:

1. Pilot users (5-10 power users) get access to Fabric workspace, run their reports, file feedback
2. Department by department rollout, with formal sign-off before enabling Power BI subscriptions for that department
3. Decommission email lists from SSRS one department at a time (don't flip the whole org at once)

Step 12 — Coexistence (4-8 weeks)¶

Run SSRS, SSAS, and Fabric Power BI side by side:

• Source SQL Server is the truth (all writes still go there)
• Mirroring/Copy Job CDC replicates to Fabric continuously
• Both stacks render the same reports for the same users
• Daily diff report (counts, key aggregations) confirms parity
• Action Group alerts on parity drift (see alerts-and-budgets.bicep)

💡 Tip: This period is expensive (running both stacks at the same time). Budget for it, but don't extend it past 8 weeks. If users haven't validated by week 6, escalate.

Step 13 — Decommission MSBI Servers¶

When parity has been green for 14 consecutive days and all users have signed off:

# 1. Pause SSAS instances (stop service, retain data on disk for 30 days)
Stop-Service "MSSQLServerOLAPService" -Force
Set-Service "MSSQLServerOLAPService" -StartupType Disabled

# 2. Disable SSIS Agent jobs
Invoke-Sqlcmd -ServerInstance "onprem-sql01" -Query @"
EXEC msdb.dbo.sp_update_job @job_name = N'Nightly_Sales_Load', @enabled = 0;
"@

# 3. Disable SSRS subscriptions (stop sending emails)
Invoke-Sqlcmd -ServerInstance "onprem-ssrs01" -Database "ReportServer" -Query @"
UPDATE Subscriptions SET InactiveFlags = 1 WHERE InactiveFlags = 0;
"@

# 4. Wait 30 days. If no one screams, fully decommission:
#    - Backup SSAS .abf files to long-term archive
#    - Backup SSISDB to long-term archive
#    - Backup ReportServer DB to long-term archive
#    - Decommission Windows Server VMs
#    - Reclaim SQL Server core licenses

⚠️ Gotcha: Do not decommission the source SQL Server databases yet. Mirroring still depends on them. Source SQL Server retirement is a separate, later project once the legacy applications writing to those DBs are also retired or repointed to Fabric SQL Database.

🧩 Special Section — TMDL (Tabular Model Definition Language)¶

TMDL is the modern, source-control-friendly authoring format for Power BI / SSAS Tabular semantic models. Instead of one giant model.bim JSON blob, TMDL emits one human-readable text file per object (table, measure, role, perspective).

// Example: measures/SalesYTD.tmdl
measure SalesYTD =
    TOTALYTD(SUM(FactSales[Amount]), DimDate[Date])
    formatString: "\\$#,0;(\\$#,0)"
    description: "Year-to-date sales, fiscal year"
    displayFolder: "Sales \\ YTD"

Why this matters for migration: - Diff-friendly — Git PR reviews on semantic model changes - Tool-agnostic — Tabular Editor, Power BI Desktop, VS Code all read TMDL - The native format of the migrated model after Step 5

Recommendation: Adopt TMDL during the migration, not after. Don't import a .bim file and then "migrate to TMDL" in a separate project.

🧩 Special Section — MDX → DAX Translation Patterns (the hardest part)¶

Multidim → Tabular is the #1 source of migration delays in this tutorial. Two strategies to manage the risk:

Strategy A — Auto-translate the easy 60%¶

The mdx_dax_translation_report.md (from Step 1) auto-translates the patterns in the Component Mapping table. Apply these in bulk via a script:

# Pseudocode of auto-translation
patterns = {
    r"\[Measures\]\.\[(\w+)\]": r"[\1]",
    r"PARALLELPERIOD\(\[Date\]\.\[Calendar\], 1, .*\)": r"DATEADD(DimDate[Date], -1, YEAR)",
    # ... 20+ more patterns
}
for mdx, dax in patterns.items():
    dax_file = re.sub(mdx, dax, mdx_file)

Strategy B — Manual rewrite the hard 40%¶

Some MDX has no direct DAX equivalent: - SCOPE assignments (DAX has no SCOPE — re-think as CALCULATE with filter context override) - Cell calculations (no equivalent — re-design as separate measures or as part of the data model) - Custom rollup operators (e.g., averaging instead of summing for time dimensions — re-design via CALCULATE + AVERAGEX) - LinkMember, Strict cube features

Budget 2-3 person-days per "hard" cube. Test exhaustively against historical query results.

Strategy C — Don't migrate calcs that aren't used¶

The inventory script captures the cube's query log (last 90 days from SSAS DMVs $system.discover_command_objects). Calculations with zero queries in 90 days are decommissioning candidates, not migration candidates. This typically eliminates 30-50% of the calc migration work.

🧩 Special Section — Custom SSIS Components¶

SSIS allows custom .NET components written in C#. These are the single biggest reason to use Azure-SSIS as an interim bridge.

Decision tree¶

Custom SSIS Component
        │
        ├── Used in < 5 packages, < 100 LOC?
        │       └── Rewrite as Notebook (PySpark) — 1-2 days each
        │
        ├── Used in many packages, well-documented?
        │       └── Rewrite as a shared Notebook helper module
        │
        ├── Critical path, complex, third-party?
        │       └── Lift-and-shift to Azure-SSIS, plan rewrite later
        │
        └── Vendor-supplied (e.g., Theobald, Cozyroc)?
                └── Check vendor for Fabric / Spark version
                └── If none: Azure-SSIS bridge only

Common custom-component categories and Fabric replacements¶

🧩 Special Section — Hybrid Pattern (Keep On-Prem SQL Server, Move BI to Fabric)¶

Not every org wants to fully retire on-prem SQL Server. A common pattern:

• Source SQL Server stays on-prem (the operational app writing to it isn't ready to move)
• Mirroring replicates to Fabric continuously
• Direct Lake semantic model in Fabric reads from the mirrored copy
• Power BI Service delivers reports
• SSAS, SSIS, SSRS retire even though the source DB does not

This delivers 80% of the modernization benefit with 20% of the source-system risk.

flowchart LR
    OnPrem["🏢 On-Prem SQL Server<br/>(stays as source)"] -->|Mirroring| Fabric[(Fabric Warehouse)]
    Fabric --> SM["Power BI<br/>Direct Lake Semantic Model"]
    SM --> PBI["Power BI Service<br/>+ Paginated Reports"]
    SSAS["❌ SSAS retired"]
    SSIS["❌ SSIS retired"]
    SSRS["❌ SSRS retired"]

💡 Tip: This is often the right first step even when the eventual goal is full SQL Server retirement. Migrate the BI tier first (low operational risk), then plan source-DB migration as a separate project.

✅ Verification (Final Checklist)¶

• All in-scope SSAS Tabular models converted to Power BI semantic models (TMDL committed to git)
• All in-scope SSAS Multidimensional cubes converted (Tabular pivot + DAX)
• All MDX-to-DAX translations validated against historical query results (within 0.001%)
• All in-scope SSIS packages converted to Fabric Pipelines (Green packages) or running on Azure-SSIS bridge (Yellow/Red packages)
• All in-scope SSRS RDL files converted to Paginated Reports
• Pixel-diff < 1% on top 100 reports (subset to 100% on regulatory reports)
• All subscriptions migrated (or replaced with Power Automate where data-driven)
• Row-Level Security ported (SSAS roles → Power BI RLS)
• Column-level encryption replaced with OneLake Security + sensitivity labels + OLS
• Hybrid bridge stable (SHIR / VNet Data Gateway HA)
• Mirroring stable for source DBs (lag < 60 seconds)
• BI consumers cut over (departments signed off)
• Coexistence period green for 14+ consecutive days
• MSBI servers paused (or fully decommissioned post-grace-period)
• SQL Server core licenses reclaimed (cost reduction documented)
• Cutover postmortem published

🧹 Cleanup¶

This tutorial creates assessment artifacts in ./assessment-output/, TMDL exports in ./tmdl-export/, and Pipeline conversion artifacts in ./fabric-pipelines/. Retain for audit; archive to long-term storage.

If your assessment ran against a real MSBI estate, the read-only queries leave no trace. No rollback needed.

🚦 Next Steps¶

• Tutorial 41 — Synapse → Fabric — for shops with both legacy MSBI and Synapse Analytics
• Tutorial 08 — Database Mirroring — deep dive on the recommended source data pattern
• Tutorial 23 — SHIR & Data Gateways — the hybrid bridge that powers Steps 3-4
• Migration Patterns — cross-source migration framework
• Paginated Reports — feature deep-dive for the SSRS replacement
• Composite Models — for hybrid Direct Lake + DirectQuery scenarios
• Data Modeling Star Schema — how to refactor cube schemas during migration
• fabric-cicd Deployment — automate the deployment of migrated TMDL, Pipelines, and Paginated Reports

🛠️ Troubleshooting¶

📁 Key Files Referenced¶

📚 References¶

Microsoft Documentation¶

• SSIS to Fabric / ADF migration guide
• SSAS Tabular to Power BI migration
• SSAS Multidimensional → Tabular conversion
• TMDL — Tabular Model Definition Language
• Paginated Reports in Fabric
• Migrate SSRS reports to Power BI
• Power BI RLS
• Mirroring for Azure SQL / SQL Server
• Power BI Report Builder download

Related Tutorials & Docs¶

• Tutorial 08 — Database Mirroring
• Tutorial 23 — SHIR & Data Gateways
• Tutorial 41 — Synapse → Fabric
• Tutorial 42 — Databricks → Fabric
• Tutorial 43 — Redshift → Fabric
• Tutorial 44 — BigQuery → Fabric
• Migration Patterns
• Paginated Reports
• Composite Models
• Data Modeling Star Schema
• Mirroring
• Copy Job CDC
• OneLake Security
• Direct Lake
• fabric-cicd Deployment
• Capacity Planning

⬆️ Back to Top | 📚 Tutorial Index | 🏠 Home