When using Power BI and pulling data from Azure Synapse, you will use the “Get Data” feature in Power BI. There are now three connections that you can use that I will cover in this blog. But first, a review of the various ways to store data in Synapse:
Lake databases: Previously, Synapse workspaces had a kind of database called a Spark Database. Tables in Spark databases kept their underlying data in Azure Storage accounts (i.e. data lakes), and tables in Spark databases could be queried by both Spark pools and by serverless SQL pools (if the tables are in Parquet or CSV format). To help make it clear that these databases are supported by both Spark and SQL and to clarify their relationship to data lakes, they have renamed Spark databases to Lake databases. Lake databases work just like Spark databases did before – they just have a new name. Any databases created using database templates is also a Lake database. They will show up under “Lake database” on the Data tab in Synapse Studio. See The best practices for organizing Synapse workspaces and lakehouses.
SQL databases: These are dedicated SQL pool relational databases or serverless databases (that use external tables) created using serverless SQL pools. They will show up under “Lake database” on the Data tab in Synapse Studio.
The three connections you can use in Power BI to connect to Synapse are:
Azure Synapse Analytics SQL – Can be used to connect to SQL databases (dedicated SQL Pools and serverless SQL pools), as well as Lake databases (if they are in Parquet or CSV format). Have to enter the Dedicated SQL endpoint or Serverless SQL endpoint
Azure SQL Database – Can also be used to connect to SQL databases (dedicated SQL Pools and serverless SQL pools), as well as Lake databases (if they are in Parquet or CSV format). Have to enter the Dedicated SQL endpoint or Serverless SQL endpoint. This seems to be the exact same connector as Azure Synapse Analytics SQL
Azure Synapse Analytics workspace (beta) – Recently released (see Supercharge BI insights with the new Azure Synapse Analytics workspace connector for Power Query). Can connect to Lake databases (if they are in Parquet or CSV format) and dedicated SQL pools, but it seems not serverless databases yet (I’m running into errors and will report back). The cool thing is you don’t need to enter endpoints, just sign in to your organization in Power BI and you will see all the Synapse workspaces under your subscription, and under each workspace you will see all the Lake databases and SQL databases you have access to
Note that with all these connectors, Lake databases and SQL databases are not separated out in the Power BI Navigator after you connect to Synapse, rather all grouped together.
Make sure to read Azure Synapse and Delta Lake on the limitations on the types of data that can be accessed using Azure Synapse serverless SQL pools.
The post Power BI connectors to Azure Synapse first appeared on James Serra's Blog.
