Connecting Power BI to SSAS Multidimensional Models - What You Need to Know

A surprising number of Australian organisations still run SQL Server Analysis Services multidimensional models as the backbone of their reporting. These SSAS MD cubes have been doing the heavy lifting for years - sometimes over a decade - powering Excel PivotTables, Reporting Services dashboards, and custom applications. They work. People understand them. There's institutional knowledge baked into those cube designs.

But the world has moved to Power BI, and the question comes up in almost every analytics engagement we run: "Can we connect Power BI to our existing SSAS cubes?"

The answer is yes, and it works better than most people expect. Microsoft has published detailed documentation on connecting Power BI to SSAS multidimensional models, but let me walk through what we've actually experienced setting this up for clients.

How the Connection Works

The connection is straightforward. In Power BI Desktop, you hit Get Data, pick SQL Server Analysis Services database, and point it at your SSAS instance. But here's the thing you need to understand upfront: this is a live connection. Power BI doesn't import the data. It doesn't create a local copy. Every time a user interacts with a report - clicking a slicer, expanding a matrix, switching pages - Power BI sends a DAX query to the SSAS server and waits for the response.

This has real implications. Report performance depends entirely on your SSAS server's capacity and network latency. If your SSAS server is a well-tuned beast sitting in a data centre with fast connectivity, reports will be snappy. If it's a VM that's been running at 95% CPU since 2019, well, your Power BI reports will reflect that reality.

The Power BI service supports these live connections too. You publish a report, and it continues to query your on-premises SSAS server through a gateway. Users in the Power BI service get the same live experience as someone using Power BI Desktop.

The Translation Layer - How Power BI Sees Your Cube

Power BI was built around tabular concepts, so when it connects to a multidimensional cube, it translates the multidimensional objects into tabular equivalents. Understanding this mapping helps you predict what your reports will look like:

Your cube becomes the model. Each cube dimension appears as a table. Dimension attributes show up as columns. Measure groups also appear as tables (with a sigma icon in the Fields pane). Individual measures come through as measures. And any KPIs you've defined carry over with their Value, Goal, Status, and Trend components.

One thing I genuinely appreciate about this translation: it's sensible. If you've defined display folders in your cube to organise measures into logical groups, Power BI respects those folders. So if your cube has 200 measures neatly arranged in folders like "Revenue", "Costs", and "Volume", they'll appear the same way in Power BI's Fields pane. That matters when you're handing reports to business users who need to find things without calling IT.

What Works Well

Hierarchies translate cleanly. If you've built user hierarchies in your cube - say Geography with Country > State > City - they appear in Power BI as expandable hierarchies. Users can drill up and down just as they would in Excel. Parent-child hierarchies also work, though they're exposed as separate hidden columns for each level.

Dimension attribute types carry over. If you've set up a Geography dimension with proper attribute types (City, State-Province, Country, Postal Code), Power BI picks up those types from the metadata. You'll see map icons in the Fields pane, and map visualisations work automatically. This saves a lot of manual configuration time.

Image URLs work. If your dimension attributes include URLs pointing to images, Power BI can render them in visuals. We've seen this used for product catalogues where each product row shows a thumbnail. It's not something everyone needs, but when you do, it's nice that it just works.

Security is respected. If your cube has dimension security through roles, Power BI honours those restrictions. A user who can only see their region's data will only see their region's data in Power BI reports. This is a big deal for organisations with strict data access requirements - you don't need to rebuild your security model.

What Doesn't Work or Has Rough Edges

You can't create report-level measures. This is the limitation that catches people out most often. With a tabular live connection or an import model, you can create new DAX measures right in Power BI Desktop. With an SSAS multidimensional live connection, you can't. The only measures available are the ones defined in the cube. If you need a new calculated metric, someone has to add it to the cube and redeploy.

This constraint has a real practical impact. It means your cube developer becomes a bottleneck for report development. A report author who wants to show "Revenue per Employee" can't just write a quick DAX measure - they need to request it, wait for the cube deployment, and then use it. For organisations that want self-service analytics, this friction is notable.

Named sets and actions don't come through. If your cube relies heavily on named sets (predefined sets of dimension members) or actions (links or drillthrough operations attached to cells), those won't appear in Power BI. You can still connect to cubes that contain them, and everything else will work. You just won't see those specific features.

Calculated members have constraints. Calculated members on attribute hierarchies work, but with conditions. The attribute can't be the key attribute (unless it's the only attribute in the dimension), and it can't be a parent-child attribute. Calculated members on user hierarchies don't come through at all. If your cube makes heavy use of calculated members for things like "Other" categories or dynamic groupings, test carefully before assuming Power BI will display them.

Cell-level security blocks the connection entirely. Dimension security works fine - restricted members just don't appear. But if a user has cell-level security restrictions, they can't connect to the cube at all through Power BI. It's an all-or-nothing situation. For organisations that use cell security (which is less common than dimension security, thankfully), this can be a deal-breaker for certain user groups.

The 32-bit vs 64-bit provider issue. If Power BI can see your cube's metadata but can't retrieve data, check whether you've installed the 32-bit or 64-bit MSOLAP provider. Power BI Desktop is 64-bit, so it needs the 64-bit provider. This sounds trivial, but we've seen it waste half a day for teams that didn't know to check.

SQL Server Version Requirements

This one is easy to overlook. For live connections, you need either Enterprise or BI edition of SQL Server 2014, or Standard edition of SQL Server 2016 or later. If you're running Standard edition 2014, you're out of luck for live connections.

Given that SQL Server 2014 went out of extended support in 2024, this shouldn't affect many organisations. But we still occasionally encounter production environments running older versions, particularly in industries where "if it works, don't touch it" is the prevailing philosophy.

Should You Keep Your SSAS Cubes or Migrate?

This is the real question behind the technical one. Connecting Power BI to SSAS MD is often a stepping stone rather than a destination.

For organisations that have well-functioning cubes with complex calculations, established security models, and cube developers on staff, the live connection approach can work for years. It lets you modernise the front end (Power BI reports instead of Excel PivotTables or Reporting Services) without rebuilding the back end. That's a legitimate strategy, and the migration risk is low.

But there's a gravity pulling toward tabular models - either Azure Analysis Services, Power BI Premium datasets, or Microsoft Fabric capacity. Tabular models offer better performance for most workloads, support report-level measures (big deal for self-service), and integrate more tightly with the modern Microsoft data platform. Most of the new tooling and features Microsoft ships are tabular-first.

We've helped several Australian organisations through this transition. The typical path is: connect Power BI to existing SSAS cubes (quick win, low risk), build new reports on top, then gradually migrate cube logic to tabular models as capacity allows. It's not a "rip and replace" - it's a staged modernisation.

If your organisation is running SSAS multidimensional and you're thinking about what comes next, our Power BI consulting team can help you assess the options. We've also done extensive work with Microsoft Fabric for organisations ready to move to the latest data platform. And if you want your team to build the skills to manage this transition internally, we offer training programmes covering Power BI and the modern Microsoft analytics stack.

The connection from Power BI to SSAS multidimensional works. It's not perfect - the measure limitation alone makes that clear. But as a way to get modern, interactive reports on top of proven cube infrastructure, it does the job well enough to justify the effort.