Shifting to Azure Synapse  · BGBS Portal

The Knowledge Export Service is an efficient resolution, however it comes with its personal fallbacks, particularly with massive clients. These fallbacks embrace failures and delays in synching at time limits, complexities in troubleshooting, the shortcoming to repeat configuration from one surroundings to the following, such that may be anticipated in an ALM course of.

Final 12 months, Microsoft launched another choice to the Knowledge Export Service. This new various known as Azure Synapse Hyperlink (or on the time Knowledge Lake) allowed the choice to simply export information out of your Microsoft Dataverse surroundings to an Azure Knowledge Lake Storage.

There isn’t a deprecation discover for the Knowledge Export service and that there are a number of Microsoft clients which might be nonetheless utilizing it, however it looks as if the path that Microsoft is pushing clients is to make use of Azure Synapse Hyperlink (previously Azure Knowledge Lake) so as to sync the information between their Microsoft Dataverse surroundings and an Azure information supply.

This Azure Knowledge Lake Storage (Technology 2) is an Azure providing that gives the power to retailer massive information from analytics functions. It’s based mostly on Azure Blob storage making it value efficient and strong. The subsequent few steps will present you learn how to configure the Azure Storage V2 account that’s required for organising the hyperlink between Microsoft Dataverse and Azure Synapse Hyperlink.

Step one is to login to the Azure Portal by navigating to, after which click on on the Create a useful resource hyperlink icon and search for Storage account. Click on on the create possibility for Storage account.

Choose a subscription and Useful resource group, enter a storage account, choose the area and you may depart the remainder of the settings as is. The screenshot beneath exhibits the primary tab of the Storage account creation

Create Resource - Azure Storage Account (Data Lake Gen 2) - Basics tab

Don’t click on on the Evaluate and create button on the backside of the display screen, however reasonably the Subsequent: Superior button to maneuver to the superior tab.

You’ll discover within the superior tab that there’s an part for Knowledge Lake Storage Gen 2. Verify the field to allow hierarchical namespace. The picture beneath exhibits this tab.

Create Resource - Azure Storage Account (Data Lake Gen 2) - Advanced tab

You’ll be able to skip the remainder of the tabs and click on on Evaluate + create to finalize the creation of the Storage account. As soon as the Azure Storage account is configured, we will go forward and begin configuring the Azure Synapse Hyperlink in our Dataverse surroundings.

Create Resource - Azure Storage Account (Data Lake Gen 2) - Deployment Complete

Navigate again to your maker portal, by going to Inside your occasion, increase the Knowledge menu, and click on on the Azure Synapse Hyperlink menu merchandise. This can open the New hyperlink to information lake panel, the place you may specify your Subscription, Useful resource Group and Storage account that you simply created to be used with Azure Synapse Hyperlink. The picture beneath exhibits this

Dataverse - Link to Data Link - Select Storage Account

You’ll discover that there’s additionally an choice to Connect with your Azure Synapse Analytics workspace, which is at present in preview. This enables us to convey the Dataverse information into Azure Synapse with just some clicks, visualize the information inside Azure Synapse Analytics workspace after which quickly begin processing the information to find insights utilizing options hyperlink serverless information lake exploration, code-free information integration, information flows for ETL pipelines and optimized Apache Spark for large information analytics.

Let’s return to our Azure portal and create the Azure Synapse Analytics in order that we will do that on the identical time inside database. In your Azure portal, click on on the create a useful resource hyperlink once more, and this time search of Azure Synapse Analytics.

Create Resource - Azure Synapse Analytics

This can open the Create Synapse workspace web page on the Fundamentals tab. Choose your subscription and Useful resource group. Additionally, you will need to enter a reputation for the managed useful resource group. A managed useful resource group is a container that holds ancillary sources created by Azure Synapse Analytics to your workspace. By default, a managed useful resource group is created for you when your workspace is created.

Enter the identify of the workspace and the area. The you’ll have to enter the identify of the Storage Gen2 that we beforehand created and the identify of the File System. When you wouldn’t have a file system identify, click on on the Create new hyperlink underneath it and supply a reputation, it will create the File system for you.

Create Azure Synapse Analytics - Basics tab

Don’t click on on the Evaluate + create, however on the Subsequent: Safety button. You’ll have to present a password to your Sql Server admin login.

Create Azure Synapse Analytics - Security  tab

Now you can click on on the Evaluate + create, after which the create buttons to create the synapse workspace. This course of takes a bit of longer then the creation of the storage account, as extra sources are being created. As soon as deployment is finished you may go to your new Azure Synapse Analytics useful resource by clicking on the Go to useful resource group button.

Create Azure Synapse Analytics - Deployment Complete

Let’s return to our Maker portal and choose the Connect with Azure Synapse Hyperlink once more, however this time we will even present the knowledge for the workspace.

Verify the Connect with your Azure Synapse Analytics workspace (preview), enter your Subscription, Useful resource group, Workspace identify and Storage account. Within the useful resource group just be sure you don’t choose the managed useful resource group, as that useful resource group doesn’t have the workspace and the storage account related to it.

Dataverse - Link to Data Link - Select Storage Account (with Workspace)

When you click on on the Subsequent button, you may choose the tables that you simply need to sync with Synapse Hyperlink. For this goal we are going to solely choose a couple of tables (account and call), however you may choose as many tables as you need.

Dataverse - Link to Data Link - Add Tables

Lastly, click on on the Save button. This can create the connections and help you begin synching and reviewing information in Synapse Hyperlink and Synapse Analytics. As soon as accomplished, the web page will refresh and you will notice the Linked information lake exhibiting the Storage account that you simply beforehand created.

