BI datasets

Reporting publishes datasets: tables of agreed figures, such as approved hours per person and day. A business intelligence tool (Power BI, Tableau, Looker Studio or a script of your own) reads them over the public API with an API key. The figures are the same as the ones in Akollo's reports. The tool reads; it never changes anything in Akollo.

1. Create a keyCopy Anchor Link

A key with only this scope cannot read projects, tasks or people, and cannot change anything.

2. List the datasetsCopy Anchor Link

curl https://<akollo-host>/api/public/v1/datasets \
  -H "Authorization: Bearer <api key>" \
  -H "Akollo-Version: 2026-09-24"

The answer lists each dataset the key can read, with its version:

{ "data": [ { "key": "hours.daily", "version": 2 } ] }

The version goes up when a dataset's columns change. An empty list means your organisation has not yet given this key's service account any datasets: ask your reporting administrator to grant them.

3. Read the rows page by pageCopy Anchor Link

curl "https://<akollo-host>/api/public/v1/datasets/hours.daily/rows" \
  -H "Authorization: Bearer <api key>" \
  -H "Akollo-Version: 2026-09-24"

{
  "data": [ { "person": "…", "date": "2026-09-01", "approved_hours": 7.5 } ],
  "has_more": true,
  "next_cursor": "eyJ…",
  "dataset": { "key": "hours.daily", "version": 2 }
}

• Each row is a flat record. A value is text, a number, true/false or empty.
• While has_more is true, ask again with ?cursor=<next_cursor>. The response also carries a Link header with rel="next" that holds the same address. Follow it as it is.
• Reporting decides how many rows a page holds. The feed does not accept limit, $top or $select.
• To read only part of a dataset, add $filter (below). Keep the same $filter on every page: the Link header already carries it.
• A cursor belongs to one dataset, one version, one $filter and the key that received it. If the dataset's version changes while you are paging, or you send the cursor with another key (for example after rolling the key), the next call answers 400 CURSOR_INVALID: start again from the first page.

Filtering with $filterCopy Anchor Link

$filter narrows the rows reporting already lets the key read; it never widens them. It is a list of comparisons joined with and:

<column> <operator> <value> and <column> <operator> <value> …

• Columns are the dataset's own column names (lower case, as they appear in the rows).
• Operators: eq (equal), ne (not equal), gt, ge, lt, le (greater than, greater or equal, less than, less or equal). Lower case only.
• Values: text in single quotes (write a quote inside text as two quotes: 'O''Brien'), whole numbers (600, -15), true, false, null (only with eq or ne) and dates as YYYY-MM-DD without quotes.
• At most 8 comparisons and 1 024 characters. or, not, parentheses and functions are not supported.

Examples (URL-encode the value when you build the address):

$filter=local_date ge 2026-09-01 and local_date lt 2026-10-01
$filter=team_id eq '018f0000-0000-7000-8000-000000000001'
$filter=local_date ge 2026-09-01 and employee_count gt 5

A filter that names a column the dataset does not have, or compares a column with the wrong kind of value, answers 400 VALIDATION_FAILED.

When a call is refusedCopy Anchor Link

Power BI DesktopCopy Anchor Link

Use Get data → Blank query, open the Advanced Editor and paste the query below. Put the host, the dataset key and the API key in. For scheduled refresh in the Power BI service, keep the host as the first argument of Web.Contents and the path in RelativePath, as below, and set the data source's credential to Anonymous: the key travels in the header.

let
    Host = "https://<akollo-host>",
    Dataset = "hours.daily",
    ApiKey = "<api key>",
    GetPage = (cursor as nullable text) =>
        Json.Document(
            Web.Contents(
                Host,
                [
                    RelativePath = "api/public/v1/datasets/" & Dataset & "/rows",
                    Query = if cursor = null then [] else [cursor = cursor],
                    Headers = [Authorization = "Bearer " & ApiKey, #"Akollo-Version" = "2026-09-24"]
                ]
            )
        ),
    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each if _[has_more] then GetPage(_[next_cursor]) else null
    ),
    Rows = List.Combine(List.Transform(Pages, each _[data])),
    Result = Table.FromRecords(Rows, null, MissingField.UseNull)
