Forum Discussion
Power bi Refresh Failure
**Title: Scheduled refresh timing out (5hr XMLA limit) — ServiceNow REST API via Web.Contents, no query folding, incremental refresh not viable**
Hi all,
I'm hitting a wall with a dataset that pulls from ServiceNow via the Table API and would appreciate any guidance from people who've dealt with non-foldable REST sources at scale.
**Setup**
- Source: ServiceNow Table API (`/api/now/table/incident`, `change_request`, `problem`, `sc_req_item`, `task`, and a custom `u_change_deployment` table) — 6 tables total in one semantic model
- Connector: `Web.Contents` with `sysparm_query`, `sysparm_fields`, `sysparm_limit`, `sysparm_offset` passed through the `Query` record (static base URL, so no dynamic data source issue)
- Paging: `List.Generate` loop, page size 5000, looping until an empty page returns
- Volume: roughly 365,000 incident records for the current year, plus large RITM/task volumes having approx 10 lakh rows and 30 columns
- Capacity: Power BI Pro / shared capacity, North Europe
- Refresh: scheduled daily
**Problems I'm facing**
**1. No query folding.** `Web.Contents` isn't foldable, so every transformation (expand record column, type changes, filters) runs locally in the mashup engine after the full payload is pulled. "View Native Query" is greyed out on every step, as expected. I've done what I can by pushing the date filter into `sysparm_query` so ServiceNow returns only the relevant range, but that's manual server-side filtering, not folding.
**2. Incremental refresh flags the query as non-foldable.** The IR dialog shows "Unable to confirm if the M query can be folded. It is not recommended to use incremental refresh with non-foldable queries." I set it up anyway (`RangeStart`/`RangeEnd` as Date/Time parameters, used both in the `sysparm_query` string and in a `Table.SelectRows` step on `sys_created_on`, with "Only refresh complete days" ticked). Settings were: archive 1 year, incrementally refresh last 2 days.
Even with that, refreshes failed. I've since disabled IR entirely to rule it out as the cause, and the timeouts persist — so the bottleneck is the extraction itself, not the partitioning.
**3. Refresh consistently hits the XMLA timeout.** Refresh history shows:
- 3h 25m — Failed
- 4h 23m — Failed
- 5h 0m 5s — Failed (`Data source error: The XML for Analysis request timed out before it was completed. Timeout value: 17999 sec.`)
After four consecutive failures the service auto-disabled scheduled refresh ("Scheduled refresh has been disabled").
**4. Dot-walked reference fields appear to be a major cost.** My incident query requests around 35 fields including several dot-walked ones: `caller_id.u_zone`, `caller_id.u_market`, `caller_id.u_region`, `location.full_name`, `assignment_group.u_stream`. These are business-critical for the dashboard so I can't drop them, but each one forces a reference join server-side in ServiceNow, and I'm also using `sysparm_display_value=true` which resolves every reference to its label. I suspect this combination is where most of the five hours goes.
**5. Malformed rows in the API response.** Occasionally ServiceNow returns an empty string in place of a record inside the `result` array, which breaks `Table.ExpandRecordColumn` with "We cannot convert the value "" to type Record". I've worked around it with `List.Select(..., each Value.Is(_, type record))` before building the table, but flagging it in case others hit the same.
**What I've already tried**
- Moved all dynamic values into the `Query` record so the data source isn't flagged as dynamic
- Added the mandatory `RangeStart`/`RangeEnd` filter step for IR detection
- Ticked "Only refresh complete days"
- Reduced the incremental window to 2 days
- Disabled IR on the small tables
- Locale-safe datetime parsing instead of hard type conversion
- Null-safe filters throughout
None of it got the refresh under the timeout.
**Questions**
- Has anyone successfully run incremental refresh against a ServiceNow REST source at this volume? Is there a paging or query pattern that performs meaningfully better than a sequential `List.Generate` loop?
- Is there any way to parallelise page requests in Power Query, or is sequential paging an unavoidable ceiling here?
- For those who've moved off direct REST extraction — did you go the ServiceNow Database View / scheduled Export Set route, or stage into SQL Server first? Roughly what refresh times did you end up with?
- Would a Dataflow Gen1 (Pro licence, no Fabric access) realistically help here, given it decouples the extraction from the dataset refresh window but doesn't make the extraction itself faster?
- Any way to raise or work around the 17999-second XMLA timeout on shared capacity, or is that a hard ceiling?
Happy to share the full M script if useful. Any pointers appreciated.
Thanks
1 Reply
- ShivekMaharaj
Memorable Member
Hi Rohit_Agrawal,
I think there are two separate issues here: how the REST extraction is being partitioned, and whether a 2-day incremental window is actually correct for ServiceNow ticket data.
First, I wouldn't treat the "unable to confirm query folding" message as meaning incremental refresh cannot work with this source. Microsoft's incremental refresh guidance for web-based sources explicitly allows RangeStart and RangeEnd to be incorporated directly into the HTTP request.
So for ServiceNow, I would make sure that each incremental partition produces a sysparm_query containing both date boundaries before Web.Contents is called. If ServiceNow only returns records inside that range, you have effectively achieved the important part: filtering at the source, even though this is not normal relational query folding.
One thing I would reconsider is the 2-day refresh window on sys_created_on.
Microsoft's incremental-refresh model assumes that rows outside the refresh period become historical and are no longer refreshed. For something like an incident, problem or change request, a record created months ago can still be modified today. A two-day sys_created_on window would therefore never bring those older updates back into the model.
I also wouldn't use sys_updated_on directly as the partitioning date because Microsoft cautions against changing the date that determines which incremental-refresh partition contains a row.
For this type of ServiceNow workload, my preferred long-term architecture would therefore be:
ServiceNow API → staging store → Power BI
The extraction process can use sys_updated_on as a watermark, retrieve only records changed since the previous run, and upsert those records into SQL/ADLS by sys_id. Power BI can then incrementally refresh from that staged source instead of crawling the ServiceNow API during every semantic-model refresh.
Before redesigning it, though, I would also optimize the current Table API requests. ServiceNow's Table API documentation supports sysparm_limit / sysparm_offset paging and recommends restricting the fields returned. With ~35 dot-walked reference fields across this volume, I would test returning the reference IDs and loading common reference tables separately rather than resolving every related attribute for every ticket row.
I would also test sysparm_exclude_reference_link=true, keep sysparm_display_value=false unless you specifically need display values, and use sysparm_no_count=true if the count isn't required. ServiceNow notes that display-value resolution can add additional work.
I wouldn't make parallel paging inside Power Query my first optimization. Sequential offset paging is the documented ServiceNow pattern, and increasing concurrency could simply move the bottleneck to ServiceNow or its API limits. I would first reduce the number of rows, fields and reference lookups in each request.
I also don't think a Pro/shared-capacity Dataflow Gen1 is likely to solve this by itself. Microsoft's current shared-capacity dataflow limitations include a two-hour timeout per table, three hours per dataflow, and no incremental refresh for shared dataflows.
One other detail caught my attention: you mention that the workspace is Power BI Pro/shared capacity, but the error shows a timeout of 17,999 seconds, which is effectively five hours. Microsoft's current incremental-refresh troubleshooting documentation documents two hours for Pro/shared-capacity semantic-model refreshes and five hours for Premium. I would verify the workspace capacity/license mode because those two observations don't quite line up.
If it really is shared capacity, there isn't a supported setting to simply raise the semantic-model refresh timeout. The Power BI REST API's configurable/enhanced refresh options are also not supported on shared capacity.
So my order of steps would be:
- Verify the actual workspace capacity mode.
- Confirm every ServiceNow API call receives the RangeStart / RangeEnd filter server-side.
- Reduce payload/reference expansion before attempting parallel paging.
- Reconsider whether a 2-day sys_created_on window can capture the lifecycle of your tickets.
- If this needs to be reliable at ~1M+ rows, stage ServiceNow changes by sys_updated_on and upsert them into a durable SQL/lake layer before Power BI.
At this scale, I think separating the operational REST extraction from the semantic-model refresh is likely to give you the biggest reliability improvement.AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
