How to Scale Your Power BI Reports with Azure Analysis Services?

Power BI is a powerful tool for creating interactive dashboards and reports. But what if you need to analyze large amounts of data that exceed the capacity of Power BI?

How can you scale your Power BI reports to handle hundreds of GBs of data without compromising performance and user experience?

The answer is Azure Analysis Services, a cloud-based service that provides enterprise-grade data modeling and analysis capabilities.

Azure Analysis Services allows you to create semantic models that can be accessed by Power BI and other tools.

You can also use Azure Analysis Services to connect to various data sources, such as Azure SQL Database, Azure Data Lake Storage, or Azure Synapse Analytics.

In this tutorial, we will show you how to use Azure Analysis Services to scale your Power BI Azure analysis services reports into hundreds of GBs of data.

Step 1: Create an Azure Analysis Services server

The first step is to create an Azure Analysis Services server that will host your semantic model. To do this, follow these steps:

● Log in to the [Azure portal] and click on Create a resource.

● Search for Analysis Services and select it from the results.

● Click on Create and fill in the required information, such as the server name, subscription, resource group, location, and pricing tier. You can choose from different pricing tiers depending on your needs and budget. For this tutorial, we will use the S0 tier, which offers 10 GB of cache and 40 QPUs (query processing units).

● Click on Review + Create and then on Create to create the server.

Step 2: Import a Power BI report into Azure Analysis Services

The next step is to import a Power BI report into Azure Analysis Services.

This will allow you to convert your Power BI report into a semantic model that can be accessed by other tools. To do this, follow these steps:

● Open the Power BI Desktop application and open the report that you want to import. For this tutorial, we will use a sample report that analyzes sales data from a fictional company called Contoso.

● Click on File and then on Export. Select Export to .pbit and save the file in a convenient location.

● Open the [Azure Analysis Services web designer] and sign in with your Azure credentials.

● Click on Model and then on Import from Power BI Desktop (.pbit). Select the file that you saved in the previous step and click on Open.

● Wait for the import process to complete. You should see a message saying Import succeeded.

Step 3: Configure the data source and refresh settings

The third step is to configure the data source and refresh settings for your semantic model.

This will ensure that your model can connect to the original data source and update the data periodically. To do this, follow these steps:

● In the Azure Analysis Services web designer, click on Model and then on Manage.

● In the Data Sources tab, select the data source that was imported from Power BI. For this tutorial, we will use a sample data source that connects to an Azure SQL Database.

● In the Connection string field, enter the connection string for your data source. You can find this information in the [Azure portal] by navigating to your Azure SQL Database resource and clicking on Connection strings. You may need to replace some placeholders with your actual values, such as server name, database name, user name, and password.

● In the Impersonation mode field, select how you want Azure Analysis Services to authenticate with your data source. For this tutorial, we will use Service Principal, which means that we will use an Azure Active Directory (AAD) application to access the data source. To create an AAD application, follow these steps:

○ In the [Azure portal], click on Azure Active Directory and then on App registrations.

○ Click on New Registration and fill in the required information, such as the application name, supported account types, and redirect URI (optional). For this tutorial, we will use Contoso Sales App as the application name.

○ Click on Register and copy the Application (client) ID from the overview page.

○ Click on Certificates & secrets and then on New Client Secret. Enter a description and an expiration date for the secret and click on Add. Copy the Value of the secret and store it securely.

○ Click on API permissions and then on Add a permission. Select Azure SQL Database from the list of APIs and then select Delegated permissions. Check the box for user_impersonation and click on Add permissions.

○ In the [Azure portal], navigate to your Azure SQL Database resource and click on Access control (IAM). Click on Add and then on Add role assignment. Select SQL DB Contributor as the role, Azure AD user, group, or service principal as the assigned access to option, and enter the name of your AAD application in the select field. Click on Save.

● In the Azure Analysis Services web designer, enter the Application (client) ID and the Client secret that you copied in the previous step in the corresponding fields. Click on Test Connection to verify that the connection is successful.

● In the Refresh policy tab, select how often you want to refresh your model data. You can choose from different options, such as daily, weekly, monthly, or custom. For this tutorial, we will use a daily refresh schedule that runs at 12:00 AM UTC.

● Click on Save to save your data source settings.

Step 4: Publish the model and connect to Power BI

The fourth step is to publish your model and connect to Power BI. This will allow you to access your model from Power BI and create reports and dashboards based on it. To do this, follow these steps:

● In the Azure Analysis Services web designer, click on Model and then on Publish. Select the server that you created in the first step and click on Publish. Wait for the publishing process to complete. You should see a message saying Publish succeeded.

● Open the Power BI Desktop application and click on Get data. Select the Azure Analysis Services database from the list of sources and click on Connect.

● Enter the server name that you created in the first step and click on OK. You may need to sign in with your Azure credentials.

● Select the model that you published in the previous step and click on OK. You should see a list of tables and measures that were imported from Power BI.

● Click on Load to load the data into Power BI.

Step 5: Test the performance and scalability of the report

The final step is to test the performance and scalability of your report. You can compare how your report performs with Azure Analysis Services versus Power BI alone. To do this, follow these steps:

● In Power BI Desktop, create a report based on your model data. You can use any visuals or filters that you want. For this tutorial, we will use a simple report that shows total sales by product category and year.

● Click on Refresh to refresh your report data. Note how long it takes to refresh your report.

● Click on Performance Analyzer from the View tab. This will show you how long each visual takes to render in your report.

● Compare the refresh time and render time of your report with Azure Analysis Services versus Power BI alone. You should see a significant improvement in performance and scalability with Azure Analysis Services.

Conclusion

In this tutorial, we have shown you how to use Azure Analysis Services to scale your Power BI reports into hundreds of GBs of data.

We have covered how to create an Azure Analysis Services server, import a Power BI report into Azure Analysis Services, configure the data source and refresh settings, publish the model and connect to Power BI, and test the performance and scalability of the report.