Access large amount of data by using a Dataverse Virtual table

(1) Share

Report

Posted on by Elowy GrootCRM

153

Hello everyone,

We’re looking to integrate hourly metering data (8 columns) from our 15,000 metering units into Dynamics. These metering units are already stored in Dataverse, and we want to make their hourly data accessible within Dynamics as well.

A quick estimate suggests that after three years, we’ll be handling around 400 million records. Given this scale, we’re considering using an external database and access to the data by using a virtual table in Dataverse. Does anyone have experience accessing such large datasets in Dynamics with virtual tables?

Our key requirements:

Hourly metering data should be accessible per metering unit within a subgrid in Dynamics and displayed in a graph.
The same visualization should be available in Power Portal.
We need alerts when data exceeds predefined limits.

For now, we’ve chosen Cosmos DB, but we’re open to switching to SQL Server or PostgreSQL based on functionality. We’re also able to store the metering unit Dataverse GUID alongside the metering data for referencing.

Would a NoSQL approach suite better for this scale, or should we opt for a relational database?
And will we able to meet the requirements when choosing for a virtual table and having this amount of data?

Any insights or recommendations would be greatly appreciated!

Thanks in advance for your help!

Best regards,
Elowy

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Michael E. Gernaey 53,335 Super User 2025 Season 2 on at

Like (2)

Report

Hi @Elowy GrootCRM

I would need more details related to what data exactly you need (meta-data, rollups, literally looking at 400million rows etc).

with something like this, I would initially think the following

1. data lake + power bi + dashboard/reports + Embed in Dynamics

2. cosmo's + jobs to summarize + whatever metadata you need + the rollup data values as a Virtual Table

3. Azure SQL database (but... for me I don't believe these items are Transactional so you should NOT store them in SQL, but I am adding this)

4. DataBricks + Frabric

This really would be more of a bigger consulting discussion. trust me when I say, its not easy to build a massive scale, high input, reprocessing (constantly) engine just with the data above.

Understanding Access Patterns

Volume / Velocity of incoming data

Volume / Velocity of Processing of the data

Volume / Velocity of access the data (# of users) reports etc

This isn't an IoT system exactly but I would expect that the data comes in, needs to be indexed (this is a general term of organizing), then the data that needs to be visible to the users, ETL, Governance, Obfuscation, you name it.

Now you need to talk about Row level, column level /field level security etc.

Was this reply helpful? Yes No
Elowy GrootCRM 153 on at

Like (0)

Report
Hi Michael,

Thank you so much for your insights! I really appreciate the time you took to respond to my post.

Here’s a more detailed breakdown of the metrics:

2,000 data points per minute (15k devices × 8 data points, hourly, just 3 columns per row), continuous flow

No updates or deletions

Nature of data: Similar to IoT data, non-transactional

Security: Organizational data, no row- or column-level security concerns

Visualization and reporting needs: About 50 times a day, users will request a visual representation, such as:

Aggregated daily metering data for the last month

Metering data for the past 24 hours

Using Power BI directly on the source database for visualization seems like a great suggestion, potentially removing the need for Dataverse altogether.

Additionally, creating rollup tables sounds like a great approach to optimize queries.

Thanks again for your valuable input!

Kind regards,
Elowy

Was this reply helpful? Yes No