Answered

Dataverse as Data Warehouse?

(0) Share

Report

Posted on by landcrab2

Hi there,

Does anyone know if Dataverse is a good fit for use as a Data Warehouse to store financial data out of an ERP system and other data small to medium size business consider useful having in a Data Warehouse?

My main concern is performance, have seen other refer to timeout issues due to data volume when using the Power BI Dataverse connector, is there any throttling imposed on the Dataverse connector? Any other potential issues to be aware of?

This scenario would especially be aimed at small to medium size business with a small budget to spend.

Potential benefit would be that Power BI already has a Dataverse connector and Microsoft announced a plan for an equivalent Excel Power Query connector for Dataverse, which means companies can use both applications for any kind of reports they want to create.

One alternative appears to be using Azure Synapse Analytics as both Power BI and Excel already have a Power Query connector for that in place, but the cost of using Azure Synapse Analytics is likely significantly more then using Dataverse.

Keen to hear what someone with more experience then me has to say about this please.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

cchannon 4,702 Moderator on at

Like (7)

Report
Copy link

Link copied!
I suppose it depends on the size of the implementation, but generally, no, I would not recommend using Dataverse as a Data Warehouse. The primary reason for this is that the only access to the data is through the APIs which are constrained in multiple ways that could become an issue for you:
Query results are paginated, with a maximum size of 5000 rows per page
Requests from a single IP are monitored and throttled to mitigate exposure to Denial of Service attacks.
Ideally, for any reporting model at scale, you want to keep the compute where the data is so you don't need to move the entire dataset; just the results of the report. This matters a lot less if the dataset isn't huge, but for highly normalized data structures such as you see in ERP systems even modest reports can involve millions of rows across dozens of tables.
Depending on the cost tolerance of your customer, what you might consider is Azure Synapse Link. It allows you to replicate your database out to an Azure Data Lake where you can use any of a wide array of analytical solutions, including Power BI. This would at least eliminate the web api constraints, although it would introduce some complexity in that the replicated data gets partitioned into headerless csvs, so there is some reassembly work you would need to do there.

Was this reply helpful? Yes No
YnteJan 8 on at

Like (0)

Report
Copy link

Link copied!

I found this post, stating: "TDS has a result set size limit of 80 MB"
So that does not look like a real showstopper. Especially if you can also connect to Dataverse through the SQL connector.
If you use the Dataverse connector in Power BI, then this will be the connectionsource
= CommonDataService.Database("orgXXXXXXX.crm4.dynamics.com")

But you can also use SQL database cooenctor and then this will be the connection source
= Sql.Databases("orgxxxxxx.crm4.dynamics.com")

That might make a difference. Although, under the hood, they may be identical. Even with Dataverse connector I see queryfolding in place.

Create a Power BI report using the Dataverse connector - Power Apps | Microsoft Learn

Was this reply helpful? Yes No