SSAS Migration Proposal — Multidimensional to Fabric

Section 01 · Background

Context & Why This Migration Is Necessary

Understanding the technical and business drivers before proposing any solution.

⚠

Compliance risk — immediate SQL Server 2016 Extended Support officially ended July 12, 2026. The organisation is now operating a production analytics environment with no Microsoft security patches, no compliance coverage, and increasing audit exposure. This is not a future risk — it is a present one.

Current state

SSAS Multidimensional on SQL Server 2016

The existing environment runs SSAS Multidimensional cubes — the older MDX-based architecture. This technology predates 2012, has no direct cloud equivalent, and is no longer on Microsoft's strategic roadmap. There is no "lift and shift" path to Azure; a deliberate rebuild is required.

Technical debt

Why Multidimensional has no cloud path

SSAS Multidimensional uses MDX (Multidimensional Expressions) — a query language and storage model not supported in Azure Analysis Services or Microsoft Fabric. Both cloud platforms support Tabular models only. The migration therefore requires a model rebuild, not a migration of files.

Strategic direction

Microsoft Fabric is the target state

Microsoft Fabric unifies Azure Synapse, Power BI, ADF, and Analysis Services into one platform. Fabric Semantic Models (formerly Power BI Premium datasets) are the long-term successor to both Azure Analysis Services and SSAS Tabular. Building toward Fabric future-proofs the investment.

Opportunity

More than compliance — modernisation

This migration is not just a compliance exercise. Done right, it delivers a faster, more maintainable analytics layer, Power BI-native reporting, lower infrastructure cost (no on-prem server), and DAX-based models that are better understood by a wider pool of engineers and analysts.

💡

Immediate bridge action While the migration project is being scoped and resourced, register the SQL Server 2016 instance with Azure Arc to qualify for Extended Security Updates (ESU) at no additional charge for Azure customers. This buys compliance coverage during the migration window — typically up to 3 additional years, though acting immediately is still recommended.

Section 02 · Architecture

The Two-Phase Migration Pathway

There is no single-step migration from SSAS Multidimensional to the cloud. The path is deliberate and sequential — each phase independently deliverable and verifiable.

Current State

SSAS Multidimensional
(SQL Server 2016)

MDX cubes, on-prem, end-of-support. SSIS feeds, SSRS consumers. Complex calculated members, named sets, KPIs in MDX syntax.

MDX queries On-premises SQL Server 2016 SSRS / Excel

Phase 1
Rebuild

Intermediate State

SSAS Tabular
(SQL Server 2019/2022)

Same business logic, rebuilt in Tabular model using DAX. Validated against existing cube outputs. Deployed on updated SQL Server. Users see no change.

DAX measures On-premises or VM SQL Server 2022 Power BI / Excel

Phase 2
Cloud lift

Target State

Microsoft Fabric
Semantic Model

Tabular model deployed to Fabric. Data sourced from ADLS / Synapse or Dataverse Synapse Link. Power BI-native. Managed cloud, zero infrastructure.

DAX measures Cloud-native Fabric / Azure Power BI

MDX cubes · No cloud path · EOL now

→

Logic preserved in DAX · Validated against old numbers

→

Zero on-prem infra · Power BI native · Auto-updated

ℹ

Why two steps — not one? Attempting to jump directly from SSAS Multidimensional to Fabric in one step combines two transformation risks: (1) re-learning MDX→DAX logic, and (2) re-pointing all data sources to cloud. Separating these means Phase 1 can be validated against existing data on known infrastructure, before cloud complexity is introduced in Phase 2.

Multidimensional vs Tabular vs Fabric — key differences

Capability	SSAS Multidimensional	SSAS Tabular	Fabric Semantic Model
Query language	MDX (+ DAX limited)	DAX (+ MDX compatible)	DAX (primary)
Cloud deployment	✕ Not supported	▲ Azure Analysis Services	✓ Fabric native
Power BI integration	▲ Via gateway only	▲ Via gateway or AAS	✓ Direct, zero-gateway
Performance (typical)	Good for large aggregations	Faster for most query patterns	Fastest — columnar + DirectLake
Infrastructure management	On-prem server required	On-prem or Azure VM	✓ Fully managed by Microsoft
Microsoft future investment	✕ Maintenance only	▲ Stable, limited new features	✓ Primary investment focus
Calculated members	MDX — complex, flexible	DAX measures — simpler syntax	DAX measures — identical to Tabular
Security model	Cell & dimension security	Row-level security (RLS)	RLS + Object-level security (OLS)

Section 03 · Phase 1

