Power BI – Getting Data from Analysis Services

Introduction

Microsoft Power BI is a cloud based self-service BI solution for the enterprise. Power BI provides capabilities that can be leveraged by people with all level of skills including data analyst, power users, business users, data stewards and folks from the IT department.

I’ve discussed Power BI in detail in the last couple of articles and demonstrated how you can create reports or dashboards on the data you have. Over a period of time, you need to refresh the data to reflect the current state or use a mechanism to query data interactively. In this article I am going to demonstrate how you can use Analysis Services connector to query your on-premise data from an Analysis Services instance in Power BI reports or dashboards interactively. Subsequently, in the next article I will talk about scheduling data refresh for other types of data sources.

Understanding Power BI – Analysis Services Connector

Power BI Analysis Services connector lets you query data from on-premises SQL Server Analysis Services instance interactively (as of this writing only tabular models are supported, although multi-dimensional support is expected to come soon). Analysis Services connector is a client or proxy agent that you need to install on any of the machines in the on-premises domain (recommended to install at the same machine on which the Analysis Services instance is running for better performance and to avoid latency). Analysis Services facilitates behind-the-scenes communication from a user in Power BI to your on-premises Analysis Services instance and back to Power BI in a secure and quick way. You can download and install this connector from here.

Understanding Power BI
Understanding Power BI

If you have more than one Analysis Services instance to connect data, you’ll need to install and configure one Analysis Services Connector per instance. Installation of Analysis Services Connector is usually done by an administrator who has Server Administrator permission on the Analysis Services instance although once configured, it can be used by any user who has access to the Analysis Services database.  

Getting Started with Power BI – Analysis Services Connector

Once installed, a user with Server Administrator permission on an Analysis Services instance can configure the Analysis Services connector. On the first screen of the wizard of the Analysis Services connector, click on the Next >> button.

Power BI Analysis Services Connector Wizard
Power BI Analysis Services Connector Wizard

On the next screen of the wizard, you need to connect to the Power BI account where you want to use data, which should come from the on-premises Analysis Services.

Sign into Your Power BI Account
Sign into Your Power BI Account

On the next screen of the wizard, you need to specify the Analysis Services Server name; for the default instance you can simply specify the machine name and for the named instance you need to specify machine name\instance name.

Next specify the Windows User Name that has Server Administrator permission on the specified Analysis Services instance, followed by the respective password for the specified windows user name.

Login Screen
Login Screen

Now, on the next screen of the wizard, you can specify the Friendly Name for the connection so that Power BI users can easily identify it while browsing on the Power BI site. You can also specify a description with more details about the connection to help Power BI users to get to know more about the connection. With Friendly error message, you can specify a customized error message to be shown when a Power BI user is not able to connect from the Power BI to the on-premises Analysis Services instance that you have configured.

Friendly Name and Description
Friendly Name and Description

On the final screen of the wizard, you should be able to see the configuration status as shown in the figure below.

Configuration Status
Configuration Status

Once installed and configured, it enables you, with Power BI, to connect to an on-premises Analysis Services instance and query it interactively.

To get started with it, go to the Power BI site, click on the Get Data link and then click on SQL Server Analysis Services from the list on the left pane as shown below.

Power BI Site
Power BI Site

Clicking on the Connect button in the above screen will take you to a page where you can see all the configured Analysis Services servers that are registered with Power BI. You can search for your configured server by using the available Search box on the top.

SQL Server Analysis Services Servers
SQL Server Analysis Services Servers

Now click on the identified Analysis Services server in the above screen, and it will connect to the respective server and will display all the models or cubes available, if you have at least read permissions on them. (For this demonstration, I used AdventureWorks Tabular Model SQL Server 2012 sample, which is available here for download). You can select the cube that you want to use and click on the Connect button.

Select the Cube
Select the Cube

Clicking on the Connect button will create a dataset, which you can verify under the Datasets tree view. Please note, the Analysis Services connector allows you to have a live connection to the on-premise Analysis Services server and is always up to date. In other words, you don’t need to setup or schedule a data refresh cycle for the data coming from the on-premises Analysis Services instance when using Analysis Services connector.

Datasets
Datasets

With the available dataset, you can start creating reports. For that, click on the dataset and you will see the data model on the right as shown below. Now you can drag and drop fields from the Fields list on the report canvas; Power BI will appropriately pick up the visualization type and will show it. In case you want to change visualization, click on the tiny Change Visualization type icon on the right top of the selected visualization and change to any available other visualization.

You can also create multi-page reports here, as shown below, by adding more pages to the report.

Multi-page Reports
Multi-page Reports

Once you are done with creating reports you can save the report or a copy of the report by clicking on the SAVE link in the menu and by giving an appropriate name, as shown below.

Save Your Report
Save Your Report

Power BI includes a rich set of visualizations or charts for better insight and you can use them to analyze your dataset from different angles or views. For example, I created this simple but nice looking report in few minutes, based on the dataset I created earlier on the data from on-premises Analysis Services.

Simple Report
Simple Report

The good part of Power BI Visualizations or charts is, they are linked together in the report page and hence for deeper insight, you can click or filter on any of the charts and the rest will be updated to reflect the selection. For example, as shown below, I clicked on the year 2008 in the first bar chart and as you can see the other three charts have been refreshed to show or highlight only data for the year 2008.

Refreshed Charts
Refreshed Charts

Security While using Analysis Services Connector

A person with Server Administrator permissions configures the Analysis Services Connector and from then on anyone with access to the Analysis Services instance (with their role membership on that instance) can access and use data from Analysis Services. Power BI uses the EffectiveUserName attribute in the connection string to pass on the context of the user who is creating the report and Analysis Services Connector in turn validates and uses the same to query the data from Analysis Services by way of impersonation (please note, credentials of the person who configured the Analysis Services Connector is not used while querying the data from the Analysis Services) and returns the only data on which the user has access to.

As of this writing, when a dashboard is shared with others, others will indirectly inherit all the permissions from the original report author (in essence, when an author shares a dashboard he\she actually shares the data view he\she has access to). For example, consider person A creates a dashboard and shares it with person B, in this case person B will be able to see all the data that person A can see (even though person B directly might not have permission to access the data).

Data Refresh – Queries Being Sent to Analysis Services

Before I begin this discussion, let me talk about a Power BI Report and a Power BI Dashboard.

You can create a Power BI report based on data from a single data source. This report can contain multiple pages, each page having multiple report items or charts control to show the data.

A dashboard is a place where you pin your report items and share it with others. Typically a dashboard contains multiple report items; each of these report items might come from different reports and hence might get data from separate data sources. At the same time, a single report item can also be used in multiple dashboards.

When you click or interact with a Power BI report, Field list or Filter (like clicking on a chart, selecting a filter, or adding a new field to the report, etc.), Power BI generates equivalent DAX queries and sends queries to Analysis Services for execution whereas for the dashboard, Power BI issues queries to Analysis Services approximately every 10 minutes to keep the dashboard up-to-date.

Please note, Power BI and Power BI Analysis Services connector is in its preview stage right now; features mentioned in this article might change in the final release.

Conclusion

Analysis Services connector lets you query data from on-premises SQL Server Analysis Services instance interactively from the Power BI site. In this article, I discussed Analysis Services connector, how to install and configure it and how to use it for querying data directly from an on-premises Analysis Services instance in an interactive manner and how to create Power BI reports or dashboards.

Resources

Microsoft Power BI

Microsoft Power BI Analysis Services Connector (Preview)

Getting Started with Power BI Designer

Understanding Microsoft Power BI – Self Service Solutions

Getting Started with Microsoft Power Query for Excel

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles