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.

• 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!

How to change URL-copy&paste back to normal (URL only)

Some months ago I switched to Microsoft Edge as my default web browser. I don’t know when it started, but out of a sudden the copy&paste of URLs out of Edge ended strange in the destination application. A new feature in Edge changed the format of copied URLs in a more “user-friendly” format. Which is nice, yes, .. but…

User-Friendly Formatting for copied URLs

Just in case you do not know how this user-friendly URL does look like:

User-friendly URL copy & paste

How to change it back to URL only

For my job, I often have to write documentations and other documents and in those I want to see the real URL. So I started to do a little research and there are several options to the back to the URL only:

• Change the Paste mode in Word (Keep as Text only)

• Change the default way, how Edge copies URL

This is my preferred way – change the default setting how MS Edge copies URLs. Short way: open Edge and enter edge://settings/shareCopyPaste into your address bar (or you open Edge settings and navigate to the Share, copy and paste section).

This opens the settings page for URL sharing. Choose the link option if you want the user-friendly information, choose the plain text version if you want the link only.

I hope you liked this tip – otherwise this post is another reference for future Wolfi 😉

Happy copy&paste,

Wolfgang

PASS Data Community Summit – Keynote

The PASSDataCommunitySummit 2021 kicks off with a Keynote presented by Rohan Kumar

• Limitless cloud database services – choose the way / service that serves you best for your data
• Unmatched analytics and insights – choose the technology / services to analyze your data and get insights out of it
• Unified data governance – Rohan compares it to a shield around your data stores and analytic technologies to get the unified overview.

It’s the #DataCommunity

Community is about working together and for each other..

The #AzureDataCommunity has grown in the past months… >70k members, 130+ user groups around the world…

The Azure Data Communtiy grew within the last months around the globe – >70k members and almost 40 countries

Some events / initiatives from and for the community are presented:

• SQLSaturday
• DataSaturday
• NewStarsOfData

SQL Server 2022 – Preview

The first (early access) private preview is announced – https://cloudblogs.microsoft.com/sqlserver/2021/11/02/announcing-sql-server-2022-preview-azure-enabled-with-continued-performance-and-security-innovation/

Overview about the SQL 2022 features

