Troubleshooting ServiceNow Performance Analytics API Timeouts, Power BI Refresh Failed, and SharePoint 503 Errors

Understanding the Error Landscape

Enterprise reporting architectures frequently combine ServiceNow Performance Analytics (PA) for IT service management metrics, Power BI for visualization, and SharePoint as an intermediate file repository or gateway host. When this pipeline breaks, you are typically confronted with a cascading series of errors.

The most common symptoms include:

1. Power BI Refresh Failed: DataSource.Error: Web.Contents failed to get contents from 'https://<instance>.service-now.com/api/now/pa/scorecards' (500): Internal Server Error or a timeout after 100 seconds.
2. SharePoint 503 Server Unavailable: OData: Request failed: The remote server returned an error: (503) Server Unavailable. This happens when Power BI attempts to read ServiceNow data dumps routed through SharePoint Online folders.

The Root Causes

Service Now Performance Analytics (SNOW PA): The ServiceNow PA REST API (/api/now/pa/scorecards) is designed for querying aggregated scores, not bulk data extraction. When Power BI requests a massive date range without pagination, the ServiceNow database takes too long to calculate and return the JSON payload. ServiceNow's default REST API transaction timeout (usually 60 seconds) is exceeded, dropping the connection and throwing a 500 or 504 error to the client.

SharePoint Throttling (503): If your architecture involves exporting ServiceNow PA data to CSVs hosted on SharePoint Online (a common workaround for API limits), Power BI must read these files. SharePoint imposes strict throttling limits based on concurrent requests and data volume per user account. When the Power BI service accounts exceed these limits during a scheduled refresh, SharePoint forcefully terminates the connection with a 503 Server Unavailable or 429 Too Many Requests status, and typically includes a Retry-After HTTP header.

Step 1: Diagnose the Failure Point

Before modifying code or infrastructure, you must identify exactly which hop in the data pipeline is failing.

Analyzing ServiceNow Node Logs

If the failure originates at the ServiceNow instance, you need to check the syslog or node logs. Log into ServiceNow as an administrator and navigate to System Logs > Errors.

Look for errors matching: Transaction cancelled: maximum execution time exceeded or REST API execution failed due to timeout

You can also use the curl command (provided in the code block section) to manually simulate the Power BI request and measure the Time to First Byte (TTFB). If TTFB exceeds 60 seconds, ServiceNow is killing the transaction.

Inspecting Power BI Gateway Logs

If you are using an On-Premises Data Gateway, the logs will reveal if the timeout is occurring at the gateway level. Navigate to the Gateway application, export the logs, and search GatewayCore.log for MashupDataEvaluationError.

Catching SharePoint Throttling Headers

For the SharePoint 503 errors, you need to intercept the HTTP response headers. A standard 503 could mean the server is genuinely down, but in Microsoft 365, it almost always means throttling. Look for the Retry-After header. If it's present, you are being throttled.

Step 2: Implement the Fixes

Fix 1: Implement Pagination for ServiceNow Performance Analytics

Never pull the entire PA scorecard dataset in a single GET request. You must use pagination. ServiceNow supports sysparm_offset and sysparm_limit.

In Power BI, you must write a custom M query function to loop through the API until no more records are returned.

let
    GetPage = (Offset as number) =>
    let
        Source = Json.Document(Web.Contents(
            "https://<instance>.service-now.com",
            [
                RelativePath="/api/now/pa/scorecards",
                Query=[
                    sysparm_limit="10000",
                    sysparm_offset=Text.From(Offset),
                    sysparm_uuid="<your_pa_indicator_uuid>"
                ]
            ]
        )),
        Result = try Source[result] otherwise null
    in
        Result,
    
    GeneratedList = List.Generate(
        () => [Offset = 0, Data = GetPage(0)],
        each [Data] <> null and not List.IsEmpty([Data]),
        each [Offset = [Offset] + 10000, Data = GetPage([Offset] + 10000)],
        each [Data]
    ),
    CombinedData = List.Combine(GeneratedList)
in
    CombinedData

This script forces Power BI to request 10,000 records at a time, preventing the ServiceNow transaction from timing out.

Fix 2: Mitigating SharePoint 503 Errors

If your pipeline uses SharePoint as an intermediary, you must respect the Retry-After header, or better yet, avoid the throttling entirely.

1. Use a Service Principal (App-Only Context): SharePoint heavily throttles user accounts (username/password). Reconfigure your Power BI SharePoint connector to use a Service Principal (App ID and Client Secret) granted Sites.Read.All permissions via Microsoft Entra ID. Service Principals have significantly higher API limits.
2. Stagger Scheduled Refreshes: Do not schedule all Power BI datasets to refresh at exactly 08:00 AM or top-of-the-hour. Offset them (e.g., 08:13 AM, 08:27 AM) to distribute the load on the SharePoint tenant.
3. Switch to Azure Data Lake: If you are moving hundreds of megabytes of exported ServiceNow data daily, SharePoint is the wrong tool. Re-route your ServiceNow exports to Azure Data Lake Storage (ADLS) Gen2, which natively handles big data workloads and is deeply integrated with Power BI without 503 throttling risks.

Fix 3: Adjusting ServiceNow System Properties (Temporary Relief)

If you need immediate relief while developers work on pagination, you can increase the REST API timeout in ServiceNow. Note: This is discouraged for long-term health as it ties up worker threads.

1. Navigate to sys_properties.list in the ServiceNow filter navigator.
2. Search for glide.rest.outbound.ecc_response.timeout.
3. Increase the value from 30 or 60 to 120 (seconds).
4. Search for glide.transaction.default.timeout and ensure it is not overriding your REST limits.

Fix 4: Power BI Incremental Refresh

Once you have paginated data coming in, configure Incremental Refresh in Power BI Desktop.

1. Define RangeStart and RangeEnd parameters in Power Query.
2. Apply these parameters to the sysparm_query in your ServiceNow API call (e.g., filtering on the sys_created_on or start_at fields in the PA scores table).
3. Right-click your table in Power BI, select Incremental Refresh, and configure it to store 5 years of data but only refresh the last 3 days. This drastically reduces the daily payload size from ServiceNow, completely eliminating timeout risks.