When Power BI 2.0 released as General Availability I’ve published a post that highlights all new features in this product. One of the features was SSAS Tabular Live Power BI Connection. In this post I want to explore more what the Live connection means and what are advantages of this type of connection. Power BI Designer (Previous version of Power BI Desktop) was supporting offline connection to SSAS. The new SSAS Live connection of Power BI only works with SSAS Tabular at the moment.
Previously there was only one type of connection to SQL Server Analysis Services (SSAS) from Power BI, and that was offline connection. Offline means that connection only worked to fetch data for a query or more. The result of query have to manipulated in query editor, or in the model through relationship management, to finally be ready for report.
This method of offline connection is useful when you work with non-modeled data sources, such as CSV files, Web search, excel spreadsheet… But when you have a data model, such as star schema model in your SSAS then creating another model in Power BI Designer is just an extra step which needs extra effort and adds load time.
When you use Get Data to connect Analysis Services;
Here is an example of Offline connection:
Here is where you have fetch data, and build queries:
as you can see in screenshot above you cannot select the whole model, you should choose a dimension, measure group, or combination of attributes and measures. Then you have to build relationship between them in Power BI model.
and Here is where you have to build relationships again:
The new Power BI Live connection for SSAS Tabular removes this barrier, it is actually works like the Power View connection to SSAS, it loads the structure and data from the model within SSAS in an instant. there is no extra loading time required for the model, except the SSAS processing time itself. As a result Power BI Desktop is getting one step further to be a product that can be used in enterprise environments.
Here is where you connect to SSAS Tabular through Live connection:
You can select whole model in this mode;
and Here is all your model metadata loaded:
You will also mention that in this mode you won’t see Data and Relationship tabs in the left hand pane. you just see report tab.
The good news for you that have your data model in SSAS Tabular is that you don’t need to re-invent the wheel and built it again in Power BI relationship management.
The bad news is that if you have your model in SSAS Multi-Dimensional you still have to wait. Because this feature is only supporting SSAS Tabular at the moment. My assumption is that same type of connection will be available for SSAS multi-dimensional in next few months updates of Power BI.
SSAS Tabular On-Premises Connection
As a side note here I would like to mention that If you have built your SSAS Tabular on premises, you can connect to it through Power BI Analysis Services Connector.
Simply download the connector from here:
4 thoughts on “SSAS Tabular Live Power BI Connection”
Have you found any performance lag having the connector send each query to onprem?
Sorry for my late response. This all depends on the size of data, and how Tabular model created (best practices….). I hadn’t still chance to test that with a very large data set to see how it goes. I will update here if I see any performance issues.