Complete Analytics Fabric – Azure Synapse and Power Query (Power BI) better together

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..

The new Azure Synapse Analytics workspace connector

Which is a warning that this is a development connector and third-party software is used to connect. I’ll continue …

The warning when using the connector the first time

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

The list of Synapse workspace the authenticated 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?

The data artifacts in my Synapse Workspace

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.

Which objects are available in the Power Query connector?

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.

Select different tables and use them in Power BI

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.

Happy data set creation!

About wolfgang

Data Platform enthusiast
This entry was posted in Azure, Azure Synapse Analytics, Business Intelligence, PowerBI. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s