Bob Ward demos (and Connor joined 🙂 the “it just gets faster without code changes” in SQL Server 2022 (Parameter sensitive plan optimization)

Bob and Connor discussing 🙂

No code change required – only the compatibility level of the database is changed.. and the magic happens

More about the (Azure) SQL Family

The (Azure) SQL family

“Build once – deploy anywhere”

Azure Arc-enabled data services

Azure Cosmos DB

Announcement: Azure Managed Instance for Apache Cassandra

Azure Synapse Link for SQL Server (2022+)

The Azure Synapse Link family is extended to SQL Server (2022+) and will be available in the future. Synapse Link is more than “just a linked server” -> Synapse Link publishes changes to Synapse and can then be used for analytics in Power BI.

Azure Synapse Link for Dataverse

It’s GA. Do some of you remember the Export to DataLake functionality? yes.. Synapse Link for Dataverse is definitely MORE than that…

From Dataverse to Azure Synapse to your the reporting tool of your choice.

Data is pushed from Dataverse to the landing part in Azure Synapse. A Spark database (containing external table definitions to the linked tables) is created. You can use this tables in either Spark or SQL Serverless (using the shared metadata approach) ..

Intelligence Platform

Arun takes over the stage

Empower every individual / every team / every organization

Power BI + Teams integration

Power BI + Excel integration

Power BI Goals

The Complete Analytics Fabric => Power BI and Synapse

Announcements for Power BI

• Patrick talks about Hybrid Tables and Automatic Aggregations.

Analyze the effects of Automatic Aggregations

Hybrid Tables – Import for historical data + Direct Query for the most up-to-date data.

Azure Purview – Data Governance

If you want to learn more about #AzurePurview – there is a video quickstart series available: https://workingondata.wordpress.com/2021/08/31/azure-purview-quickstart-series/

Data Lineage

Data Lineage is one of the key features required for a good understanding of your data landscape. Btw. if you want to learn more about data lineage, g2.com has a very good introduction: https://learn.g2.com/data-lineage

Integration of Purview and Synapse

Integration of Power BI and Purview

Integration of SQL Logic (Stored Procs…)

Ignite 2021 – Some of my Azure Data Highlights

This week is Microsoft Ignite 2021 week – virtual again! Three days full of content, mostly pre-recorded sessions and many announcements of new features.

Microsoft published – like for some other conferences in the past – a Book of News, which contains an overview about the features, products and options announced during the conference.

• Book of News (Microsoft Ignite November 2021) – https://news.microsoft.com/ignite-november-2021-book-of-news/

Those of you that know me, maybe 🙂 know my favorite topics – Azure Data, Power BI and Azure Purview. If you have a look at the Book of News, the section for Azure Data is not that long, BUT…

The section of Azure Data announcements at Microsoft Ignite 2021

Let’s dive a little bit deeper and look at some of the announcements (Full announcement text is found here https://techcommunity.microsoft.com/t5/azure-synapse-analytics/ignite-2021-announcements-accelerate-time-to-insight-with-azure/ba-p/2912147).

Azure Synapse Analytics

The Azure Synapse team announced some features around a third, new type of analytic pools – Data Explorer pools. With this type of analytics runtime, we are able to query log and telemetry data.

source: https://techcommunity.microsoft.com/t5/azure-synapse-analytics/ignite-2021-announcements-accelerate-time-to-insight-with-azure/ba-p/2912147

• Documentation – Azure Synapse Data Explorer – https://docs.microsoft.com/en-us/azure/synapse-analytics/data-explorer/data-explorer-overview

Azure Synapse Link for Dataverse is now GA. The concept of Synapse Link already an ETL-free extension of your analytic workspace into/ Cosmos DB and now is extended to support Dataverse. With this connection, selected tables out of a Dataverse database are replicated into a Synapse Workspace and are there exposed in a Spark database (which is – through shared metadata – accessible by the SQL Serverless runtime).

One of my overall highlights – Database templates – does not sound really interesting at the beginning. BUT – What I found out so far and if I understood it correctly, these database templates allow for a creation of a lake database based on data lake files. WITHOUT the need for coding – a UI-based editor is included in the first preview version. I am very curious to find out more about these lake databases.

source: https://docs.microsoft.com/en-us/azure/synapse-analytics/database-designer/concepts-lake-database

• Lake Databases – https://docs.microsoft.com/en-us/azure/synapse-analytics/database-designer/concepts-lake-database
• Lake Database templates – https://docs.microsoft.com/en-us/azure/synapse-analytics/database-designer/concepts-database-templates
• Overview Database templates – https://docs.microsoft.com/en-us/azure/synapse-analytics/database-designer/overview-database-templates

Delta Lake support is now GA. The functionality of read-access to delta lake files through the SQL Serverless pool was already available in preview for some time, now this functionality got into GA state.

• Delta Lake Support (SQL Serverless Pools) – https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-delta-lake-format

SQL Server 2022 is in Preview

Long live on-premises, long live SQL Server. The new version – SQL Server 2022 was announced in a preview version.

Many, many new features are mentioned in the blog post (https://cloudblogs.microsoft.com/sqlserver/2021/11/02/announcing-sql-server-2022-preview-azure-enabled-with-continued-performance-and-security-innovation/) , but one of them really found my attention – the Azure Synapse Link for SQL Server 2022. With that, the ETL-free connection between Azure Synapse Analytics and SQL Server 2022 will get into appearance. Think about (almost) real-time analytics by only connecting SQL Server with your Synapse environment – WITHOUT the need for ETL development! 🙂

source: https://cloudblogs.microsoft.com/sqlserver/2021/11/02/announcing-sql-server-2022-preview-azure-enabled-with-continued-performance-and-security-innovation/

• SQL Server 2022 – https://www.microsoft.com/en-us/sql-server/sql-server-2022
• Announcement blog post – https://cloudblogs.microsoft.com/sqlserver/2021/11/02/announcing-sql-server-2022-preview-azure-enabled-with-continued-performance-and-security-innovation/

Azure Purview

As you might have noticed, Azure Purview is one of my newest friends in Azure Data town. During Ignite, the support for Amazon RDS (Relational Database Service), the Data Lake Data Asset Access Governance, and Microsoft Defender for Cloud Integration with Azure Purview was announced.

What I really look forward to test is the Data Asset Access Governance for Data Lake storages. Imagine a world that allows you to define permission on a central place and those permissions are brought to a storage account / system of your choice..

If you want to get a quick preview of the Data Access functionality, please have a look at the video published at channel 9 – https://channel9.msdn.com/Shows/Azure-Videos/Azure-Purview-data-owner-provisioning-for-Azure-Storage

• Azure Purview announcements at Ignite: https://techcommunity.microsoft.com/t5/azure-purview/what-s-new-in-azure-purview-at-microsoft-ignite-2021/ba-p/2915954
• Azure Purview video series – https://www.youtube.com/watch?v=6JDINLykFnA&list=PLGdtUVeal1ELbPdRqNyNBb7-tR83Dp02Y
• Azure Purview blog posts: https://workingondata.wordpress.com/category/azure-purview/

Power BI

Last in my list, but definitely not least – Power BI. The full description of the announcements can be found here (https://powerbi.microsoft.com/en-us/blog/ignite-2021-enabling-data-driven-decisions-everywhere-collaboration-happens-with-enterprise-grade-analytics/)

Power BI and Teams are getting better together – the Power BI app for Teams is now GA and allows a deep integration of your Power BI environment directly in your teams application.

source: https://powerbi.microsoft.com/en-us/blog/create-a-more-data-driven-collaboration-with-the-power-bi-app-for-microsoft-teams-ga/

• What’s new (and better) in the Power BI app for Microsoft Teams – https://powerbi.microsoft.com/en-us/blog/create-a-more-data-driven-collaboration-with-the-power-bi-app-for-microsoft-teams-ga/

Power BI Goals got some enhancements too, like a new Scorecard visual, permissions, Power Automate integration (triggers and actions), …

• Read more about the Power BI Goals enhancements here – https://powerbi.microsoft.com/en-us/blog/more-easily-integrate-goals-into-all-your-business-processes/

Power BI Premium Gen2 is now GA – allowing for a more enterprise grade and scalable analytics.

The concept of Hybrid tables allows a dynamic combination of in-memory caches of historical data combined with real-time data queried in DirectQuery mode.

Power BI Hybrid tables – source – https://powerbi.microsoft.com/en-us/blog/ignite-2021-enabling-data-driven-decisions-everywhere-collaboration-happens-with-enterprise-grade-analytics/

Power BI integration in Azure Purview

Already featured in the Azure Purview launch event, the Power BI integration is now GA, allowing for a deeper integration and metadata scanning of your Power BI workspaces, datasets, reports and even dataset structures.

Power BI integration in Azure Purview – source: https://powerbi.microsoft.com/en-us/blog/ignite-2021-enabling-data-driven-decisions-everywhere-collaboration-happens-with-enterprise-grade-analytics/

• Power BI lineage integration in Azure Purview – https://powerbi.microsoft.com/en-us/blog/microsoft-power-bi-and-azure-purview-work-better-together-is-now-in-ga/
• Power BI and Azure Purview integration (video): https://www.youtube.com/watch?v=iue37PHUPLA&list=PLGdtUVeal1ELbPdRqNyNBb7-tR83Dp02Y&index=9

Summary

Although the Book of News section is not that impressive, a closer look at the new feature really makes me look forward to a bright future of Azure Data. One of my highlights are the announced Database templates and Lake database in Azure Synapse…

What is YOUR favorite feature?

DirectionsEMEA 2021 in Milano

Last week was a special one – I went to a conference, a real conference. 🙂

DirectionsEMEA 2021 took place at Milan from Wednesday 27th to Friday 29th October. It was a kind of unusual conference for me – No data focus, no majority of Azure data or Power BI sessions – it was a conference about Business Central (Dynamics). AND – I learned a lot. For me, now it is clearer what Business Central is capable of, how to get data into Business Central and how to get data out of BC to perform data analysis upon that data.

Right after the registration – Full mask mode during the conference

My Sessions: Power BI, Azure Synapse, PowerPlatform Dataflows

I had the chance to present two sessions about data anyway.

T-10 minutes before the first session

T-15 min before the PowerPlatform dataflows session

The first session was about the relationship of Azure Synapse Analytics and Power BI. During this session I compared the two tools and how they can be used in the flow of data – from the datasource to data visualization.

Session 1 – Power BI and Azure Synapse – better together?

The second session was about PowerPlatform dataflows. I described the concept of dataflows, why they are needed and why they have been introduced. Next I compared the possibilities of Power BI dataflows versus PowerPlatform dataflows.

Session 2 – What are the possibilities of PowerPlatform dataflows?

What I really liked at the conference was the corona-save health procedures – temperature check plus check of the “green pass” everytime you entered the conference center. Plus mandatory wearing a mask anytime within a building..

Some Impressions

Keynote on day one

SQLDays 2021 in Erding

Sie gehören zum Herbst dazu – die SQLDays der PPEDV in Erding. Traditionell in der Stadthalle in Erding und heuer auch endlich wieder in-person oder genauer gesagt als hybride Veranstaltung.

Ein Teil der Teilnehmer war direkt vor Ort dabei und der Rest konnte per Teams-Meeting bei den Sessions dabei sein.

Nach der 3G Kontrolle am Eingang stand dann dem Vortragsvergnügen (hoffe ich mal) nichts mehr im Wege.

Ich war heuer mit meinen aktuellen Lieblingsthemen unterwegs

• Azure Purview sowie
• Azure Synapse und Power BI

Ein paar Impressionen von der Veranstaltung

Azure Purview – Azure Data Governance Event

Today, the Azure Data Governance event happened. As you might have noticed, I’ve focused on Azure Purview in the last few months and I really looked forward to this event..

So what happened? What was announced?

Rohan Kumar started the event and talked about the importance of data in enterprises and how Data Governance can help to increase the value of data.

Purview is GA (General Available)

I kind of expected it – Azure Purview is General Available as of today.

Read more about it in the announcement blog post: https://azure.microsoft.com/en-us/blog/govern-your-data-wherever-it-resides-with-azure-purview/

The Azure Purview platform

Afterwards, Mike Flasko took over and went into the more detailed part of the presentation focusing on the Azure Purview platform

Purview Demo

The Azure Purview demo was presented by Evangeline White from the Azure Data Governance team. She showed some interesting stuff like – SQL stored procedure support and the enhanced metadata lineage extraction for Power BI.

Feature summary

The feature overview concluded the event:

But what about the future of Azure Purview / Azure Data Governance?

There was one slide shown, that included some interesting roadmap steps ahead of us..

And hey Wolfi – What about the costs?

Well, the discussion of Purview costs is a long story (blog post) but the pricing page is now updated and there is still some time for you to test it for free (until Nov 1st, 2021)

Some links:

• Azure Purview documentation: https://docs.microsoft.com/en-us/azure/purview
• The Purview GA blog post: https://azure.microsoft.com/en-us/blog/govern-your-data-wherever-it-resides-with-azure-purview/
• Azure Purview pricing: https://azure.microsoft.com/en-us/pricing/details/azure-purview/
• Some Purview blog post: https://workingondata.wordpress.com/category/azure-purview/
• You want to start with Purview today? Have a look at my Purview Quickstart video series… https://workingondata.wordpress.com/2021/08/31/azure-purview-quickstart-series/

Why can’t I move my Azure Purview sources between collections?

Yesterday, Andy Cutler (t) asked me on twitter, if the move of a registered source between collections is possible.

Andys question

My first answer was, yes sure… And I also included a screenshot from one of my Purview accounts (which – this will be important in the following – is a legacy Purview account).

Move a source between collections in Azure Purview (legacy Purview account)

But – tech isn’t tech without some differentiation between versions and available options 😉

The new Purview collection management (mentioned here) introduced in August 2021 allows for a finer granular permission definition and other things, but some of the features are still missing: like the move and delete of sources in the data map.

If you are using a new (created after August 18th, 2021) Purview account, the move of sources between collections is not possible (as of today, 2021-09-16)

Sorry @Andy – we’ll hopefully do not have to wait tooo long for the move feature to be available in new Purview accounts.

I would like to say – Happy source-moving.. but we’ll have to wait some time for that,

Wolfgang

Azure Purview: Where are my Assets classified listed?

I got a question from a customer this week, that Azure Purview does not list the Assets classified in the scan history anymore but classifications happen.

Hmm.. What happened?

Remark: All the screenshots were taken in a legacy Purview account (Azure Purview – Access Control and Collection Management).

When I open the scan history list, the list of columns indeed does not contain the Assets classified column.

What is for sure available for a longer period in the UI – the Edit columns action is responsible for the missing columns. This dialog allows you to customize the display columns – et voila – the missing information is listed there.

I also spotted some (for me) new information pieces like Relationships ingested, … which I will for sure have a closer look in the future.

Happy data scanning,

Wolfgang

Unable to create Azure Purview Account – Resource provider Microsoft.Purview is not registered

In my last Azure Purview Quickstart video (#3 – Create an Azure Purview Account – link), I’ve shown you how to create a new Azure Purview account.

And what pre-prepared demos have in common, well – it “just” works there 😉
BUT: there are some requirements that need to be configured beforehand, in order to create an Azure Purview Account.

Basically, problems during the creation process can be listed to:

• Security / permissions
• Missing Resource providers

Permissions required to create an Azure Purview Account

You need Azure Contributor or Owner role (or be an Administrator of the Azure subscription)

Missing Resource providers

Azure Purview requires the following resource providers to be registered in your Azure subscription. If you have not heard of Azure resource providers before, please have a look at the docs page.

Azure Purview requires the resources providers for

• Microsoft.Purview
• Microsoft.Storage
• Microsoft.EventHub

to be activated in your subscription…

The validation error message looks similar to this one:

Validation failed with error: The template deployment ‘Microsoft.AzurePurviewGalleryPackage-datagov01’ is not valid according to the validation procedure. The tracking id is ‘xxxxxxxx’. See inner errors for details.
Detailed error(s):
21005 – The resource providers Microsoft.Storage and Microsoft.EventHub and Microsoft.Purview are not registered for subscription xxxxxx. For improved Azure Policy validation on managed resources, we recommend to register Microsoft.PolicyInsights resource provider on your subscription.

The resource providers Microsoft.Storage and Microsoft.EventHub and Microsoft.Purview are not registered for subscription xxxx

Missing resource providers (Microsoft.Purview) for Purview Account

How to register missing resource providers?

• First of all – you need administrative access to your Azure subscription to enable resource providers.

• Open the Azure portal and head over to Subscriptions

Azure subscription list

Select the Resource providers menu entry and search for the three required ones (Microsoft.Purview, Microsoft.Storage, Microsoft.EventHub)

Resource provider menu in the subscription section

(1) Search for the resource provider name, select (2) the entry and (3) register / re-register it in your subscription.

Resource provider details page

Reminder: check for all three resource providers to be registered! 😉

With these configuration steps, you now should be able to create your Azure Purview account!

Happy data cataloguing,

Wolfgang