In late June last month, the Microsoft Power BI team released the Microsoft Power BI Analysis Services Connector. The Power BI SSAS Connector allows your deployed Power BI reports to utilize your on-prem SSAS data sources. It’s super easy to set up and can be downloaded for free! And who doesn’t love “free”?

Download the MS Power BI SSAS Connector here

Why Use the Power BI SSAS Connector?

One of the advantages to using Power BI is that the tool has the ability to connect to an incredibly wide variety of data sources including SQL Server Analysis Services instances. With that in mind, what’s the purpose of using the Power BI SSAS Connector? Why not just load our SSAS data into our Power BI semantic model like we do with our Access, Excel, CSV and web data and then schedule the Power BI semantic model to refresh? That’s a good question.

Power BI: Live & Prime Time with SSAS Tabular

First of all by utilizing the Power BI SSAS connector, we are granted a live connection to our SSAS instance. What this means is that every time a user interacts with a filter, slicer, chart or other data visualization, Power BI quickly generates a DAX query behind the scenes which is sent to your on-prem SSAS Tabular model. Now currently Power BI users are restricted to how often data sources are refreshed. If you’re a free Power BI user you’re limited to one data refresh per day and if you’re a Power BI Pro user you’re limited to Hourly data refreshes. By leveraging the live to connection to your SSAS Tabular instance, you can update the data in your Tabular model as often as you are able.

Because the Power BI SSAS Connector allows you to have a live connection to your SSAS Tabular model, this also means that your users experience less latency between updates to their data. Without the live connection to SSAS, each day the users would have to wait for the SSAS Tabular model to be processed and then for the Power BI semantic model to be refreshed. With the live connection, as soon as the Tabular model has finished processing, the Power BI users have access to the most current data instantly. Data is available to your users with potentially much less time between data refreshes.

Currently, the Power BI SSAS Connector only supports live connections to SSAS Tabular instances, although I would expect a future update to support live connections to SSAS multidimensional cubes.

Enterprise Data in Power BI

Currently Power BI semantic models are restricted by a data capacity limit. If you’re a free Power BI user you’re limited to 1 GB per/user and if you’re a Power BI Pro user you’re limited 10 GB/user. This can be a problem if you need to access more than the limit. With the live connection to SSAS Tabular, you’re only limited to as much data as you can fit in your Tabular model. If you have a 200 GB SSAS Tabular model, all 200 GB of data is at your finger tips in Power BI in real time while using the Power BI SSAS Connector.

As the amount of data in your Power BI semantic model increases, the time required to refresh the model also increases. By utilizing your on-prem SSAS Tabular instance, you can take advantage of the ability to partition a Tabular model in such a way that you can separate your volatile data from your static data. A properly partitioned Tabular model can be quickly reprocessed and refreshed with the latest data with minimal latency.

SSAS Has Role Based Row-Level Security

Another huge advantage to utilizing the Power BI SSAS Connector is that you can now leverage the built-in SSAS security roles. What data  a user can access is based on the user’s Windows username. Usually a user will belong to a role that will grant the user read access to the data in the Tabular model.

Sometimes though, your Tabular model will be designed to utilize a more dynamic row-based security model. A dynamic row-based security model dictates which rows a user can access. This type of security also relies on the user’s Windows username. When you use the Power BI SSAS Connector, a user’s permissions are filtered through the built in SSAS security model defined by the SSAS developer.

Summary

Here’s the quick wrap-up on the advantages to using the Power BI SSAS Connector to create a live connection to your on-prem SSAS Tabular models:

1. Access your SSAS Tabular models live without delays while refreshing your Power BI semantic model.
2. Expose enterprise level amounts of data to Power BI with an SSAS Tabular model.
3. Take advantage of dynamic role based row-level security models designed in your SSAS Tabular model.

Resources

I’ve got a few resources here, but I want to share them all with you so you can have the links in one place:

• Download the Microsoft Power BI Analysis Services Connector
• Configure a Power BI Analysis Services Connector
• More in-depth information on the Power BI SSAS Connector
• Refreshing data in Power BI
• Compare Power BI Free with Power BI Pro

Feedback?

So what do you think? Will your organization be leveraging the Power BI SSAS Connector? Leave a comment below and let me know about your experiences with Power BI and SSAS.