Microsoft Fabric & Power Platform - How to get your Dataverse Data into Fabric / OneLake

Introduction

Microsoft Fabric is top of mind everywhere, and its promises for improving the analytic capabilities of Power BI users are huge.

Similarly, Power Automate is already a powerful tool in combination with Data Factory, and will arguably play a major role in reaping the benefits of the future Data Activator tool in Fabric.

However, announcements about integrations and compatibility between Fabric and the wider Power Platform (e.g. Dataverse, Apps) is sparse.

For starters: How can I leverage data generated via Power Apps, currently stored in Dataverse, in my Microsoft Fabric setup?

This guide will show you the step-by-step process of getting all your Dataverse Data synced up with your OneLake hosted Data Lakehouse, making it easy to integrate with the rest of your Fabric setup.

Solution: Azure Synapse Link + Microsoft Fabric Shortcut

Dataverse has long supported Azure Synapse Link for Dataverse, and while support for a similar link directly to Fabric is coming soon, for now, we are limited to using the old solution.

However, by leveraging the new Fabric OneLake Shortcuts, we can use the old Synapse Link for Dataverse to transport data into an Azure Data Lake Gen2, and subsequently the OneLake shortcut for ADLS Gen2 to make said data available in OneLake / Fabric.

The steps we need to take to make this happen are as follow:

1. Setup the necessary Azure Resources for the Synapse Link (Subscription, Resource Group, Storage Account/ADLS Gen2)
2. Prepare Dataverse Tables and establish Azure Synapse Link between the Tables and ADLS Gen2
3. Create Shortcut in your Fabric/OneLake Data Lakehouse to the Dataverse Tables

The following sections will take you through each step.

Step 1: Azure Resources

In this step we will create the following Azure Resources, and note down a couple of important links and values:

• A Subscription
• A Resource Group
• A Storage Account (ADLS Gen2) including its URL Endpoint.
• An App Registration with correct roles and permissions on the Storage Account (+ a Client ID & Secret for authentication).

Follow the steps below to create the resources:

• First head to portal.azure.com, and if you do not have a subscription already, create one and connect to a billing account.
• Second, create a Resource Group, linking it to your Subscription.
• Third, create a Storage Account, linking this to your Subscription and Storage Account. Also make sure to find the Advanced Options, and toggle on 'Enable Hierarchical Namespace'. This ensure that the Data Lake being created is Gen 2!

• Fourth, go to your Azure Active Directory, open App Registrations, and create a new registration, making sure "Accounts in this organizational directory only" is selected as supported account type.

• After creating the App Registration, open it, and from the Overview Page, write down the Directory (tenant) ID and the Application (client) ID for later use.

• Then go to Certificates & Secrets of the App Registration to create a new Secret for authentication. Write down the Secret Value, for use later on.
• Now go back to your Storage Account, and into 'Access Control (IAM)'. Here, add a new Role Assignment for your newly created App Registration, giving it the Roles "Storage Blob Data Contributor" and "Storage Blob Data Owner".
• And finally, open up the "Endpoints" menu of your Storage Account, and write down the Primary Endpoint for later use.

Step 2: Prepare Dataverse Tables and Create Azure Synapse Link

After creating all the necessary Azure Resources, we can prepare our Dataverse tables and setup the Azure Synapse Link.

First go to make.powerapps.com and identify the Tables which you want to sync to your Data Lake.

For each table, open up the table settings, go to properties, expand the Advanced Options, and make sure "Track Changes" has been enabled. If you do not do this, your table will not show up in the Synapse Link.

Now go to the Azure Synapse Link page, by selecting it from the navigation panel on the left hand side. If the option is not there, select More > Discover All and find the option in the bottom right side of the page under Data Management.

Create a New Link, and select the Subscription, Resource Group and Storage Account that you created during Step 1.

Next, Add the Tables which you want to sync to your Data Lake. Note that only tables which have "Track Changes" enabled will show up here.

And you should now be able to see an overview of your Synapse Link and the tables being synced to the Data Lake:

Similarly, you should be able to go to your Storage Account in Azure, find the Storage Browser, and see that the tables are now being synced and are available within your Data Lake.

As a final act in Step 2, please write down the string of the subfolder in which your Dataverse Tables have been uploaded. In my case, it is the "dataverse-60xtthdefault-unq......." string.

Step 3: Create Shortcut in your Fabric/OneLake Data Lakehouse to the Dataverse files in the Data Lake

Our final step on the journey, is to create a shortcut from our OneLake Data Lakehouse to the ADLS Gen2 folder containing our Dataverse data.

First, go to app.powerbi.com, and if you haven't already, create a Fabric-Enabled workspace, and create a Data Lakehouse within the Workspace. Microsoft has published an excellent guide to get you started on this.

Now open up the Data Lakehouse and click Files > New Shortcut

From here, choose Azure Data Lake Storage Gen2

In the Connection Settings, point the URL to the ADLS Gen2 'Primary Endpoint', which you wrote down as part of step 1.

For Connection Credentials, you may, depending on your settings, be able to connect with your Organizational Account, but otherwise select Service Principal as 'Authentication Kind', and fill in Tenant ID, Client ID and then your Secret Value as the Service Principal Key, all of which are values you wrote down during Step 1.

Finally, give the Shortcut an appropriate name, and then provide it with the Subfolder string which you wrote down as the very last thing in Step 2.

Congratulations!

You should now be able to use the Shortcut from the navigation menu of your Lakehouse, and query the copies of your Dataverse data directly from within your Notebooks.

Did you like the tutorial? Do you have any other cool ideas for integrating Power Platform and Fabric? Please reach out in the comments, or directly to me.