How to Connect Power BI to a SQL Server Analysis Services Database

June 26, 2018

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

In this tutorial, you will learn how Power BI connects to a SQL Server Analysis Services (OLAP) database and how to get data from there. SQL Server Analysis Services databases are one of the most common data sources for Power BI.

The first step is to go to Get Data >> Analysis Services:

This opens the Analysis Services connector:

Enter the SQL Server name and the database name as required:

Next, you have two options to choose from:

Import – Loads data from database into Power BI Desktop
Connect Live – Data stays in the database and the query is executed each time you interact with visuals

For this example, we will use the Connect Live option.

Although it is possible to write in an MDX or DAX query, this isn’t very common. Just click OK and the connection will be established. The Navigator window will show the cubes that are in the database. If you select a cube it will show the dimensions and measures that exist in the cube. Select the Sales cube and click OK.

At this point, you are ready to start creating visualizations.

Ex. Drag Sales and Profit out onto the canvas and format them to use the Card Visual:

Next, drag out Sales, Profit, and Profit % as a “Line and clustered column chart” across months using the date hierarchy in the Posting Date dimension. Dragging Profit % into the Line Values section will plot it on a separate axis.

Then drag the Company dimension out and format it as a Slicer visual with a horizontal orientation to make it easy for users to filter data by each company.

Finally, click the Image button in the Insert section of the ribbon and select a file with your corporate logo to personalize the dashboard with your company branding.