Now let’s begin by going to Azure and see what occurred after we clicked on the Save button. In our record of containers you will notice varied containers, one in every of them containing the identify of your dataverse group. Clicking to that container will present you a folder for every of the tables that you simply chosen to your synchronization in addition to a mannequin.json file which comprises your schema for the entities that you simply chosen.

When you drill down into the chosen entities, you can find a single csv file containing the preliminary dump from Dataverse. You’ll be able to view and edit the file straight in Azure Blob or obtain it and open it in Excel. The info will comprise all of the fields which might be a part of the entity.

Azure Storage Account - Initial Sync results

As soon as we both add an extra report we are going to discover {that a} new file will get created comparable to the month of the creation. If the report already exists in our Azure Blob surroundings, a brand new file won’t be created, however it should modify the present report.

Azure Storage Account - Sync Results after new record created

When modifying exists data, the modified report will get up to date within the corresponding file the place it at present exists. In our case, based mostly on the report the adjustments will both be within the 2021-06 or 2021-07 file.

Now that we see that the recordsdata are created, let’s go forward and see how we will view this information in Energy BI. The file factor that we’re going to want is to get the Endpoint of the Knowledge Like storage. Inside your new Storage account, within the left navigation underneath settings, click on on Endpoints. Throughout the endpoints web page, underneath the Knowledge Lake Storage part, you will notice that there are a Main and Secondary endpoint Urls for Knowledge Lake Storage. Copy the Main endpoint url. This will likely be used inside Energy BI. That is proven within the picture beneath:

Azure Storage Account - Endpoints - Data Lake

Subsequent you’ll have to get the Entry key for the Knowledge Lake Storage Gen2. Within the Azure Storage Account, underneath the Safety + networking part, click on on Entry keys. This can present you the web page containing a listing of entry keys. Click on on the Present keys button, and the copy the worth of the primary key. You’ll need this for configuring Energy BI.

Azure Storage Account - Access Keys

In Energy BI, click on on the Get Knowledge button within the Ribbon, choose Azure for the supply of the information, and from the accessible Azure supply choose Azure Knowledge Lake Storage Gen2 as proven within the picture beneath:

Power BI - Get Data - Azure Data Lake Storage Gen2

Click on on the Join button, and within the new pop up window, enter the Knowledge Lake Storage Main endpoint url that you simply copied within the earlier step and paste it on this window, choose the CDM Folder view Beta after which click on OK.

Power BI - Get Data - Url and Data View (CDM Folde View)

Within the subsequent window, you’ve got the choice to sign up utilizing an Organizational account or the account key. Click on on the account key. That is the entry key that you simply copied from the earlier step. After you enter the entry key, you will notice the next window, with the accessible information supply outcomes.

You’ll then see the navigator which gives you with a view of the mannequin that you’ve in Azure Knowledge Lake. Broaden the Storage Container, after which increase the cdm hive. This can present you the record of entities that you’ve accessible there as tables. See the screenshot beneath.

Power BI - Get Data - Navigator

Lastly from Energy BI, you can begin including visualizations or fields, and customise your information as wanted. Within the screenshot beneath, we add a couple of fields from the accounts desk within the Azure Storage account.

After we configured Azure Synapse hyperlink we checked the field for making a Synapse workspace as properly. If we navigate to the Synapse workspace that we created, we will question the information from our Azure Storage account container from throughout the Synapse Analytics workspace. There are a number of configuration choices which might be accessible in Azure Synapse Analytics workspace, equivalent to configuring Analytics swimming pools, encryption and firewall configuration and extra. These may be additional reviewed within the Microsoft documentation, however for our goal, we’re going to check out the Synapse studio. The picture beneath exhibits the Azure Synapse Workspace overview web page, the place we will click on on the Open Synapse Studio to start out querying our analytics.

Azure Synapse Analytics - Overview page

When Synapse Analytics Studio opens, there are a number of accessible hyperlinks on learn how to use this Azure product, and is likely to be overwhelming, however we’re simply going to overview the fundamentals on learn how to retrieve or question information from the information warehouse. There are a couple of choices that you need to use to create a SQL Script in opposition to Synapse Analytics. You’ll be able to click on on the New button on the highest of the web page, and select SQL script

Azure Synapse Analytics Studio - New SQL Script from Home Page

You’ll be able to click on on the Knowledge icon on the left navigation, underneath the House icon, which can help you increase into the information construction of the information retailer, and from there click on on the account desk, select New SQL script after which select the Choose TOP 100 rows. This can create the SQL script for you and you can begin enhancing it from there.

Azure Synapse Analytics Studio - New SQL Script from Account Table Data

The final possibility is clicking on the Develop icon within the left navigation, then clicking on the + button and choosing SQL Script as proven beneath:

Azure Synapse Analytics Studio - New SQL Script from Develop page

As soon as we’ve got chosen the question possibility that we need to use, we will go forward and construct our question. In our case we’re simply going to retrieve the record of accounts as proven within the screenshot beneath:

Azure Synapse Analytics - Create Query for Account table

After we click on on the Run button we will see the outcomes as proven beneath.

Azure Synapse Analytics - Create Query Results for Account table

There are much more prospects and choices which might be accessible for learn how to use Azure Synapse Hyperlink and Azure Synapse Analytics and accessing the information from totally different sources. This articles gives the fundamental overview on learn how to configure them and make them work to your Dataverse surroundings. I hope this was useful.


Please enter your comment!
Please enter your name here