Power BI Desktop comes with new updates today as well, and the best feature in this update (from my point of view) is Live connection to SSAS Multi-Dimensional. This feature is still in preview mode, but it is good to see that Microsoft Power BI team invested on this most wanted request from users. With this new feature you can connect to SQL Server Analysis Services 2012 SP 1 CU4 or higher versions from Power BI Desktop. The ability to connect from Power BI website as live connection to on-premises SSAS multi-dimensional however still not available, but I reckon it would be underway.

How Live Connection Works?

If you are not familiar with Live connection and might ask that what is Live or Direct connection then and what is the difference of that with regular connection, I have to point out you to this blog post about SSAS Tabular Live Connection from Power BI. However I’ll explain it here in a nutshell;

Power BI has a built-in data model engine which is Power Pivot. Everything that comes to Power BI will be loaded into Power Pivot, and will be processed there. That is why when you close your Get Data experience in Power BI Desktop then you will see messages that shows 1000 rows loaded into memory or messages like that. There is however another method of connection in Power BI. Power BI Desktop can connect to few data sources as direct / Live connection. This means that data of those data sources would be directly loaded into the report, No Power Pivot layer between! only structure of the source will be available in Power BI Desktop for building the report. This method of connection reduce one extra step which is loading the data again into Power Pivot component of Power BI. With this method if you have an existing data source (such as SSAS tabular) built and ready for reporting, then you connect it directly to Power BI without hassling of modeling it again into Power BI Desktop. However there are some cons for this approach, for some data sources the connection speed might be low and report take longer to visualize or refresh.

Live Connection for SSAS Multi-Dimensional

In July release of Power BI Desktop you could connect to SSAS Tabular as direct connection. Now with the new release of Power BI Desktop today you can connect to SSAS Multi-Dimensional and use it as a direct and live connection! Here is how you can do it:

You have to first set a configuration in Power BI Desktop Options;

In the Options window under Preview Features check the Explore live with SQL Server Analysis Services multidimensional models option.

Then you need to restart Power BI Desktop (Close and re-open)

After restart, when you go to Get Data, and then Databases, and choose Analysis Services as the source, you can choose Live Connection

After creating the connection you can choose which database, and cube you want to connect to

and then you will see all objects under that cube in list of Fields

and you can simply build any reports you want.

with this live connection you can use these SSAS objects and features;

• Default Members
• Dimension Attributes
• Dimension Attribute types
• Measures (with or without Measure groups)
• Measures as Variant
• KPIs
• ImageUrls
• Dimension security

Limitations of Live Connection

Live connection is great, however there are some limitations for it. Some of these limitations might be removed in later versions, but this is list of limitations at this point of the time;

• With Live connection you won’t see anything in Edit Queries tab in Power BI Desktop Except changing the connection window.

• With Live connection you won’t see Data or Relationship tabs in Power BI Desktop (because these features are for modelling into Power BI which is not available in live connection). You would only see Report tab.

• You can only connect to SSAS multi-dimensional 2012 SP 1 CU4 or higher versions through Live connection.
• Not all object types in SSAS multi-dimensional will be available through this connection. For example hierarchies, and display folders.
• You can’t publish Power BI report to Power BI website yet!

This doesn’t look right, does it? because you should be able to publish your report, otherwise what would be the point of creating it?! The reason for this is that Power BI website doesn’t support live connection to on-premises SSAS multi-dimensional. However please understand that this feature is still on preview mode, and I believe product team will bring it into Power BI website very soon.

• Because you don’t have Edit Queries experience and data modelling you cannot connect any other data source to this report.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.