Most people in the data space have heard of Azure Synapse Analytics. It is the premier service that brings together data warehousing capabilities, data integration and data analytics – including machine learning and AI capabilities. It has become one of the go-to services for organisations to leverage the power of their data, and for good reason. Power BI is also known as one of the most powerful programmes for visualisation so combining the two is a no-brainer.
This blog aims to show how Power BI and Azure Synapse Analytics can be seamlessly integrated together to leverage the power of both services in one place.
Connecting Power BI Direct to Synapse SQL Pool
Connecting to Azure Synapse from within Power BI can be a simple process; if the data is stored within a SQL database on a dedicated pool within Synapse, the method is straightforward.
Within Power BI Desktop, using the Azure Synapse Analytics Connector SQL connector behaves like most other database connectors.
The server is either the Dedicated SQL endpoint or the Serverless SQL endpoint which can be found on the overview tab in the Synapse Workspace. This is dependent on the type of SQL pool created within Synapse. For this example, I am using the Serverless SQL endpoint as the data is contained within the Built-in pool which was specified to be serverless.
There are three options for the Data Connectivity mode:
- Import – Bring the data into memory within Power BI
- Direct Query – For live data that needs to be updated regularly
- SQL statements – Write custom SQL queries and return the contents of the query into Power BI
This method is the simplest way to get your data from Synapse into Power BI to start creating reports and gaining insights from your data. However, there is another method which integrates Synapse and Power BI together within the Synapse Workspace to provide that one-stop-shop and truly leverages the capabilities of both.
Integrating Synapse and Power BI
Power BI Datasets and reports can be created directly within a Synapse Analytics Workspace. This can be done by creating a linked service connection to a Power BI Workspace.
With this linked service created, navigate to the develop area within the Synapse Workspace. The workspace will now appear within the Develop area and new Power BI datasets can be created.
Connecting to the SQL Pool and DB containing data. There will be an option to download a .pbids file and open it within Power BI Desktop. This will automatically connect to the SQL pool within Synapse and any tables can be loaded in, transformed, and modelled. Likewise with the normal database connector, there is the option for import or direct query.
The report can then be published to the linked workspace. For this example, I published a very simple report to show the functionality to edit reports within Synapse.
The Power BI report then can be edited, and visuals created directly within the Synapse Workspace. If Direct Query is utilised, real time changes from within the data source can be fed through into the Power BI report to see, in real time, the effect of any changes made within Synapse.
Final Conclusions
There are some limitations – you obviously don’t have the full capabilities of Power BI when building reports within Synapse. For example, any changes that need to be made to Power Query or the data model and relationships must be done within Power BI desktop.
However, the benefits far outweigh the limitations: from providing a centralised place for users to collaborate, to utilising a common interface for both the data manipulation capabilities of Synapse and the brilliant visualisations of Power BI, integrating these two platforms can be a game-changer. You can truly leverage the power of Synapse to improve performance within Power BI; from result set caching, to automatically caching query results and the creation of materialised views to store pre-computed data. Both these features reduce the computational requirements for Power BI, resulting in fast and responsive reports. Finally, it ensures a single source of the truth for Power BI, meaning insights are reliable and can be trusted.
Integrating Power BI and Azure Synapse enables data engineers, data scientists and data analysts to all collaborate and unlock the value in an organisation’s data.
To explore how Azure Synapse can further your organisation’s insights with flexibility, scalability and cost-effective data analytics, data science or data architecture services, browse our Microsoft Azure Services today.
Blog Author
Alex Duff, Senior Consultant, Simpson Associates
Back to blog