How to Connect Power BI to a SQL Server Analysis Services Database
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.
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.