4 ways to access data from Microsoft Fabric OneLake in Dataverse / Power Platform solutions
Introduction
I have written about accessing Dataverse Data from Microsoft Fabric, outlining how it is possible using:
Azure Synapse Link for Dataverse
Dataverse Link for Fabric
Dataflows / Dataflows Gen2
Power BI semantic models created in Power BI Desktop
But what if we wanted to go the other way around? What if we wanted to use data stored in OneLake as part of a Power Platform Solution?
There are several potential solutions to this, and this article will outline four different options, each with their pros and cons:
Using Virtual Tables in Dataverse to reference OneLake data
Using Dataflows in Dataverse to copy OneLake data
Using Data Pipelines in Microsoft Fabric to copy OneLake data to Dataverse
Using direct connectors in Power Apps / Power Automate to access OneLake data
Using Virtual Tables in Dataverse to copy OneLake data
The first option for bringing Fabric / OneLake data into Dataverse and your Power Platform solutions, is to reference the data using Dataverse External Tables, to query Fabric Lakehouses.
First, go to the Tables section of the Dataverse Environment that should access the data:
Create a New Table, select Virtual Table, and create a new Microsoft Fabric connection if you have not used the connection before (it will ask you to authenticate with your Windows Credentials):
Then select from the list of available workspaces in Fabric, and point to the Lakehouse that holds your tables:
Select the table you wish to query:
Configure Primary Key column (your table must have a unique column that can be used for this), and save the table:
The result is a Virtual Table in Dataverse which can be imported to your Power Platform solutions like any other Dataverse table:
Pros:
- Data is not replicated, but queried.
Cons:
Warehouses are not supported.
Tables must have a unique column to be used as Primary Key.
A general list of Virtual Table considerations and limitations can be found in the Microsoft Documentation: Limitations and troubleshooting virtual tables with Power Apps — Power Apps | Microsoft Learn
Using Dataflows in Dataverse to copy OneLake data
Another option is to use Dataflows in Dataverse to ingest data from Fabric Lakehouses and Warehouses.
First create a new dataflow:
Choose SQL Server as your data source, and input the Connection String of your Fabric Warehouse or Lakehouse in the Server details (you can get this from inside Microsoft Fabric), and write the exact name of the Warehouse or Lakehouse as the Database name:
Now select the table you wish to import, apply any transformations and map the output to the destination table in Dataverse:
Publish the dataflow, and way for the refresh to go through. Now you can use the data in your Power Platform solutions just like any other Dataverse table!
Pros:
Allows transformation of data before landing in Dataverse
Supports both Warehouses and Lakehouses in Fabric
Cons:
Data is replicated and will take up storage in both Fabric and Dataverse
Data is not live and depends on refresh schedule
Using Data Pipelines in Microsoft Fabric to copy OneLake data to Dataverse
The third way is to use a Copy Activity in a Microsoft Fabric Data Pipeline to copy data from a Lakehouse or Warehouse in Fabric into a table in Dataverse.
In order for this integration to work, you need to first setup a Service Principal, and grant it access to your Dataverse Environment, so that your Data Pipeline can use it for authentication.
Step 1: Creating the Service Principal in Azure
First, go to portal.azure.com, search for ‘App Registrations’, and create a new Registration (this is your Service Principal / SPN).
Provide it a name, just stick with the default Account Type, and hit create.
After creation, go to the Overview Page of your App Registration, and note down the Application/Client ID and the Tenant ID. You will need those later.
Then jump to the API Permissions page, and add a new Permission.
Select / Search for Dynamics CRM, select the ‘user_impersonation’ permission and add it to your permissions.
Now go to Certificates and Secrets, and create a new Secret. Note down the Key in the same place as your Application ID and Tenant ID, as you will need this later as well. Note that once you exit the screen with the Key displayed, you will not be able to view the key again, and will have to create a new one to continue setup.
Step 2: Adding the Service Principal to your Power Platform Environment
Next, we need to grant the Service Principal access to the Power Platform Environment that we need it to authenticate against.
Go to the Power Platform admin center (microsoft.com), and under Environments, select the one you intend to use. From this screen, copy the Environment URL and store it with the IDs and Keys you copied from Azure. You will also need this later.
Under Access, select the button under ‘Users’, and on the Users page find the button to go to the App Users List.
In here, create a new app user, and select the Service Principal you created in Azure.
Assign it the required Security Roles. You may use custom roles, or for testing purposes you may select the System Customizer role, as it will ensure it has the necessary privileges.
Step 3: Creating the Pipeline and setting up the Copy Activity
Finally its time to setup the actual data copy. Create a new Data Pipeline in a workspace of your choice.
Add a Copy data Activity, and configure its source to the table you wish to copy into Dataverse.
Setup the destination of the Activity to point to a Dataverse Destination by choosing External, and selecting Dataverse.
While creating the connection, you will need all the four values we copied during Steps 1 and 2.
Insert your Environment URL as the Environment Domain.
Insert your Tenant ID as the Tenant ID
Insert the Application / Client ID as the Service Principal Client ID
Insert the Secret Key as the Service Principal Key
Select the destination table in Dataverse you wish to write to. If it is not created already, you will need to jump into Dataverse and create that now.
After pointing to the table, feel free to Preview the Data and test the connection, as well as selecting which Write Behaviour you wish, under Advanced options.
Finally, we need to map the Source Columns against the Destination Columns, under the Mapping tab.
Selecting Import Schema will attempt to automatically do this for you, but you may remove any columns you do not wish to include in the copy, as well as do any manual adjustments.
Note, that in case your table includes complex columns like LookUp columns, you may need to do some additional steps. In this case, I recommend following Scott Sewells excellent video on the topic: Fabric Pipelines for Dataverse Part 5: Populate the Lookups (youtube.com)
After mapping the activity, you can Validate, Save and Run your pipeline, and hopefully it succeeds:
With the data showing up in your Dataverse Table, as having been Created/Modified by your SPN:
Pros:
May be easily included with the rest of your Fabric Data Pipelines.
Good Performance.
Cons:
Requires you to enter Azure to configure SPN — which not everybody has access to.
May be difficult to setup if your table includes complex columns, e.g. LookUp columns.
Using direct connectors in Power Apps / Power Automate to access OneLake data
The last option is to skip the step of populating a Table in Dataverse which the previous methods take advantage of, and instead connect directly to the SQL endpoint of your Fabric Warehouse and Lakehouse from within Power Apps / Power Automate.
In Power Apps, simply add a SQL Source Connection:
And point it towards the same SQL Connection string + database name as mentioned in the Dataflows option above, before choosing the tables you wish to add:
The data can now be used in Power Apps controls, but do note that Power Apps does not allow you to write data directly to a Fabric Data Warehouse, despite the endpoint being both Read and Write enabled. This is due to the lack of enforced primary keys in the Warehouse. (Read more in my blog about write-back to Microsoft Fabric: https://downhill-data.com/2024/05/07/live-data-write-back-to-fabric-data-warehouse-from-power-bi-w-power-apps-and-power-automate/):
In Power Automate its a similar story. Simply add an action that leverages the SQL connector, and connect using the Server connection string and Database name. Here I am using my Power Automate write-back workaround by using the SQL connector in Power Automate against a Fabric Warehouse:
Pros:
Data is always queried live from the Fabric data source.
Data is not replicated.
Supports both Warehouses and Lakehouses in Fabric
Cons:
Power Apps functionality is limited due to lack of enforced Primary Keys (while Power Automate has no issues with that).
Data never lands in Dataverse, making you unable to leverage Dataverse for any additional functionality. You have to build any transformations/logic locally in the solution you are developing.
Summary
I hope this post has given you inspiration for different ways to access data from Fabric and OneLake in your Power Platform solutions.
Here I outlined four possibilities, and I am sure that there are more out there. Do you think I missed an obvious one? Please drop me a comment or message — I would love to hear from you.
Top comments (0)