PowerObjects Blog 

for Microsoft Business Applications


Dataverse Synapse Link to Data Lake Storage integrated with Power BI Desktop and Dataflows

Post Author: Joe D365 |

You may have seen something new when looking at Power Apps Data sub menus: an “Azure Synapse Link” sitting in between Dataflows and Connections. This new option can be a valuable Business Intelligence Catalyst. What this Azure Synapse Link does is duplicate selected data from in this case Dynamics 365 Marketing Dynamics Dataverse into a Data Lake. Azure Data Lake Storage lets you collaborate with people in your organization using Power BI, Azure Data, and AI services, or using custom-built Line of Business Applications that read data from the lake. Dataflows that load data to an Azure Data Lake Storage account store data in Common Data Model folders. Common Data Model folders (In Preview) contain schematized data and metadata in a standardized format, to facilitate data exchange and to enable full interoperability across services that produce or consume data stored in an organization’s Azure Data Lake Storage account as the shared storage.

Data Lakes automatically keeps the Data close to real time refreshed using a “trickle” flow. Data in the Data Lake is easy and extremely fast to connect to and synchronize with. In this blogpost, we highlight connecting to Power BI Desktop and Dataflows. Data connections and refresh time are much faster than the standard Dataverse connectors, but keep in mind some of what we are showing here is in preview (CDM Folder View) and we would not recommend for Production until formally released.

Setting up a Data Lake

A fast and effortless way to set up the Data Lake is via setting up a new Azure Synapse Analytics Workspace. When setting up the Synapse Workspace the System will walk you through the steps to create the Subscription, Resources Groups, Synapse Workspace, and the Storage Account (Data Lake). You may have to provide a credit card to get a Trial of Azure but there were no charges we incurred for using Data Lake Storage across a few trials’ worth of demo data.

Steps:

  1. Go to https://portal.azure.com/
  2. Select Data / Azure Synapse Analytics
  3. Click Create

The page below will walk you through the steps to set up the Resource Groups, the Synapse Workspace, and Data Lake Storage 2 that we will be connecting to with Power BI.

Once the Data Lake is set up, you can then Link the Dataverse Environment to the Data Lake from Make.Powerapps.com via Data/Azure Synapse Link. We noticed that the Source Dataverse and the Target Data Lake had to be in the same geo environment, so for locale we set up an East and West Data Lake to cover all the Locations our Dataverse environments were located in.

Two uncomplicated ways to connect to Data Lake Storage:

  1. Power BI Desktop: One way to connect to the Data Lake is to select Azure / Azure Data Lake Storage Gen2 from Power BI Desktop.

2. Power Apps Dataflows: Another approach is to create a Dataflow from Make.Powerapps.com. Using dataflows with Microsoft Power Platform makes data preparation easier, and lets you reuse your data preparation work in subsequent reports, apps, and models. Dataflows enable developers in your organization to integrate dataflow data into internal applications and line-of-business solutions, using developer resources for dataflows and Azure. Data ingested through dataflows is now available to data engineers and data scientists to leverage the full power of Azure Data Services, such as Azure Machine Learning, Azure Databricks, and Azure SQL Data Warehouse for advanced analytics and AI. This enables business analysts, data engineers, and data scientists to collaborate on the same data within their organization.

Steps:

  1. Go to https://make.powerapps.com
  2. Select Data / Dataflow
  3. Select New Data Flow
  4. Start from Blank
  5. Name the Dataflow
  6. Select data Source Azure Data Lake Storage Gen2

When connecting to the Data Lake from either the Power BI Desktop or Service, use the “dfs” endpoint for the URL value. We found the value in the JSON of the Data Lake in Azure:

  1. Go to https://portal.azure.com/
  2. Select Data Lake (Storage Account)
  3. Click on JSON View
  4. Copy the “dfs” URL

Enter in the URL highlighted and then select CDM Folder View (Beta).

Once connected, you can select the folders for which environments and select which tables to import. You can also Transform the data and further Filter and develop the data model.

In summary, we share several high-level value propositions for exploring Data Lakes and Dataflows integrated with Dataverse environments

  • Azure Data Lake Storage Gen2 provides an enormously scalable storage facility for data.
  • Dataflow data and definition files can be leveraged by your IT department's developers to leverage Azure data and artificial intelligence (AI) services as demonstrated in the GitHub samples from Azure data services.
  • It enables developers in your organization to integrate dataflow data into internal applications and line-of-business solutions, using developer resources for dataflows and Azure.
  • Power Platform dataflows store data in Dataverse or Azure Data Lake Storage—which means that data ingested through dataflows is now available to data engineers and data scientists to leverage the full power of Azure Data Services, such as Azure Machine Learning, Azure Databricks, and Azure SQL Data Warehouse for advanced analytics and AI. This enables business analysts, data engineers, and data scientists to collaborate on the same data within their organization.

We find the Data Lake Storage quite easy to set up using the Synapse Workspace Setup. And we are seeing fast connection and refresh times when comparing to direct Dataverse connections.

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

PowerObjects Recommends