Cube Transform secrets

When working with PowerBI Datasets or SSAS Cubes as sources in PowerBI, you face a common headache. The default approach used by PowerBI is to pull the entire dataset into Power BI and then filter it down. It's inefficient and slow to work with whenever you have to make a change. This article shows you how to filter SSAS and Power BI datasets at the source using Cube.Transform and MDX, saving you from unnecessary data transfers and performance bottlenecks.

Why Bother with Source Filtering?

This use case came up for me as I was working with a massive PowerBI dataset that covered a companys global operations. The Power BI report I was working on only needed data for Australia, for a subset of time and an even smaller subset of the company's range of products. I had to make some minor changes, but it was literally taking hours to effect and validate each change, so I started to hunt for a better way. 

Here's what typically happens without source filtering:

1. Your Power BI refresh and even minor changes take ages
2.  PowerBI Desktop Memory usage goes through the roof
3. Your IT team sends you unhappy emails about resource usage on the PowerBI Service
4. Everything slows down, and you end up watching data load for far too long

Enter Cube.Transform - Your New Best Frenemy

When you connect to a PowerBI dataset or SSAS cube, it does so using the Cube.Transform function, which allows you to bring in data from such sources. However, this function does not allow you to apply any filtering to the source - it's the whole cube or nothing. So what typically happens in a developer scenario is you pull everything across, and filter it using Table.SelectRows - but that's like downloading the entire internet when you only need one webpage.

Now this is where I went down a bit of a rabbithole, and why this article will save you experiencing the same pain. Surely the PowerBI Developers (fab guys generally) can't have omitted adding even some basic filtering beyond picking what dimensions and metrics you need? Spoiler alert: they did.

I fought with Cube.ApplyParameter, which helpfully offers in the documentation "Returns a cube after applying parameter with arguments to cube." Which requires the Cube to be set up to accept parameters. So that is no use in filtering the source using your own set of parameters. I also looked at trying to issue a DAX query (not supported as far as I could work out), trying various different syntaxes of Cube.AddAndExpandDimensionColumn until eventually I had a brainwave which made me grateful for my limited but passable MDX from back in my on premise SQL Server days.

Re-enter Cube.Transform - a best friend when its buddy MDX comes out to play too.

Instead of dumping the entire dataset into Power BI and then filtering it, you can tell SSAS exactly what you want before it sends anything across. It's like having a smart filter at the source - you only get what you need, when you need it.

How it works is like this; the documentation merely says:

Cube.Transform(cube as table, transforms as list) as table

With the "helpful" description:

Applies the list cube functions, transforms, on the cube.

What is doesn't say - which is kind of important - is that the "cube as table" can be a valid MDX query. As SSAS in all its forms and PowerBI Datasets both can be queried by MDX.

Here's how you'd implement it in pseudocode:

let

Source = AnalysisServices.Database("server/instance", "DatabaseName"),

Model = Source{[Catalog="ModelName"]}[Data],

FilteredData = Cube.Transform(Model, 

{"

SELECT [Measures].[Amount] ON COLUMNS,

NON EMPTY {[Date].[Calendar].[Date].MEMBERS} ON ROWS

FROM [Model]

WHERE [Region].[Region Hierarchy].&[APAC]

"})

in

FilteredData

My MDX isn't much to talk about, but this is where I leant on my good friend Claude (though feel free to use your GenAI tool of choice) to rewrite the PowerQuery into some passable MDX which I could then substitute in. Now my hour long changes were responding in minutes, and my development accelerated significantly. 

Closing thoughts

When you're implementing this in production, here's what you need to think about:

• Structure your MDX queries logically - future you will thank present you
• Remember that bad MDX will still perform slowly so write efficient queries
• Document your work - your colleagues aren't mind readers

But here's a good trade off for venturing into MDX land:

• Memory usage drops dramatically
• Refreshes actually finish before your coffee gets cold
• Queries zip along nicely
• Your solution actually scales when the business dumps more data in

Filtering data after ingesting using Cube.Transform with MDX might seem like more work upfront compared to just dragging everything across and filtering it later. But it's worth it. Your reports will run faster, your refreshes will be quicker, and you won't have to explain to management why your PowerBI solution is crawling along. Plus, you'll have the satisfaction of knowing you've done it the right way, which any good developer should be smug about :)