Dataverse has its pros and cons but combined with Power Apps and the No-Code capabilities of the platform, it can solve many business problems without building complicated apps and databases.
Now that Power Platform has evolved with Fabric to process the data using state-of-the-art tooling, it's more important to know how you can connect to your Dataverse tables from Fabric. You can bring your data from Dataverse into Fabric Lakehouses & Warehouses, and Microsoft is about to make it much easier with a brand new Synapse Link connector for Fabric.
Let's dive into it.
1. Synapse Link for Dataverse for Fabric
Synapse Link for Dataverse has been an ultimate game changer, whether you push your Dataverse data into ADLS Gen2 or Synapse Analytics Workspace. Using it with the Synapse Workspace is the best option, as you don't need to worry about the Common Data Model schema when building your Lake Database Tables. You get the "partitioned" snapshot view of the data and the real-time updates.
Now, Microsoft is preparing to release the Synapse Link for Dataverse for the Fabric platform. With the upcoming September 2023 release, Synapse Link will start supporting creating tables in Fabric Workspaces and Lakehouses as it does with Synapse Workspace
Check out Microsoft's release plan for this connector:
We can bring the snapshot or "append-only" datasets into our Workspaces and start processing them with Notebooks and Dataflows. Or, if you're in a hurry, you can use Power BI's Direct Lake functionality to connect to your Dataverse tables and create your reports.
When released, this is how the connection will look like:
Pros
- Direct connectivity from Fabric to Dataverse
- Real-time and snapshot data available to query across Workspaces
- Best way to connect and analyse your Dataverse data
Cons
- Not yet released; it'll go into Preview in September 2023 update
- Further limitations will be unveiled after the release
Subscribe to TBC Weekly Newsletter!
Receive articles, tips and tricks from us and the Fabric community
Subscribe2. Push from Synapse into Fabric Lakehouse
If you already have a Synapse Workspace connected to your Dataverse, you can start pushing your data into your Fabric Lakehouse from Synapse Pipelines or Notebooks. As we've covered in the "Adopting Fabric" article, OneLake supports ADLS Gen2 endpoints, allowing you to make connections from Synapse and everywhere.
The best approach here is to treat your Synapse Workspace as your Landing area. It's read-only data in Tables, backed by CSV files in the ADLS Gen2, so it doesn't exactly fit into your Bronze area.
You can target the Delta files in OneLake as your Sink in your pipelines, executing the Merge command on your Synapse Dataflows, making OneLake your Bronze and Silver layers in Medallion Architecture. You can also achieve the same result using the Notebooks, allowing you to run MERGE statements in your SQL cells or with Delta commands in Scala & Python.
This approach is also your best bet until the Fabric connector for Synapse Link is released into GA. It would keep your costs at a minimum and allow you to utilise the Serverless SQL of Synapse Workspace if you needed to access your data anywhere else.
Pros
- A well-established pathway
- Easy to manage and configure
Cons
- Fabric connector for Synapse Link is underway. If you don't already have Synapse Link to your Workspace, waiting for the Fabric support is better.
- Requires management and configuration of Synapse Workspace environments
3. Pull from Synapse Serverless SQL into Fabric Lakehouse
Another viable option is if you have an existing Synapse Link to a Synapse Workspace or can't wait until Fabric support is in GA. Suppose you want to avoid further investing in Synapse Pipelines or Notebooks. In that case, you can pull the data from Synapse's Serverless SQL Endpoint and push it into the Bronze area in your Fabric Lakehouse.
This way, you can avoid dealing with the Delta files on OneLake and directly talk to the tables in the Lakehouse.
Pros
- A well-established pathway
- Easy to manage and configure
Cons
- Fabric connector for Synapse Link is underway. If you don't already have Synapse Link to your Workspace, waiting for the Fabric support is better.
- Requires management and configuration of Synapse Workspace environments
4. Pull from Dataverse via OData APIs in Fabric Lakehouse
This is an existing option you had with Power BI Dataflows and now with Fabric, using the Dataverse connector. Under the hood, it talks to the Dataverse using its OData APIs. Beware, though, this connector would connect you to the transactional data directly, not to an analytics copy. This option should work fine if you're fetching a small dataset or need to write back to Dataverse. But anything more extensive than that, I would recommend something other than this approach.
Additionally, Microsoft imposes throttling rules per connecting user on OData APIs. You can make up to 6000 requests within a 5-minute sliding window, which roughly equals 20 req/sec. If you're fetching a lot of data, it could be a problem. You can find more information on the limits here.
Pros
- Direct connection to Dataverse
- Works great in small datasets or when you need transactional data
Cons
- Very slow in large data operations
- Throttling limits are strict
- Not an analytics copy, OData isn't flexible enough to cover most query scenarios
Conclusion
The best approach for you will depend on when Fabric will be Generally Available and when you want to adopt Fabric in your organisation. But, if you're eager to get value from Fabric (like me), then with the Synapse Link connector on the horizon, it's best to wait for the Fabric support to arrive.
If you have an existing Synapse Workspace investment or can't adopt Fabric fast enough, you can use the options above to connect to your Dataverse data. You should be able to move onto Fabric relatively quickly in the future.
What are your thoughts on Synapse Link for Fabric? Please share with us in the comments!
