The title pretty much says it all. The data model in my cube, which lives in Azure Analysis Services (AAS). Which means that at some point the performance of the cube will suffer which means anything you build on top of it will also experience loss of performance. Facing a similar problem? Here's my way of solving our woes.

Problem

You already have, or this is the case very soon, too much data in your cube. This impacts the performance of not only the cube, but everything you've build on top of it.

Of course you can just scale-up to a more expensive AAS tier. But even that has it's limits. Plus: not everyone has the budget to do so.

Solution part 1: indexing, indexing, indexing.

Alright, I put "indexing" in the heading because that's the word everyone reaches for when a database gets slow — but a tabular model in Analysis Services doesn't have indexes in the SQL sense. It uses the VertiPaq engine, which is a columnar store: instead of keeping your data row by row, it slices each column out on its own and compresses it hard. So the thing that's really going on here isn't "add an index", it's "help the compression do its job".

And the single biggest lever on compression is cardinality — the number of distinct values in a column. Low-cardinality columns (a Country with 30 values, a Status with four) compress beautifully, because VertiPaq can store each distinct value once and then just point at it. High-cardinality columns are the opposite: every value is nearly unique, there's nothing to fold together, and the column stays fat in memory. That's where your model is ballooning.

The usual offenders, in my experience:

  • Precise datetime columns. A column holding 2019-11-09 14:37:52.413 is basically unique on every single row — that's about as high-cardinality as it gets. If your reports only ever group by day, you're paying for the seconds and milliseconds for nothing. Split it into a Date column and a separate Time column. Now the date side has one value per day (lovely compression), and the time side, if you even keep it, has at most 86,400 distinct values instead of one per row. Two skinny columns beat one enormous one.
  • Over-precise decimals. A sensor reading stored to nine decimal places is nine decimals of cardinality you probably don't report on. Round it at the source to whatever precision the business actually looks at.
  • Free-text and unique IDs. Long description fields and GUID-style keys are cardinality nightmares. If you're not slicing or filtering on a column, ask hard whether it needs to be in the model at all (more on that in part 3).

There's also sort order. VertiPaq compresses better when similar values sit next to each other, because it can run-length encode them — store "this value, 4,000 times" instead of the value 4,000 times. You don't get fine-grained control over this in AAS the way you might dream of, but loading your fact data sorted by a low-cardinality column (rather than by a unique timestamp) can genuinely shrink the model. It's worth a measure before and after.

Before you do anything else, go find your highest-cardinality columns. That's almost always where the memory went.

Solution part 2: do I really need all this data?

This is the question nobody wants to ask, because the honest answer is usually "no, but it felt safer to keep it."

Tabular is an in-memory engine. Every row you load lives in RAM on the AAS instance, and RAM is exactly what you run out of (and exactly what the pricier tiers sell you). So the cheapest optimisation in the world is loading fewer rows.

Start with history. Does the model really need ten years of transactions in memory? Ask the people who use the reports, not the person who built the source system. More often than not the dashboards only look back two or three years, and the decade of cold data behind that is sitting in RAM purely because nobody told it to leave. Archive the cold stuff — keep it in the warehouse where storage is cheap, and only bring the recent window into the cube.

Then filter at the source. Don't load the whole fact table and let the model sort it out; put the WHERE clause in the query that feeds the partition, so the rows you don't want never make the trip. Filtering in the source database is free-ish; filtering in memory is the thing you're trying to escape.

And if there genuinely is a slice of detail people need but rarely touch — say the current year sits in the cube but someone occasionally wants to drill into raw line-level history — that's a case for a DirectQuery pattern rather than import. DirectQuery leaves the data in the source and queries it live, so it doesn't cost you any memory; the trade-off is that those queries are slower and lean on your source database being up to the job. A common shape is to keep the hot, summarised data imported (fast, in memory) and reach back to the source only for the rare deep dive. It's not free, it just moves the cost somewhere you can afford it.