Phase 1 — Assessment & Tabular Rebuild

The most technically demanding phase. Every MDX calculation must be understood, documented, and faithfully re-expressed in DAX. No number should change without a business reason.

Step 1A — Deep assessment of the existing cube

Inventory every artefact

All measure groups and their source fact tables
All dimensions, attributes, and hierarchies
All calculated members and named sets (MDX)
All KPIs — definition, target, status expressions
All perspectives (if used)
All roles and security definitions
All SSIS packages feeding the cube
All SSRS / Excel reports consuming the cube

Tools for the inventory

SSMS DMVs — query $SYSTEM.DISCOVER_XML_METADATA to extract full model schema
SQL Server Profiler — capture live MDX queries to see what users actually run
Tabular Editor — open the .bim export for structural analysis
Excel PivotTable connections — find all workbooks connected to the cube
SSRS Report Manager — list all reports with cube data sources
Business user interviews — which numbers do they trust? Which do they cross-check?

Step 1B — MDX to DAX translation

MDX and DAX approach calculations fundamentally differently. MDX is set-based and navigates the cube space; DAX is column-based and works with filter context. Each MDX calculated member needs to be re-understood in DAX terms — not line-by-line translated.

Rolling 12-month sales (MDX)

ParallelPeriod([Date].[Year], 1, [Date].[Month].CurrentMember)

Set-based navigation over the date dimension hierarchy

→

Rolling 12-month sales (DAX)

CALCULATE([Sales], DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -12, MONTH))

Filter context manipulation — same result, different mental model

Year-over-year % (MDX)

([Measures].[Sales], ParallelPeriod([Date].[Year],1))

References prior period via tuple navigation

→

Year-over-year % (DAX)

DIVIDE([Sales] - [Sales LY], [Sales LY])

Explicit measure reference with CALCULATE + SAMEPERIODLASTYEAR

Named set — Top 10 products (MDX)

TopCount([Product].[Product].Members, 10, [Measures].[Sales])

Named set defined once, reused across reports

→

Equivalent in DAX / Power BI

TOPN(10, ALL('Product'), [Sales], DESC)

Used inside measures or as Power BI visual filter — more flexible

⚡

The hidden risk in MDX translation Many MDX calculated members contain undocumented business assumptions — which warehouse locations to include, how to handle null members, fiscal year offsets. These must be surfaced and documented during assessment, then verified by a business owner before the DAX equivalent is written. A technically correct DAX measure that reflects the wrong business rule is worse than no measure at all.

Step 1C — Tabular model design principles

Schema

Star schema — strictly enforced

Tabular models perform best with a clean star schema: one fact table per subject area, dimension tables with single-column surrogate keys. Avoid snowflaking — flatten dimension hierarchies into single tables.

Measures

All logic in measures — not columns

Do not put business logic in calculated columns unless required for relationship keys or row-level filtering. Every KPI, ratio, and time-intelligence calculation should be a DAX measure. Measures are recalculated in context; columns are computed at refresh — wrong placement kills performance.

Date table

Explicit date dimension — always

Create a dedicated Date table (marked as a Date Table in Tabular) with continuous dates covering all fact data. All time-intelligence DAX functions — SAMEPERIODLASTYEAR, DATESBETWEEN, DATESYTD — require this. Do not rely on auto-date tables.

Security

Row-Level Security (RLS) design

SSAS Multidimensional dimension security maps to Tabular RLS roles. Map each existing dimension security rule to a DAX filter expression on the relevant table. Test every role before cutover — RLS failures are a serious data governance risk.

Partitions

Partition strategy for large fact tables

Partition large fact tables by month or quarter. Incremental refresh processes only new/changed partitions — critical for supply chain fact tables with high daily transaction volumes. This reduces nightly processing window from hours to minutes.

Naming

Business-friendly naming from day one

Use business names (not source column names) for all measures, dimensions, and attributes. "Gross Sales Amount" not "AMT_SALE_GRS". This is the moment to clean up years of cryptic technical naming — it will not get easier later.

Step 1D — Validation & parallel run

✓

The golden rule: numbers must match before cutover Run the old SSAS Multidimensional cube and the new Tabular model in parallel for a minimum of 4–8 weeks. For every key metric, compare old vs new across at least 3 historical reporting periods. Any divergence must be explained — either the old cube was wrong (document why the new number is correct) or the new model has a bug (fix it). Only cut over when a business owner signs off that the new numbers are trusted.

Section 04 · Phase 2

Phase 2 — Cloud Migration to Microsoft Fabric

