Live SSAS Multi-Dimensional Connection from Power BI

2

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;

1

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

2

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

3

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

4

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

5

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

6

and you can simply build any reports you want.

9

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.

7

  • 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.

8

  • 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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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.

2 thoughts on “Live SSAS Multi-Dimensional Connection from Power BI

  • hello reza, Direct Connect mode aka Connect Live (also named Lived Connection) is still a bit mysterious to me although i’ve tried to find out some answers on internet.
    And according to different blogs contents, (correct me if i’am wrong) i’ve tried to sum up the distinctions between Direct Connect mode and DirectQuery with 2 following sentences :
    Direct Connect mode is dedicated only to SQL Server Tabular & Multidimensional Analysis Services databases (both on premise and cloud versions) and source data model structure is not propagated within Power BI data model tab.
    Direct Query is dedicated to connect to different available SQL Server relational databases (both on premise and cloud versions) and like in Import mode, the model structure is maintained within the Power BI model tab.
    But what is confusing me is now apparently for SQL Server 2016 Analysis Tabular db is apparently also now available in Direct Query mode with DAX functions avaible with some restrictions according to following url :
    https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016
    So could please help me clarifying the situation regarding the 3 last sentences i’ve wrote.
    BTY, Is Power BI online stil doesn’t support live connection to on-premises SSAS multi-dimensional db as mentioned in your post?
    Many thanks for your feedback

    • Hi Stephane,
      This post is now old with the speed of updates in Power BI. Now you can use LiveQuery connection to SSAS MD or Tabular from Power BI. to confirm your questions. here are differences;
      LiveQuery is live connection to SSAS MD or Tabular only. you cannot edit relationship or create measures (yet!) from Power BI. All you can do is mere visualization.
      DirectQuery is direct connection to data sources such as SQL Server, Oracle, Teradata… you CAN edit relationship, and create basic measures (sum of something or average…) or even basic transformations in edit queries. But you cannot have data from more than one data source.
      Import Data is bringing the whole data set into Power BI. This method if full function Power BI; Full Power Query, DAX, and Visualization is supported.
      There will be a blog post soon about differences of these methods in details 🙂

      Cheers
      Reza

Leave a Reply