Welcome to our blog post on how to connect an Analysis Services database in Power BI. This article is aimed at individuals who are learning SSAS and want to create reports using Power BI.

Analysis Services is an OLAP technology used for decision support that can be complemented with Power BI. In this example, we will show you how to connect Power BI with an Analysis Services Multidimensional project.

Requirements to Connect an Analysis Services Database in Power BI

Before we begin, there are a few requirements that need to be met:

1. You need to have the AdventureworksDW database installed. In this example, we are using the AdventureworksDW2019 database. This database contains dimensions and fact tables used to generate multidimensional cubes.
2. You also need to have the Adventureworks Multidimensional project. This project contains data and examples to demonstrate the features and information that can be obtained from the multidimensional database. You can download the Multidimensional project here.
3. Make sure you can connect to the AdventureworksDW2019 database.

Process the Cube

Processing the cube ensures that the information is up to date in SSAS. Here are the steps to process the cube:

1. First, process the cube information including all the dimensions and cubes from the model.
2. Next, run the process with the Run button.
3. If everything is fine, the Measuregroups and dimensions will be processed, and a green “Process succeeded” message should be displayed.

Connect with Power BI

Assuming you have Power BI Desktop installed, follow these steps to connect with Power BI:

1. Open Power BI Desktop and go to Home in the menu.
2. Select Get Data > Analysis Services.
3. Enter the SSAS Server name with the multidimensional model.
4. Optionally, enter the name of the database.
5. You can choose to import the data or connect to it.
6. You can also generate MDX queries or DAX queries to retrieve the data.
7. Power BI will list all the measure groups. Select the measure group you want to analyze and press OK.
8. Once selected, you will see the measure groups (in red) and the dimensions (in blue).

Create Reports

Now that you are connected to the Analysis Services project, let’s create some reports:

1. Drag and drop the Internet Average Sales Amount onto the report.
2. Include the Product Category to see the sales by category.
3. Add a filter using the Calendar Year.
4. You can add additional filters to the report to further analyze the data.

Map Visuals

In the model, you can check the relationships between tables. Note that there is a sphere icon which indicates that you can use the information in maps. Here’s how you can work with map visuals:

1. Check the geography of the fields. Note that the Map visual is created by default.
2. Include Reseller Orders in the map.
3. Graphically analyze the reseller orders. Note that the size of the circles represents the magnitude of the orders.

Conclusion

In this article, we learned how to configure the Adventureworks SSAS Multidimensional project and connect to it using Power BI Desktop. We created simple reports and worked with maps using the data. By leveraging the power of Analysis Services and Power BI, you can gain valuable insights and make informed decisions based on your data.