Power BI refresh fails for revenue management reports due to API throttling on large datasets

ERP Microsoft Dynamics 365
, , , , , , , ,
1

Our scheduled Power BI refreshes for revenue management reports are failing with throttling errors in D365 F&O 10.0.42. The reports pull contract revenue data, recognition schedules, and performance obligations - about 500k rows across multiple entities.

Error in Power BI service:


OData query failed: 429 Too Many Requests
Retry-After: 3600
Throttling limit exceeded for resource

Refreshes work fine when triggered manually during off-hours, but scheduled refreshes at 6 AM fail consistently. We need these reports ready by 8 AM for daily revenue reviews. The API throttling limits seem too restrictive for our reporting volume. How can we optimize the queries or adjust the refresh strategy to work within D365’s throttling constraints?

2

Query folding is critical here. Push your filters to the OData source using $filter parameters instead of loading full datasets. For 500k rows, you’re probably pulling several GB of data unnecessarily. Apply date filters at the source - if you only need current fiscal year revenue, filter it in the OData query. This reduces both data volume and API calls. Also consider using $select to retrieve only required columns. Your throttling issue is likely caused by volume, not just request count.

3

Nina’s incremental refresh suggestion is spot-on for your scenario. I’d add: implement query batching in your Power BI data flow. Instead of 8 separate queries hitting D365 simultaneously, stagger them with 2-3 minute delays using Power Query dependencies. This spreads the API load across time windows, reducing peak concurrent requests. Combine this with proper $filter and $select parameters, and you should stay within throttling limits even at 6 AM.

4

Let me provide a comprehensive solution addressing all three optimization areas:

API Throttling Limits in D365: D365 F&O implements multi-tier throttling: user-level (60 requests/minute), tenant-level (varies by license), and Azure APIM limits (typically 100 requests/minute per instance). Your 6 AM failures indicate collision with other scheduled processes. D365 priority throttling favors interactive users over service accounts during business hours (even early morning in some regions).

Incremental Refresh Configuration: This is your primary solution for 500k row datasets. In Power BI Desktop, configure incremental refresh:


RangeStart = #datetime(2023, 1, 1, 0, 0, 0)
RangeEnd = #datetime(2025, 12, 31, 23, 59, 59)
Archive: 2 years
Incremental: 7 days
Detect data changes: Yes (use ModifiedDateTime column)

Apply filter in Power Query:

`Table.SelectRows(Source, each [ModifiedDateTime] >= RangeStart and [ModifiedDateTime] < RangeEnd) This reduces daily refresh from 500k rows to approximately 5-10k rows (only recent changes), cutting OData calls by 98% and eliminating throttling issues entirely.

Query Optimization Strategy: Rewrite your 8 queries with proper OData parameters:


// Before: Full table load with Power BI filtering
RevenueSchedules (500k rows, no filters)

// After: Optimized OData query
RevenueSchedules?$filter=FiscalYear eq 2025 and Status eq 'Active'
&$select=ScheduleId,ContractId,RecognitionDate,Amount,Status
&$top=10000&$skip=0

Implement pagination for large result sets and use $expand judiciously - joining tables in OData is expensive. For your revenue reports, consider creating a denormalized data entity in D365 that combines RevenueSchedules, PerformanceObligations, and ContractDetails. This reduces 8 queries to 2-3 optimized calls.

Additional Optimizations:

  1. Refresh Timing: Shift to 5:15 AM to avoid 6 AM batch job window, or use 7:30 AM if morning review can wait
  2. Query Dependencies: Stagger queries using Power Query “Depends On” to serialize OData calls (prevents concurrent throttling)
  3. DirectQuery Consideration: For real-time data needs, use DirectQuery with aggregations for summary tables, Import mode with incremental refresh for detail tables
  4. Monitoring: Enable Power BI refresh failure alerts and track API response times in D365 LCS

Immediate Action Plan:

  1. Enable incremental refresh today (requires Power BI Premium/PPU)
  2. Add $filter parameters to all queries limiting to current fiscal year + 1 prior year
  3. Reschedule refresh to 5:15 AM
  4. Monitor for 1 week - you should see refresh times drop from 45+ minutes to under 10 minutes with zero throttling errors

If you don’t have Premium licensing for incremental refresh, implement query folding with date filters as interim solution - this alone should reduce data volume by 60-70% and resolve most throttling issues.

5

The 429 error with 3600 second retry suggests you’re hitting Azure API Management throttling limits, not D365 itself. These limits reset hourly. Your 6 AM refresh might be colliding with other integrations or overnight batch jobs. Try shifting your refresh to 5:30 AM or 7 AM. Also, how many queries does your report execute? Power BI makes separate OData calls for each table - with multiple revenue entities, you could be making 10-15 concurrent requests, which quickly exhausts the throttling bucket.