With the Tabular model validated on-prem, Phase 2 moves the model and its data sources to the cloud. The semantic model is already correct — the task is re-pointing data sources and deploying to Fabric.

Step 2A — Data source migration

For D365 sources

Dataverse Synapse Link

If the organisation is migrating to D365 cloud, use Dataverse Synapse Link to continuously replicate D365 tables into Azure Data Lake Gen2. The Tabular model's data source is re-pointed from on-prem SQL Server to the ADLS / Synapse tables. Near-real-time replication with no SSIS packages to maintain.

For non-D365 sources

Azure Data Factory (ADF)

For any remaining on-prem data sources (ERP databases, flat files, APIs), build ADF pipelines to land data into ADLS Gen2 or Azure SQL. These replace the on-prem SSIS packages. SSIS packages can also be lifted to ADF's SSIS Integration Runtime as a transitional step, then gradually rebuilt as native ADF pipelines.

Step 2B — Deploying the Tabular model to Fabric

Step 1

Export .bim file from SSDT / Tabular Editor

The Tabular model built in Phase 1 is exported as a .bim (Business Intelligence Model) JSON file from SQL Server Data Tools or Tabular Editor. This file contains the full model definition — tables, relationships, measures, roles.

.bim fileVersion controlled in Git

Step 2

Create a Fabric Workspace & Semantic Model

In Microsoft Fabric, create a dedicated workspace for the analytics layer. Import the .bim file as a new Semantic Model. Fabric accepts the same Tabular model format — DAX measures, relationships, and RLS roles all carry over.

Fabric workspaceSemantic Model deployed

Step 3

Re-point data sources to ADLS / Synapse

Update all data source connections in the Fabric Semantic Model to point at the cloud data sources (ADLS Gen2, Azure SQL, or Synapse Analytics) rather than the on-prem SQL Server. Use DirectLake mode where possible — this allows Fabric to read Delta tables in ADLS directly without an import step, delivering the fastest query performance.

Cloud data sources configuredDirectLake mode enabled

Step 4

Configure refresh schedule & monitoring

Set up incremental refresh in Fabric (replacing the SQL Agent SSAS processing jobs). Configure failure alerting via Fabric monitoring hub or Power Automate. Set up semantic model refresh logging to detect silent failures — one of the key risks in the current on-prem environment.

Refresh scheduleFailure alerts configured

Step 5

Re-point Power BI reports & Excel workbooks

Migrate SSRS reports to Power BI reports built on the Fabric Semantic Model. For Excel workbooks using Analyze in Excel or OLAP connections, re-point the connection string to the Fabric workspace. Power BI reports built on top of the model can be deployed to the same Fabric workspace.

Power BI reports migratedExcel connections updated

Step 6 · Cutover

Decommission on-prem SSAS environment

After a parallel validation period (minimum 4 weeks), formally decommission the on-prem SSAS Multidimensional cube and the Phase 1 Tabular model. Retain read-only access for 30 days post-cutover as a safety net. Archive the SSDT project files in Git.

Sign-off from business ownersOn-prem decommissionedArchive completed

Section 05 · Reporting Layer

Reporting Migration — SSRS to Power BI

The reporting layer migration runs in parallel with Phase 2 and does not need to be completed before cutover — but a clear plan for each report type is required.

Report type	Current tool	Target tool	Effort	Notes
Interactive dashboards	SSRS / Excel PivotTable	Power BI Report (Fabric)	▲ Medium	Rebuild in Power BI Desktop against Fabric Semantic Model. Better UX for users.
Pixel-perfect paginated reports	SSRS (.rdl files)	Fabric Paginated Reports	✓ Low	Same RDL file format — upload to Fabric, re-point data source. Minimal rework.
Excel Analyze in Excel	SSAS OLAP connection	Fabric Semantic Model connection	✓ Low	Update connection string in Excel. Pivot tables continue working — no rebuild.
Scheduled email reports	SSRS subscriptions	Power BI subscriptions / Power Automate	▲ Medium	Rebuild subscription logic in Power BI or Power Automate. Better delivery options.
Operational reports (high volume)	SSRS parameterised	Fabric Paginated Reports	✓ Low	Keep as paginated for performance and format consistency. RDL migration is low risk.

ℹ

Adoption strategy — do not force the switch Run Power BI alongside SSRS during the migration window. Identify a champion in each business team. Let users pull toward Power BI by demonstrating its advantages (drill-through, mobile, self-service). Only retire SSRS once usage data shows Power BI has replaced it for key reports. Forcing a big-bang switch is the most common cause of BI migration failure.

Section 06 · Risk Register

Risks, Mitigations & Owners

