SSAS Tabular vs Multidimensional: Choosing for a New Project

The first analytics meeting can turn into a debate about server modes. Tabular vs Multidimensional becomes easier when you start with the reports and their rules. Choose a model your team can operate.

Frame Tabular vs Multidimensional Around Business Questions

I start this choice with the business calculations. A list of server features doesn't explain how a financial total should behave. Write down the difficult totals first.

Sales amounts usually add across dates and products. Account balances don't add across time in the same way. Shared ownership introduces another rule about counting.

Ask who owns those rules after deployment. A model outlives the first developer. The team maintaining calculations and access permissions needs a clear understanding of the design.

SSAS is a separate service from the Database Engine. Its installed server mode determines the models it hosts. Check the intended deployment mode before building your project.

A new project normally starts with Tabular as the first candidate. Its table-based structure gives relational teams a familiar starting point. A specific multidimensional requirement can change that decision.

Compare Storage in Tabular vs Multidimensional

Imported Tabular models use columnar storage and compression in memory. Repeated values compress differently from unique text. The source database's size doesn't predict the final memory requirement reliably.

DirectQuery is another Tabular storage choice. It sends query work to the source rather than importing the same data. That choice transfers requirements to the relational server and its query workload.

Multidimensional models organize measures and dimensions into cubes. MOLAP stores processed data and aggregations within Analysis Services. Other storage modes change the balance between source access and processed storage.

Neither model avoids memory planning. Processing, concurrent queries and caches all consume resources. A claim that one mode is automatically faster tells you too little.

Test the intended design with representative dimensions and calculations. Include refresh operations in that test. A model that queries well but cannot refresh within its window has another problem.

Match the Calculation Language to the Team

DAX is the main expression language for Tabular calculations. Filter context matters as much as the visible formula. A familiar SUM can behave differently inside a changed context.

MDX is central to multidimensional calculations. It works with members, tuples and sets. Cube calculations require a team comfortable with that model of evaluation.

Client query language support isn't an absolute dividing wall. Analysis Services supports combinations beyond the main authoring language. The calculation design and maintenance skills remain the practical distinction.

I ask the team to explain one difficult total without reading its formula. That exercise exposes gaps in understanding quickly. A successful deployment doesn't certify a calculation's meaning.

Tabular vs Multidimensional is also a staffing decision. Existing MDX expertise has value when the project needs cube behavior. Existing relational and DAX expertise makes Tabular easier to maintain.

Model Shared Ownership Carefully

Many-to-many relationships represent shared ownership or multiple classifications. A sale assigned to several categories illustrates the trap. Summing category subtotals can double-count the original sale.

Multidimensional supports these relationships through an intermediate measure group. The bridge describes the association between dimension members and facts. Test totals above the association's grain.

Recent Tabular models support direct many-to-many relationships at compatibility level 1500 and higher. Older models need different designs, such as bridge tables and explicit measures. Check the deployed compatibility level.

A relationship feature doesn't define allocation rules. Shared balances can appear under each owner without being divided equally. Decide whether that behavior matches the business question.

The SQL below gives a relational illustration in a Database Engine query window. The balances and owners are invented sample inputs. They explain the grain without claiming measured SSAS performance.

DECLARE @Balance TABLE(AccountId int PRIMARY KEY, BalanceAmount decimal(12,2));
DECLARE @Owner TABLE(AccountId int, OwnerId int);
INSERT @Balance VALUES(1, 100.00), (2, 200.00);
INSERT @Owner VALUES(1, 10), (1, 20), (2, 20);
SELECT o.OwnerId, SUM(b.BalanceAmount) AS OwnedBalance
FROM @Owner AS o
JOIN @Balance AS b ON b.AccountId = o.AccountId
GROUP BY o.OwnerId;
SELECT SUM(BalanceAmount) AS PortfolioBalance FROM @Balance;

Treat Large Dimensions as a Test Requirement

A large dimension with mostly unique attributes changes compression and processing costs. Remove unused columns before comparing server modes. Long descriptive text deserves particular attention.

Hierarchies and distinct counts also influence the design. Test the questions users ask at different levels. A total by year and a detail lookup aren't the same workload.

The following source profile runs in the Database Engine. It inspects the sample relationships without needing a deployed SSAS model. Replace the sample tables with representative warehouse tables for your review.

DECLARE @Dimension TABLE(MemberId int, DisplayName nvarchar(80));
INSERT @Dimension VALUES(1, N'North'), (2, N'South'), (3, N'West');
SELECT COUNT_BIG(*) AS SourceRows,
       COUNT(DISTINCT MemberId) AS DistinctMembers,
       MAX(DATALENGTH(DisplayName)) AS LargestNameBytes
FROM @Dimension;
SELECT MemberId, COUNT_BIG(*) AS DuplicateRows
FROM @Dimension
GROUP BY MemberId
HAVING COUNT_BIG(*) > 1;

Decide Whether Write-Back Is Essential

Native cube write-back is a meaningful multidimensional capability. Supported measure partitions record adjustments through a write-back table. Permissions and supported aggregation behavior need their own tests.

Tabular doesn't provide the same native cube write-back feature. An application can write approved adjustments into relational tables instead. The model then reads or refreshes those adjustments through its storage design.

That alternative changes latency and operational responsibilities. Don't call the two approaches identical. Ask whether planning users require interactive cube allocation or approved relational adjustments.

I argue against my Tabular starting point when native cube write-back is a firm requirement. The simpler familiar model isn't always the correct one. Requirements deserve more authority than preference.

Prototype Tabular vs Multidimensional Before Committing

Build a small representative model with the hardest measures first. Include shared ownership and a large dimension slice. Verify totals against hand-checked relational results.

Measure refresh duration and resource demand on your own server. Observe concurrent reports, not only a single quiet query. Keep the hardware and source data comparable.

What requirement would make you reject your preferred model? Write it down before the prototype. Otherwise the test becomes an elaborate way to confirm a favorite answer.

Tabular vs Multidimensional ends with an operational decision. Record the required features, available skills and tested limits. The cube-shaped icon isn't a capacity plan.

Review security using the intended report identity too. Correct totals with unrestricted administrative access don't prove the role design. Test permitted and forbidden slices before approving the model.

Related reading on this blog: Tabular Models for the SQL Developer and Optimizing Tabular Models for Self-Service Reporting: Summarize By: Notes from the Field #123.

A model choice is not a fashion vote, it is a decision about calculations and the team maintaining them.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.