The gotcha here: people hoard "just in case" data and then never query it. Check your usage — if a whole range of history hasn't been touched in a year, that's your answer.

Solution part 3: do I really need this level granularity?

Closely related, but worth its own heading because it's the part with the biggest easy wins.

Drop the columns you don't use. I mean it — this is the single highest-return thing on the list. Every column in a tabular model costs memory whether anyone queries it or not, because VertiPaq loads and compresses all of them. Go through your fact and dimension tables and be ruthless: that staging column, the three audit fields, the source-system key nobody reports on, the description you imported "to be safe" — if no report, relationship, or measure uses it, cut it. Models routinely carry 20–30% dead columns, and removing them is a refresh-and-done win with zero downside.

Next, aggregate to the grain the reports actually use. If your fact table is at transaction-line level but every dashboard rolls up to daily totals per store, then storing every individual line in memory is paying for detail nobody ever looks at. Pre-summarise the data in the warehouse to daily-per-store grain and load that into the cube. You can keep the gory detail in the source for the rare drill-through. A table that's 50 million lines at transaction grain might be two million rows at the grain people report on — and that's a 25× reduction before you've touched anything clever.

And watch your calculated columns. A calculated column is computed at processing time and stored, row by row, in memory — same cost as any other column. A measure, by contrast, is computed on the fly at query time and stored nowhere. A lot of calculated columns are really aggregations in disguise — a running total, a flag, a bucket — and those almost always belong as measures instead. Rewriting a calculated column as a measure removes it from memory entirely. Not every calculated column can move (if you need to slice or relate on it, it has to exist as a column), but a surprising number can, and each one you convert is pure savings.

Solution part 4: does everything really need to be in 1 model?

At some point the honest fix isn't to shrink the monolith — it's to stop building a monolith.

We have a habit of building one big model that holds everything, because it's convenient and because "one source of truth" sounds responsible. But a single model that serves Finance, Operations, and Marketing is carrying every table all three of them need, in memory, all the time — even though no single user touches more than a third of it.

So: split by subject area or audience. A focused Finance model and a focused Operations model are each far smaller than the combined beast, each refreshes faster, and each only costs memory for the data its users actually query. Yes, you lose the ability to write a single query that spans all of it — but ask whether anyone genuinely does that, or whether it's a convenience you're paying for in RAM every day.

If a full split is too much, perspectives are a lighter option: one model, but curated "views" that expose only the relevant tables and fields to a given audience. Perspectives don't reduce memory — the whole model is still loaded — but they reduce the overwhelm for users and keep things tidy while you decide whether a real split is worth it.

And whether you split or not, look at partitioning. This one confuses people, so let me be clear about what it does and doesn't do:

  • Partitions are about processing, not query speed. Slicing a fact table into partitions — typically by month or year — does not make user queries faster. What it does is let you process (refresh) one partition at a time.
  • That enables incremental processing. Instead of reprocessing the entire table every night — rereading and recompressing years of unchanged history — you reprocess only the current partition, the one that actually got new data. Last month's numbers don't change, so why recompute them at 2am every single day?

For a big model, moving from "reprocess everything" to "reprocess this month" is often the difference between a refresh that fits in your overnight window and one that doesn't. It won't shrink the model, but it'll stop the nightly refresh from being the thing that falls over.

In conclusion

Scaling up to a bigger AAS tier is a real option, and sometimes it's the right one — but it's the last thing to try, not the first. Throwing money at it masks the problem, and the problem tends to grow back.

Almost every "my cube is too big" situation I've run into turns out to be "my model is holding data it doesn't need, at a grain it doesn't need, in one place it doesn't need to be." Find your high-cardinality columns, drop the ones nobody uses, load fewer rows at a coarser grain, and split the monolith when it's earned it. Do the cheap, unglamorous work first. Nine times out of ten you'll claw back enough memory and enough performance that the upgrade conversation goes away — and if you still need the bigger tier after that, at least you'll be paying for a model that's actually pulling its weight.