Connecting Power BI to Foreman

Architecture at a glance

Power BI Service ──► On-prem Gateway ──► HTTPS ──► Foreman API
                                                       │
                                          Authorization: Bearer tmac_…
                                                       │
                                                       ▼
                                       JSON: { rows, nextCursor, schemaVersion }

You install the Microsoft on-premises data gateway on a VM that can reach your Foreman deployment, paste your Foreman API token into the gateway's data source configuration, and Power BI Service pulls through that gateway on a schedule.

For ad-hoc development or smaller setups, Power BI Desktop can connect directly without a gateway.

Prerequisites

• Tenant on the Business plan or above.
• An analytics token (see Issuing & Revoking Analytics Tokens).
• Power BI Desktop (latest version) for authoring; Power BI Service + on-premises data gateway for scheduled refresh.

Available endpoints

All endpoints share the same base path. Replace your-foreman-host with your tenant's domain (the same one you use to sign in).

The exact URLs for your tenant are listed in the Setup panel of Organization → Power BI Analytics with copy buttons.

Connect from Power BI Desktop (development)

1. Get Data → Web
   From the home ribbon, click Get Data, search for Web, and pick the Web connector.

2. Choose "Advanced"
   In the dialog, switch to the Advanced tab so you can set HTTP headers.

3. Paste a dataset URL
   For the URL, use one of the endpoints above with ?pageSize=1000.

4. Add the Authorization header
   Header name: Authorization
   Value: Bearer tmac_<your-token-here>

5. Click OK and inspect
   Power BI shows the JSON response. Expand the rows column, then convert to a table and expand the record fields.

This loads the first page only (up to 1,000 rows). For full datasets, use the paginator below.

Paginating with M

Drop this into a blank query (Home → Get Data → Blank Query → Advanced Editor). Replace BASE_URL and TOKEN:

let
    BaseUrl = "https://your-foreman-host/api/analytics/v1/access-requests",
    Token   = "tmac_REPLACE_ME",
    Headers = [
        #"Authorization" = "Bearer " & Token,
        #"Accept"        = "application/json"
    ],

    GetPage = (cursor as nullable text) as record =>
        let
            qs = if cursor = null
                 then [pageSize="2000"]
                 else [pageSize="2000", cursor=cursor],
            resp = Json.Document(Web.Contents(BaseUrl, [
                Headers              = Headers,
                Query                = qs,
                ManualStatusHandling = {429, 401, 403}
            ])),
            rows = if Record.HasFields(resp, "rows") then resp[rows] else {},
            next = if Record.HasFields(resp, "nextCursor") then resp[nextCursor] else null
        in
            [ rows = rows, nextCursor = next ],

    Pages = List.Generate(
        () => GetPage(null),
        each _[rows] <> null,
        each if _[nextCursor] = null
             then [rows = null, nextCursor = null]
             else GetPage(_[nextCursor])
    ),

    AllRows  = List.Combine(List.Transform(Pages, each _[rows])),
    AsTable  = Table.FromList(AllRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1", Record.FieldNames(AllRows{0}))
in
    Expanded

This walks every page in order until nextCursor returns null. For a tenant with hundreds of thousands of QA check results, expect the initial load to take 1–2 minutes.

Connect from Power BI Service (production)

For scheduled refresh, install the on-premises data gateway on a server that can reach your Foreman deployment over HTTPS.

1. Download the gateway from Microsoft's gateway docs and run the installer.
2. In Power BI Service, go to Settings → Manage gateways.
3. Add a Web data source, point it at one of the analytics endpoints, and set authentication to Web API (or Anonymous if your gateway version doesn't support Web API — the bearer token in the M header still authenticates the call).
4. In your dataset settings, enable gateway connection and pick the gateway you just registered.
5. Configure scheduled refresh — hourly is plenty for most reporting use cases.

Incremental refresh

Once the dataset is loading from end to end, switch to incremental refresh so you don't re-pull the entire history every hour. Power BI uses two date parameters, RangeStart and RangeEnd, that you pass to the modifiedSince query parameter:

let
    Since = DateTime.ToText(RangeStart, "yyyy-MM-ddTHH:mm:ssZ"),
    qs    = if cursor = null
            then [pageSize="2000", modifiedSince=Since]
            else [pageSize="2000", modifiedSince=Since, cursor=cursor]
    // ...rest of the paginator
in
    ...

Recommended dataset settings: store the last 2 years, refresh the last 7 days. The first refresh is full; every subsequent refresh only pulls rows whose updated_at_utc is in the recent window.

Troubleshooting

See also

• Power BI Analytics Overview
• Issuing & Revoking Analytics Tokens
• Privacy & Requester PII