Power BI
Goal: Show a warm query in a Power BI report.
Power BI reads a warm query the way any HTTP client does: one GET to its endpoint with a bearer token. Nothing is installed in OpenX, and Power BI is not a connector OpenX ships. What follows is the OpenX side, which the code settles, and the Power BI side as it usually looks. Power BI's own menus change between versions, so check the step names there.
Before you start
- Power BI Desktop.
- A warm query that is saved or accepted. A warm query has nothing to build in your cloud, so it is live as soon as it is saved or its proposal is accepted.
- A token. A team API key works while the workspace is in Team or Hybrid mode; in App Identity mode team keys are turned away (see Call the API from your app).
1. Get the endpoint and a token
- Open APIs -> API Manager and find your warm query.
- Copy its endpoint. It looks like
https://openx.fathym.com/ox-api/warm-queries/{lookup}, with your own lookup. - Under API Keys, pick an expiration, pick the rights the key carries, and choose Generate JWT. Copy it when it is shown. A token lives at most 90 days. Any right will do: a team key's route right is not checked today (see Call the API from your app).
2. Add the data source in Power BI
- In Power BI Desktop, choose Get Data -> Web, then Advanced.
- Enter the endpoint as the URL.
- Add an HTTP request header named
Authorizationwith the valueBearer YOUR_TOKEN. - If Power BI then asks how to sign in to the source, choose Anonymous: the header you just added is the credential. This is how Power BI's Web connector usually behaves; it was not run against a workspace here.
3. Turn the response into a table
The response is not a flat list of records. It is one JSON object:
| Part | What it holds |
|---|---|
Engine | The word steps |
Outputs | One entry per output of the query, keyed by the output's name |
Outputs.<name>.Status | Succeeded, Skipped or Failed |
Outputs.<name>.Columns | A list of { Name, Type }, in column order. Type is optional |
Outputs.<name>.Rows | A list of rows. Each row is a list of values in the order of Columns, not a record |
Outputs.<name>.Page | { Truncated }, and Reason when the source cut the table |
A query saved as plain text has one output, named result. A query built from steps can name several; a
Failed or Skipped output carries no Columns or Rows.
In Power Query:
- Expand
Outputs, then the output you want (resultfor a plain query). - Expand
Rowsand split each row into one column per position. - Rename the columns in the order
Columnslists them. - Set column types (date and time, number) and choose Close & Apply.
NOT TESTED HERE
The Power BI steps are the usual ones for a web JSON source. They were not run against a workspace while this page was written, so adapt them to your Power BI version.
4. Build the report
The data is in Power BI now. Visuals, slicers and measures are Power BI's from here.
Refresh
Manual: choose Refresh in Power BI. Each refresh runs the warm query once.
Scheduled, in the Power BI Service: publish the report, open the dataset settings, store the credential Power BI asks for, and set the schedule. How the Service stores a request header, and whether it needs a gateway, is Power BI's decision; read its documentation for your tenant.
TOKENS EXPIRE
A token lives at most 90 days, so a scheduled refresh stops working when its token does. Put the expiry in your calendar, make a new key before it, and update the credential in the dataset. A revoked key is refused on its next call.
Good habits
| Habit | Why |
|---|---|
| Summarise inside the query | The API adds no row cap of its own. A query answers every row the engine returns, so aggregate in the query |
| Match the refresh to the data | Each refresh is a fresh run. Refreshing faster than your data arrives only adds load |
| Give the report its own key | You can revoke it without touching anyone else's, and its rights stay as narrow as the report needs |
| Plan for an empty output | A query with no matching rows still answers; show "No data" instead of failing |
When it does not work
| What you see | What to check |
|---|---|
| An authentication error | The token has expired or was revoked, or the header is not exactly Authorization: Bearer ... |
| An error in App Identity mode | Team keys are turned away there. See Secure your workspace API |
Warm query "..." was not found | The lookup in the URL does not match a saved warm query. Copy the endpoint from the API Manager again |
| Nothing in the table | Run the query in the API Manager (Try it) first. If it returns rows there, the issue is in Power Query |
| A timeout | The query runs long or returns a great deal. Narrow or summarise it in the query text |
Every refusal the API can give is in API refusals.
Next steps
- The same query in a dashboard -> Grafana
- Other ways to call it -> APIs
- Manage tokens -> Security: Secrets