Every identified risk with a concrete mitigation — not generic advice.

Risk	Severity	Phase	Mitigation
SQL Server 2016 security breach during migration window	Critical	Now	Register with Azure Arc immediately for Extended Security Updates. Network-isolate the SSAS server. Patch any OS-level vulnerabilities.
Undocumented MDX business logic lost in translation	High	Phase 1	Full MDX inventory before any DAX work begins. Business owner sign-off on every calculated measure definition. Parallel run validation for 3+ periods.
Numbers change between old and new model — user distrust	High	Phase 1	Reconciliation report comparing old vs new for every key metric. Any difference must be explained. Never cut over with unexplained variances.
SSIS packages failing silently — stale data in cube	High	Phase 1	Add email/Teams failure alerts to all SQL Agent jobs. Add row-count validation steps to each SSIS package. Create a data freshness indicator visible in reports.
Row-Level Security gaps — wrong data exposed to wrong users	High	Phase 1–2	Map every existing dimension security rule to Tabular RLS before go-live. Test each role with real user credentials. Security sign-off required before cutover.
Performance regression — Tabular slower than Multidimensional for specific query patterns	Medium	Phase 1	Profile the 10 slowest MDX queries from Profiler. Reproduce them as DAX. If performance is worse, add aggregation tables or review the partition strategy. Tabular is faster for most patterns — regression is uncommon but possible for deep many-to-many cube scenarios.
Cloud data source latency — Fabric refresh slower than on-prem SSAS	Medium	Phase 2	Enable DirectLake mode in Fabric (reads Delta tables directly — no import). Validate refresh window against business SLA before cutover. Incremental refresh on large fact tables reduces this significantly.
User resistance to Power BI — SSRS dependency	Medium	Phase 2	Paginated Reports for all SSRS equivalents — same look and feel. Keep SSRS live during transition. Run Power BI training workshops. Identify a power user champion in each team.
Key person dependency — only one engineer knows the cube	Medium	All phases	Documentation-first approach. All model artefacts in Git. The migration itself produces the documentation — every DAX measure written gets a business definition written alongside it.
Scope creep — stakeholders add new requirements mid-migration	Low	Phase 1–2	Strict change freeze on the existing cube during migration. New requirements go into a backlog for Phase 3. Communicate: migration reproduces what exists — enhancements come after.

Section 07 · Delivery Plan

9-Month Delivery Roadmap

A realistic, month-by-month plan with clear deliverables at each milestone. Timeline assumes a single lead data engineer with part-time business analyst support.

Immediate · Week 1

Azure Arc registration — compliance bridge

Register SQL Server 2016 with Azure Arc to activate Extended Security Updates. This removes the immediate compliance risk and buys time for the migration project to be properly resourced. Estimated effort: half a day.

Azure Arc enabledESU activatedCompliance risk mitigated

Month 1 · Weeks 1–4

Assessment — inventory & documentation

Full audit of the SSAS Multidimensional cube: all measure groups, dimensions, calculated members, KPIs, named sets, roles, SSIS feeds, and report consumers. Business owner interviews. Output: a complete artefact inventory and a business-approved data dictionary.

Artefact inventoryMDX logic documentedData dictionary draftSSIS package register

Month 2–3 · Weeks 5–12

Tabular model build — core measures & dimensions

Build the star schema Tabular model in SSDT or Tabular Editor. Migrate all dimensions and fact tables. Translate all MDX measures to DAX. Implement RLS roles. Deploy to a SQL Server 2022 development environment for initial testing.

Tabular model (dev)All DAX measuresRLS roles implementedGit repository set up

Month 4 · Weeks 13–16

Validation — parallel run against old cube

Deploy Tabular model to UAT. Run parallel reconciliation: every key metric compared between old cube and new model across 3 reporting periods. Business owner sign-off on all measures. Fix any divergences. Do not proceed to Phase 2 until sign-off is complete.

Reconciliation reportBusiness sign-offBug fixes complete

Month 5 · Weeks 17–20

Cloud infrastructure setup

Provision Azure Data Lake Storage Gen2, Fabric workspace, and Synapse or ADF pipelines. Configure Dataverse Synapse Link (if D365 is in scope). Migrate or rebuild SSIS packages as ADF pipelines. Set up monitoring and alerting infrastructure.

ADLS Gen2 provisionedFabric workspace createdADF pipelines builtMonitoring configured

Month 6–7 · Weeks 21–28

Fabric Semantic Model deployment & Power BI