in
    Result

Store the key as a Power BI parameter rather than in the query text when the report file is shared.

TableauCopy Anchor Link

Tableau reads the feed in either of two ways.

• Web Data Connector. A small connector page calls the same two addresses: it lists the datasets for the table picker and pages the rows by following next_cursor. The key is entered in the connector's authentication step, not in the page.
• Scheduled extract. A script reads every page into a CSV file (or a Hyper file through Tableau's Hyper API), and Tableau Server or Tableau Cloud refreshes the extract from that file on a schedule. For example:

import csv, os, requests

host = "https://<akollo-host>"
headers = {"Authorization": f"Bearer {os.environ['AKOLLO_API_KEY']}", "Akollo-Version": "2026-09-24"}
url = f"{host}/api/public/v1/datasets/hours.daily/rows"
rows, cursor = [], None

while True:
    page = requests.get(url, headers=headers, params={"cursor": cursor} if cursor else None, timeout=60)
    page.raise_for_status()
    body = page.json()
    rows.extend(body["data"])
    if not body["has_more"]:
        break
    cursor = body["next_cursor"]

with open("hours_daily.csv", "w", newline="", encoding="utf-8") as file:
    columns = sorted({name for row in rows for name in row})
    writer = csv.DictWriter(file, fieldnames=columns)
    writer.writeheader()
    writer.writerows(rows)

Keep the key in the environment or a secrets store, never in the script file.

Looker StudioCopy Anchor Link

Looker Studio reads the feed in either of two ways.

• Community connector. An Apps Script connector calls the same addresses, asks for the key in its authentication step (key type) and pages the rows by following next_cursor.
• Google Sheets on a schedule. An Apps Script in a sheet reads the rows on a time-driven trigger and writes them to a tab; Looker Studio uses that sheet as its source. For example:

function pullAkollo() {
  const key = PropertiesService.getScriptProperties().getProperty('AKOLLO_API_KEY');
  const base = 'https://<akollo-host>/api/public/v1/datasets/hours.daily/rows';
  const headers = { Authorization: 'Bearer ' + key, 'Akollo-Version': '2026-09-24' };
  let rows = [];
  let cursor = null;

  do {
    const url = cursor ? base + '?cursor=' + encodeURIComponent(cursor) : base;
    const body = JSON.parse(UrlFetchApp.fetch(url, { headers: headers }).getContentText());
    rows = rows.concat(body.data);
    cursor = body.has_more ? body.next_cursor : null;
  } while (cursor);

  const columns = [...new Set(rows.flatMap((row) => Object.keys(row)))];
  const sheet = SpreadsheetApp.getActive().getSheetByName('hours.daily');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, columns.length).setValues([columns]);
  if (rows.length > 0) {
    sheet
      .getRange(2, 1, rows.length, columns.length)
      .setValues(rows.map((row) => columns.map((name) => row[name] ?? '')));
  }
}

Keep the key in the script properties, and share the sheet only with the people who may see the figures.

Other ways to get data outCopy Anchor Link

Besides the datasets feed, Integrations → Data out lists export targets: Azure Blob, Power BI and Analytics SQL. With Azure Blob export on, Akollo writes each file once to the chosen container and never changes it; it never deletes, changes or reads existing files. To read datasets straight from the database, see Connecting a BI tool over SQL.

Good practiceCopy Anchor Link

• One key per tool and per workspace, so a key can be replaced without stopping the others.
• Refresh as often as the figures change, not every few minutes; each call counts against the key's per-minute limit and is recorded as API use.
• Replace a key before it expires: Integrations → API keys → Roll keeps the previous key working for a short grace period while you update the tool.

FAQCopy Anchor Link

Related pagesCopy Anchor Link

• Connecting a BI tool over SQL
• API and keys
• Reports