oData Query limits

(0) Share

Report

Posted on by Samkh

Hi.

I have to build reports on Power BI using oData, where the number of records to be fetched is more than 2 million. For now I am getting error because of the volume. What are my options? Thanks Sam

I have the same question (0)

All responses (7)

Answers (3)

Sunil Lakkireddy on at

Like (0)

Report

RE: oData Query limits

Using OData service is one of the way to connect to Dynamics365 Fin & Ops. However, especially for use with Power BI reports, it is not recommended with large data volumes because with the time when the data volume increases this may cause refreshes/reports to timeout. This is officially documented in the following doc:

Choose a data integration (import/export) strategy
docs.microsoft.com/.../integration-overview

Ref: docs.microsoft.com/.../integration-overview

Recommendation would be to use any of the below method for better performance:

I.
The recommended and supported approach for creating Power BI reports which will consume data from D365FO is DirectQuery connecting to an Azure SQL/SQL server database. This is documented in details in the following doc:

Power BI integration with Entity store
docs.microsoft.com/.../power-bi-integration-entity-store

Data accessible in this approach are sourced from AXDW database which is equivalent to Entity Store in D365FO which holds data populated to it through aggregate measurements.

The development process of Power BI reports which use DirectQuery is documented in detail in the following blog post of one of our Product Managers:

Power BI content from Microsoft and your partners
blogs.msdn.microsoft.com/.../power-bi-content-from-microsoft-and-your-partners

Developers should develop the reports against a copy of the AXDW database stored locally (on a customer hosted development box, for example).

Only DirectQuery data sources are supported, no Import. Not supported is also DirectQuery to the production database AXDB because external access to this database is restricted.

II.
Other options customers have is to bring data to their own storage.

Currently 2 options are available:

1) Own database

Bring your own database (BYOD)
docs.microsoft.com/.../export-entities-to-your-own-database

Here Data management entities (source: AXDB) can be (incrementally) exported to a customer’s own database.

2) Data Lake storage in Azure

Make Entity store available as a Data Lake
docs.microsoft.com/.../entity-store-data-lake

Here Aggregate measurements (source: AXDW) can be (incrementally) populated to a Data Lake storage owned by the customer.

The possibility for pushing data management entities to Data Lake as well is planned to be implemented in future releases. It is also planned to enable Tables in Data Lake.

Ref: docs.microsoft.com/.../planned-features

I hope this information helps in planning your solutions.

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,421 on at

Like (0)

Report

RE: oData Query limits

Only for Development environments, you can use local SQL DB.

For sandbox/ production environment, you will need to use Azure SQL only.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

RE: oData Query limits

Hi Sam,

On-premise MSSQL server should work as well.

Was this reply helpful? Yes No
Verified answer

vinitgoyal2005 6,332 on at

Like (0)

Report

RE: oData Query limits

Hi,

You definitely cannot use OData for PowerBI reports as it is the slowest method. as suggested BYODW is the way to go. There is another option of Data lake which is in Preview. I am not sure what is the exact date for GA of this but ADL said to be better than BYODW and eventually retire the use of BYODW.

Was this reply helpful? Yes No
Samkh 6 on at

Like (0)

Report

RE: oData Query limits

Hi,

BYOD seems to be an Azure (in the cloud). Is there any equivalent process so to make it on premise?

Thanks

Sam

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,421 on at

Like (0)

Report

RE: oData Query limits

Sam,

The limit for oDATA is 10K records.

You can look at using BYOD instead for Power BI reports. Please look up -

docs.microsoft.com/.../export-entities-to-your-own-database

community.dynamics.com/.../powerbi-and-byod-with-microsoft-dynamics-365-finance-and-operations

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

RE: oData Query limits

Hi Sam,

Have you taken a look at BYOD? It's a common approach to export report data to external DB and use it for reporting purposes or source for DW solution.

docs.microsoft.com/.../export-entities-to-your-own-database

Was this reply helpful? Yes No