Deploy Tabular model to Fabric. Re-point data sources to cloud. Enable DirectLake where applicable. Migrate SSRS paginated reports to Fabric Paginated Reports. Build key Power BI reports on the Fabric Semantic Model. User acceptance testing with business teams.

Fabric Semantic Model livePower BI reports deployedPaginated reports migratedUAT sign-off

Month 8 · Weeks 29–32

Parallel run — Fabric vs on-prem

Run Fabric Semantic Model and on-prem Tabular model in parallel for 4 weeks. All key users validate their reports on the Fabric version. Track any issues. Build user confidence before decommission.

Parallel run completeUser feedback resolvedFinal sign-off

Month 9 · Cutover

Decommission on-prem — migration complete

Formally retire the SSAS Multidimensional cube and the SQL Server 2016 server. Decommission the Phase 1 Tabular model on-prem. Archive all project files in Git. Retain read-only access to old server for 30 days. Migration complete.

On-prem decommissionedDocumentation finalisedArchive completeHandover done

Decision framework — when to escalate vs proceed

Stop & escalate immediately if…

Reconciliation shows unexplained variances > 0.1% on revenue or inventory
RLS testing reveals data visible to wrong user groups
Azure Arc registration fails (compliance gap remains open)
Any production SSIS package failure goes undetected for > 24 hrs

Review before proceeding if…

Tabular model query performance is > 2x slower than old cube on key reports
Cloud data refresh is consistently missing the business SLA window
More than 30% of users have not completed Power BI UAT
Scope additions threaten the cutover timeline by > 2 weeks

Proceed with confidence when…

All key metrics reconciled and signed off by business owners
All RLS roles tested with real user accounts
Fabric refresh completing within SLA window for 2+ weeks
At least one champion in each business team using Fabric reports

Success metrics

Unexplained metric variances at cutover

100%

MDX calculated members documented before Phase 1 ends

<15 min

Target Fabric incremental refresh window (vs hours on-prem)

On-prem infrastructure remaining post-cutover

9 mo

End-to-end migration timeline

Zero

Reporting downtime during migration

SSAS Multidimensional
to Microsoft Fabric

The case for acting now

Context & Why This Migration Is Necessary

SSAS Multidimensional on SQL Server 2016

Why Multidimensional has no cloud path

Microsoft Fabric is the target state

More than compliance — modernisation

The Two-Phase Migration Pathway

Multidimensional vs Tabular vs Fabric — key differences

Phase 1 — Assessment & Tabular Rebuild

Step 1A — Deep assessment of the existing cube

Inventory every artefact

Tools for the inventory

Step 1B — MDX to DAX translation

Step 1C — Tabular model design principles

Star schema — strictly enforced

All logic in measures — not columns

Explicit date dimension — always

Row-Level Security (RLS) design

Partition strategy for large fact tables

Business-friendly naming from day one

Step 1D — Validation & parallel run

Phase 2 — Cloud Migration to Microsoft Fabric

Step 2A — Data source migration

Dataverse Synapse Link

Azure Data Factory (ADF)

Step 2B — Deploying the Tabular model to Fabric

Reporting Migration — SSRS to Power BI

Risks, Mitigations & Owners

9-Month Delivery Roadmap

Decision framework — when to escalate vs proceed

Stop & escalate immediately if…

Review before proceeding if…

Proceed with confidence when…

Success metrics

SSAS Multidimensionalto Microsoft Fabric

The case for acting now

Context & Why This Migration Is Necessary

SSAS Multidimensional on SQL Server 2016

Why Multidimensional has no cloud path

Microsoft Fabric is the target state

More than compliance — modernisation

The Two-Phase Migration Pathway

Multidimensional vs Tabular vs Fabric — key differences

Phase 1 — Assessment & Tabular Rebuild

Step 1A — Deep assessment of the existing cube

Inventory every artefact

Tools for the inventory

Step 1B — MDX to DAX translation

Step 1C — Tabular model design principles

Star schema — strictly enforced

All logic in measures — not columns

Explicit date dimension — always

Row-Level Security (RLS) design

Partition strategy for large fact tables

Business-friendly naming from day one

Step 1D — Validation & parallel run

Phase 2 — Cloud Migration to Microsoft Fabric

Step 2A — Data source migration

Dataverse Synapse Link

Azure Data Factory (ADF)

Step 2B — Deploying the Tabular model to Fabric

Reporting Migration — SSRS to Power BI

Risks, Mitigations & Owners

9-Month Delivery Roadmap

Decision framework — when to escalate vs proceed

Stop & escalate immediately if…

Review before proceeding if…

Proceed with confidence when…

Success metrics

SSAS Multidimensional
to Microsoft Fabric