2.2 REST API Connectors, Pagination Strategies & Web Data

REST API Connectors, Pagination Strategies & Web Data

Enterprise data architectures increasingly depend on SaaS platforms, web services, and cloud APIs—such as Salesforce, ServiceNow, Jira, Workday, and custom internal microservices. Unlike relational databases that return flat tabular sets over established TCP sockets, web services expose data via HTTP/HTTPS endpoints delivering semi-structured formats like JSON and XML. Ingesting this data into Qlik Sense requires mastering the Qlik REST Connector, automated schema parsing, and iterative script pagination techniques.

1. Qlik REST Connector Mechanics & HTTP Protocols

The Qlik REST Connector is a specialized data provider built to communicate across standard web protocols. When creating a REST connection in Qlik Sense, the architect configures four fundamental layers:

HTTP Methods: GET vs. POST

• GET: The standard HTTP verb for data retrieval. Parameters are passed directly within the Uniform Resource Identifier (URI) query string (e.g., https://api.service.com/v1/incidents?status=open&limit=100). GET requests are idempotent and should never alter the state of the underlying system.
• POST: Used when the request requires sending a structured payload in the HTTP request body. In modern analytics, POST is commonly utilized for:
  1. Executing GraphQL queries, where the client submits a JSON payload defining the exact fields requested.
  2. Querying search engines like Elasticsearch or OpenSearch using complex Query DSL JSON payloads.
  3. Performing OAuth 2.0 token exchanges (grant_type=client_credentials), where client secrets and authorization codes are posted to generate short-lived bearer tokens.

Request Configuration: Headers, Parameters, and Authentication

Authentication Workflows in Enterprise Pipelines

1. Static API Keys: Passed either as a custom header (X-API-KEY: abc123xyz) or as a query parameter. Suitable for low-risk internal services or developer sandboxes.
2. OAuth 2.0 Client Credentials Flow: The gold standard for automated machine-to-machine ETL pipelines. A two-step script sequence is required:
   • Step 1: Issue an HTTP POST to the identity provider token endpoint transmitting client_id and client_secret.
   • Step 2: Extract the resulting access_token from the response JSON and inject it dynamically into subsequent data extract requests using the WITH CONNECTION script extension.

2. Parsing Nested JSON and Relational Key Linkage

JSON (JavaScript Object Notation) documents are hierarchical and support deeply nested arrays and child objects. Because the QIX engine operates on tabular associative tables, the Qlik REST Connector automatically flattens and normalizes hierarchical JSON structures into multiple relational tables.

Automated Schema Decomposition

Consider the following sample JSON response returned from an e-commerce orders API:

{
  "orders": [
    {
      "order_id": "ORD-9821",
      "order_date": "2026-10-14",
      "customer_name": "Apex Logistics",
      "items": [
        {"sku": "WIDGET-01", "qty": 10, "price": 25.50},
        {"sku": "GADGET-09", "qty": 2,  "price": 110.00}
      ],
      "shipping_address": {
        "city": "Chicago",
        "state": "IL"
      }
    }
  ]
}

When the Qlik REST Connector inspects this payload, it decomposes it into three separate tables linked by autogenerated primary and foreign keys:

1. Root Table (root): Represents the top-level document structure. Contains scalar root attributes and creates a primary key named __KEY_root.
2. Orders Table (orders): Represents the orders array. Contains order_id, order_date, customer_name, an internal primary key __KEY_orders, and a foreign key __FK_orders (which links back to __KEY_root).
3. Items Table (items): Represents the nested items array. Contains sku, qty, price, an internal primary key __KEY_items, and a foreign key __FK_items (which points to __KEY_orders).
4. Shipping Address Table (shipping_address): One-to-one child object flattened either into orders or created as an extension table linked by __FK_shipping_address.

Schema Linkage Mechanics

Managing Autogenerated Surrogate Keys

While this automatic decomposition preserves hierarchical integrity, it introduces critical modeling challenges:

• Synthetic Keys ($Syn): If multiple independent REST endpoints generate identical generic key names (__KEY_root, __FK_root), loading them into the same data model creates unintended composite synthetic keys.
• Data Model Bloat: Storing hundreds of thousands of arbitrary connector surrogate integers in memory degrades associative engine performance.
• Best Practice Solution: In production scripts, always use Preceding Load or field aliasing to discard unneeded connector keys, replacing them with business natural keys (e.g. order_id as the relational bridge between Orders and OrderItems).

3. Pagination Strategies in Qlik Load Scripts

Modern REST APIs almost never return an entire enterprise dataset in a single HTTP response. To prevent server exhaustion and bandwidth saturation, APIs enforce Pagination, capping responses to between 50 and 1,000 records per call. The Data Architect must implement script loops that fetch each page sequentially, appending records until the entire dataset is ingested.

Before writing a loop, check the REST connection's own pagination settings (offset, next token, next URL, or custom); when the API fits one of them, the connector pages automatically. When it does not, Qlik Sense provides the WITH CONNECTION script extension to dynamically override connection properties (such as the target URL, query parameters, or HTTP headers) on an existing connection definition.

Strategy 1: Next-Page URL Pagination

Many modern APIs (such as Microsoft Graph, Jira, Shopify, and OData services) include the complete URL of the next page directly in the response metadata (e.g., @odata.nextLink or next_page_url).

// Pattern: Next-Page URL Pagination Loop
LIB CONNECT TO 'REST_Jira_Service_Desk';

// 1. Initialize variables
LET vNextUrl = 'https://jira.enterprise.com/rest/api/3/search?jql=project=IT&maxResults=100';
LET vPageCount = 0;
LET vMaxPages = 1000; // Safety circuit breaker to prevent infinite loops

DO WHILE LEN(vNextUrl) > 0 AND vPageCount < vMaxPages

    // Temporary table to extract the current page payload and next URL
    NextPageMeta:
    LOAD 
        [nextPage] as NextUrl
    ;
    SQL SELECT 
        "nextPage"
    FROM JSON (wrap on) "root"
    WITH CONNECTION (
        Url "$(vNextUrl)"
    );

    // Ingest the actual business record payload from the page
    Issues:
    LOAD 
        [id]          AS IssueID,
        [key]         AS IssueKey,
        [summary]     AS IssueSummary,
        [status]      AS IssueStatus
    ;
    SQL SELECT 
        "id",
        "key",
        "summary",
        "status"
    FROM JSON (wrap on) "issues"
    WITH CONNECTION (
        Url "$(vNextUrl)"
    );

    // Retrieve the next URL from the metadata table
    LET vNextUrl = Peek('NextUrl', 0, 'NextPageMeta');
    DROP TABLE NextPageMeta;

    LET vPageCount = vPageCount + 1;
    TRACE >>> Ingested Page $(vPageCount). Next URL: $(vNextUrl);

LOOP

TRACE >>> Pagination complete. Total pages fetched: $(vPageCount);

Strategy 2: Page-Number and Offset Pagination

Standard REST APIs accept numeric page parameters (?page=1&pageSize=100) or row offset parameters (?offset=0&limit=100). The loop iterates, incrementing the page number or offset until the number of rows returned is less than the page size. Note the accumulation technique: a staging table with the same field list as the target would be automatically concatenated into it, so the script measures each page by how much the target table grew.

// Pattern: Offset / Limit Pagination Loop
LIB CONNECT TO 'REST_Billing_System';

LET vPageSize = 250;
LET vOffset = 0;
LET vRowsFetched = vPageSize;
LET vIteration = 0;
LET vMaxIterations = 500; // Prevent runaway reloads

DO WHILE vRowsFetched = vPageSize AND vIteration < vMaxIterations

    LET vRequestUrl = 'https://billing.company.com/v1/invoices?offset=' & vOffset & '&limit=' & vPageSize;
    LET vRowsBefore = Alt(NoOfRows('Invoices'), 0);

    // Every page has the same field list, so each LOAD auto-concatenates into Invoices
    Invoices:
    LOAD 
        invoice_id     AS InvoiceID,
        customer_id    AS CustomerID,
        invoice_date   AS InvoiceDate,
        amount         AS InvoiceAmount
    ;
    SQL SELECT 
        "invoice_id",
        "customer_id",
        "invoice_date",
        "amount"
    FROM JSON (wrap on) "invoices"
    WITH CONNECTION (
        Url "$(vRequestUrl)"
    );

    // Rows returned by this page = growth of the Invoices table
    LET vRowsFetched = Alt(NoOfRows('Invoices'), 0) - vRowsBefore;
    LET vIteration = vIteration + 1;
    LET vOffset = vOffset + vPageSize;

    TRACE >>> Completed iteration $(vIteration). Rows returned: $(vRowsFetched);

LOOP

Strategy 3: Cursor / Token-Based Pagination

High-volume platforms (Stripe, Slack, Salesforce) use cursor-based pagination. Rather than passing numeric offsets, the API response provides a cryptographic cursor string (e.g. has_more: true, next_cursor: "dGhpcyBpcyBhbiBleGFtcGxl"). Subsequent requests pass ?starting_after=$(vCursor). The loop terminates when has_more evaluates to false or next_cursor returns null.

4. Web File Connectors & Resilient Ingestion

Beyond REST API JSON endpoints, Qlik Sense provides Web File Connectors designed to read structured files directly over HTTP/HTTPS—including CSV, Excel, XML, and HTML web tables hosted on SharePoint, GitHub raw repos, or public data portals.

Resiliency, HTTP Status Codes & Rate Limiting

Web data ingestion is inherently vulnerable to network volatility, server outages, and API throttling. Production architectures must handle these failure modes gracefully:

• HTTP 200 OK: Request succeeded; data payload returned.
• HTTP 401 Unauthorized: Authentication token has expired or is invalid. In long-running multi-page reloads, an access token may expire mid-stream, requiring a dynamic re-authentication routine.
• HTTP 429 Too Many Requests (Rate Limiting): The client has exceeded the API provider's request quota (e.g., maximum 60 requests per minute). API responses often include a Retry-After: 30 header indicating how many seconds the client must wait.
• HTTP 500 / 503 Service Unavailable: Temporary server-side failure. Requires transient retry logic.

Rate Limit Mitigation Techniques

To avoid hitting rate limits and crashing scheduled reload tasks:

1. Script Delays (Sleep): Insert a programmatic pause between pagination requests to stay within rate quotas:

   // Pause execution for 1000 milliseconds (1 second) between page requests
   Sleep 1000;
   

2. Batch Window Throttling: Restrict concurrent reloads of API-dependent apps in Qlik Management Console or Qlik Cloud.
3. Incremental API Harvesting: Never reload entire historical API datasets daily. Ingest only newly created or modified records (?updated_since=$(vLastReloadTimestamp)) and merge them into persistent historical QVD storage.

5. Enterprise Production Scenario

Context: A healthcare technology enterprise integrates incident tickets from ServiceNow into Qlik Cloud. The ServiceNow instance houses 75,000 active customer support cases. The initial pipeline script attempted to load all cases in a single REST request without pagination. The request consistently aborted after 120 seconds with a Gateway Timeout (HTTP 504), stalling executive KPI reporting.

Architecture Solution: The Data Architect redesigns the ingestion script into an authenticated, offset-paginated, fault-tolerant QVD generation pipeline:

// Enterprise ServiceNow REST Ingestion Script
SET ErrorMode = 0; // Prevent script crash on transient network error

LET vBaseUrl = 'https://enterprise.service-now.com/api/now/table/incident';
LET vPageLimit = 500;
LET vOffset = 0;
LET vHasMoreData = 1;
LET vSafetyCounter = 0;

LIB CONNECT TO 'REST_ServiceNow_SvcPrincipal';

DO WHILE vHasMoreData = 1 AND vSafetyCounter < 200

    LET vPagedUrl = '$(vBaseUrl)?sysparm_limit=$(vPageLimit)&sysparm_offset=$(vOffset)&sysparm_query=ORDERBYsys_created_on';

    LET vRowsBefore = Alt(NoOfRows('Incidents'), 0);

    // Same field list on every page, so each LOAD auto-concatenates into Incidents
    Incidents:
    LOAD 
        [sys_id]                AS IncidentID,
        [number]                AS IncidentNumber,
        [short_description]     AS IncidentSummary,
        [priority]              AS IncidentPriority,
        Date(Floor(Timestamp#([opened_at], 'YYYY-MM-DD hh:mm:ss')), 'YYYY-MM-DD') AS OpenedDate,
        [state]                 AS IncidentState
    ;
    SQL SELECT 
        "sys_id",
        "number",
        "short_description",
        "priority",
        "opened_at",
        "state"
    FROM JSON (wrap on) "result"
    WITH CONNECTION (
        Url "$(vPagedUrl)"
    );

    // Rows received on this page = growth of the Incidents table
    LET vRowsReceived = Alt(NoOfRows('Incidents'), 0) - vRowsBefore;
    LET vSafetyCounter = vSafetyCounter + 1;

    IF vRowsReceived < vPageLimit THEN
        // Empty or short page: the last page has been read
        LET vHasMoreData = 0;
    ELSE
        LET vOffset = vOffset + vPageLimit;
        // Respect ServiceNow API rate limits (100ms pause)
        Sleep 100;
    END IF

LOOP

// Store to QVD Tier 1 staging repository
LET vTotalRows = Alt(NoOfRows('Incidents'), 0);
STORE Incidents INTO [lib://Shared_Data_Space:DataFiles/ServiceNow_Incidents.qvd] (qvd);
DROP TABLE Incidents;

TRACE >>> ServiceNow extract complete. Processed $(vTotalRows) total records.;

Outcome: The pipeline smoothly reloads all 75,000 incident tickets across 150 paginated requests in 3 minutes and 40 seconds, with zero timeouts and zero rate-limiting errors.

6. Common Exam Pitfalls

1. Omitting the Loop Circuit Breaker: Writing DO WHILE vNextUrl <> '' without a maximum iteration counter (vPageCount < 1000). If an API provider returns a malformed response or an endless loop of duplicate next-page tokens, the reload engine runs indefinitely, locking server reload threads and consuming compute quotas.
2. Variable Expansion Failures in WITH CONNECTION: Forgetting dollar-sign expansion in the WITH CONNECTION statement (e.g., writing Url "vNextUrl" instead of Url "$(vNextUrl)"). The connector attempts to navigate to the literal text string "vNextUrl", failing with a malformed URI error.
3. Accumulating Autogenerated Connector Keys: Allowing the REST connector's default surrogate keys (__KEY_root, __FK_root, __KEY_result) to enter the business associative model. This causes severe synthetic key creation and cross-table circular references.
4. Hardcoding Expiring Tokens: Hardcoding bearer tokens inside the connection wizard during development. When the token expires 24 hours later, all automated reloads immediately fail with HTTP 401 Unauthorized.