fbpx Skip to content

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

insightsoftware -

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:
Power Bi Analysis Services

This opens the Analysis Services connector:
Power Bi As Connector

Enter the SQL Server name and the database name as required:
Power Bi Server And Database

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.

Power Bi Connect Live

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.
Power Bi Navigator

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:

Power Bi Visuals

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.

Power Bi Line And Cluster Chart

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.
Power Bi Slicer

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.
Power Bi Logo

We love working with Power BI and we love showing you how to do more with it. Learn more about how Jet Reports works with Power BI.

Learn also how to configure and connect the Simba Spark JDBC Driver with SQLLine.