Integrate Power BI with Azure Synapse Analytics

This tutorial guides you through the process of integrating Azure Synapse Analytics with Power BI to visualize your data. Learn how to connect Power BI to your Synapse SQL pool and create compelling reports.

Prerequisites

An Azure Synapse Analytics workspace with a provisioned SQL pool.
Power BI Desktop installed on your machine.
Appropriate permissions to access your Synapse SQL pool.

Step 1: Connect Power BI to Azure Synapse Analytics

Open Power BI Desktop. In the Home tab, click Get Data, and then select Azure Synapse Analytics SQL.

Get Data from Azure Synapse Analytics in Power BI Desktop

Figure 1: Selecting Azure Synapse Analytics SQL as a data source.

In the connection dialog:

Server name: Enter the fully qualified server name of your Synapse SQL pool. You can find this in the Azure portal under your Synapse workspace's SQL pools.
Database name (optional): You can specify your database name here or select it later.
Data Connectivity mode: Choose between Import and DirectQuery. For large datasets, DirectQuery is often recommended.

Click OK.

Step 2: Authenticate your connection

You will be prompted to authenticate. Use your Azure Active Directory credentials or SQL Server authentication, depending on your Synapse setup.

For enhanced security and performance, consider using Azure Active Directory authentication.

Step 3: Select Tables and Load Data

Once authenticated, the Navigator window will appear, showing the schemas and tables available in your SQL pool. Select the tables you want to analyze and click Load. If you chose Import mode, the data will be loaded into Power BI. If you chose DirectQuery, Power BI will query the data directly from Synapse as needed.

Navigator window in Power BI showing Synapse tables

Figure 2: Selecting tables from your Synapse SQL pool.

Step 4: Build your Power BI Report

With your data loaded, you can now use Power BI's visualization tools to create reports. Drag and drop fields from your tables onto the report canvas, choose different chart types, and apply filters to explore your data.

Example: Creating a Sales Performance Dashboard

Let's assume you have a 'Sales' table and a 'Products' table. You can create visuals such as:

A bar chart showing total sales by product category.
A card displaying total revenue.
A map visualizing sales by region (if you have geo-location data).

To create a bar chart for sales by category:

Drag the 'Category' field to the canvas.
Drag a measure like 'SalesAmount' to the canvas. Power BI will likely default to a bar chart.
Format the chart for readability.

-- Example SQL query you might run against Synapse Analytics
SELECT
    p.Category,
    SUM(s.SalesAmount) AS TotalSales
FROM
    Sales s
JOIN
    Products p ON s.ProductID = p.ProductID
GROUP BY
    p.Category
ORDER BY
    TotalSales DESC;

Step 5: Publish your Report

Once you are satisfied with your report, you can publish it to the Power BI service for sharing with others.

In Power BI Desktop, click the Publish button on the Home tab.
Select a workspace in the Power BI service to publish to.

Ensure your Power BI gateway is configured correctly if you are using on-premises data sources or need to schedule refreshes for datasets connected via DirectQuery.

Step 6: Schedule Data Refresh (Optional)

If you used the Import mode, you can schedule automatic data refreshes in the Power BI service to keep your reports up-to-date.

Navigate to the dataset in the Power BI service.
Go to Settings and configure the Scheduled refresh options.

Conclusion

By following these steps, you can effectively connect Power BI to your Azure Synapse Analytics SQL pool, analyze your data, and create insightful reports and dashboards. This integration empowers you to leverage the power of Synapse for data warehousing and analytics, with Power BI providing the visualization layer.

Previous 1 Next