As a follow up of todays PASSDataCommunitySummit keynote, in which Arun Ulag mentioned a new phrase – the Complete Analytics Fabric, I would like to write more about a new option to really get a complete picture of your Azure Synapse Analytics artifacts in your Synapse workspace in one got – in one Power BI dataset – with one connection option only. Sounds interesting? Yes.. it is a nice way..
The new Azure Synapse Analytics Workspace Connector for Power Query
Announced at Ignite 2021 (source: https://techcommunity.microsoft.com/t5/azure-synapse-analytics/supercharge-bi-insights-with-the-new-azure-synapse-analytics/ba-p/2929352) , the new Azure Synapse Analytics Workspace Connector allows you, as a Power Query / Power BI user to directly connect to all the data artefacts in a Synapse workspace. Prior to this new connector, you needed to connect to the SQL Pools and/or Spark database separately.
The November Release of Power BI Desktop includes a preview version of this new connector (Nov 2021 release blog post)
Let’s see how this connector works and which options you get by using it. In my demo example, I am using Power BI Desktop and as a first action, use the Get Data dialog to connect to a Synapse Workspace.
There are (as of today) two options to connect to Synapse Analytics – the “old” Azure Synapse Analytics (SQL DW) connector and the new Synapse Workspace connector. Select the new one and move on to the next step..
Which is a warning that this is a development connector and third-party software is used to connect. I’ll continue …
Up next – Authenticate using your AAD user that has access to a Synapse Analytics workspace.
The next dialog lists all the Synapse workspace your user has access to
Whenever I expand one of the workspace nodes, the available data artifacts (databases) are listed. In my demo workspace, the following objects are available. The question that comes up now is: What are all these objects? SQL Dedicated Pools? Serverless databases? Spark databases?
If we compare the list in Power BI Desktop and the objects in Synapse Studio, it will get clearer which objects are now available in the Power Query connection dialog.
As of today, Spark databases (now called Lake databases in Synapse Studio), SQL Serverless databases and Dedicated Pools are available for a connection.
The Synapse Analytics workspace connector now allows you to select tables (objects) from different sources and them in one connection and data access action.
In my example, Power BI did not ask if I want an Import or Direct Query connection – Import mode was chosen for me.
In my opinion, this new connector really makes our life as Synapse and/or Power BI developer easier – only one connector to use them all. Let’s see if the list of supported artifacts is extended (like it is mentioned in the announcement blog post) – this really would be very nice: SQL Pools, Spark databases and data lakes, and even linked services connected in one go.
- Power Query Blog: https://powerquery.microsoft.com/en-us/blog/enable-synapse-workspace-data-in-power-bi/
- Power BI Desktop November 2021 blog post: https://powerbi.microsoft.com/en-us/blog/power-bi-november-2021-feature-summary
Happy data set creation!