Power BI

1. Summary

Status: Recipe. A bulk export feed for BI is Planned.

Direction: TimeTracking to Power BI (read-only).

Typical reports: hours per client per month, approval progress (draft/submitted/approved/exported/locked), conflicts, source mix (app, kiosk, mail, import), exported rows per payroll run.

2. Mechanism

Power Query calls the REST API with Web.Contents and a read-only key:

Endpoint	Table
GET /days?from&to[&status][&updated_since]&limit=500&cursor=	one row per placement per date (the resolved value)
GET /claims?from&to[&source]&limit=500&cursor=	raw claims per source (optional)
GET /people?limit=500, /clients?limit=500, /placements?limit=500	dimensions (paginated shape {items, next_cursor})
GET /exports/runs?limit=500	payroll export runs
GET /leave?from&to, GET /expenses?from&to	leave requests and expenses (read keys allowed)

All return {data: {items, next_cursor}}; request the next page with cursor=<next_cursor> until it is null. Payroll hour types (normal, overtime, ORT) are not exposed to read keys: export preview needs a write key; run details (GET /exports/runs/{id}) carry pay_lines per run.

3. Prerequisites

• Power BI Desktop; for scheduled refresh, Power BI Service with Anonymous web credentials and the key in a parameter.
• TimeTracking API key with scope read only.

4. Entity mapping

TimeTracking field	Power BI table.column	Notes
days[].id	FactDay[dayId]	Key.
work_date, placement_id, person_id	FactDay[date], [placementId], [personId]
minutes	FactDay[minutes]	null while has_conflict = true.
status, has_conflict, resolution	FactDay[status] …
approved_by_office_at, approved_by_client_at, export_run_id, updated_at	FactDay	export_run_id relates to FactRun[id].
claims[].source, minutes, confidence, created_at	FactClaim	Optional.
placements items (id, ref, role_title, person_id, client_id, active, external_ref)	DimPlacement	Join client via client_id.
people items	DimPerson
clients items	DimClient
exports/runs items (id, starts_on, ends_on, row_count, created_at)	FactRun

Measure: Hours = DIVIDE(SUM(FactDay[minutes]), 60).

5. Step-by-step setup

1. Parameters TTKey (text, read key) and FromDate (date, for example 2026-01-01).
2. Shared function TTPaged:

(path as text, query as record) as list =>
let
    Base = "https://app.timetracking.tech/api/v1",
    Page = (cursor as nullable text) =>
        let
            Q = if cursor = null then query else Record.AddField(query, "cursor", cursor),
            Resp = Json.Document(Web.Contents(Base, [RelativePath = path, Query = Q,
                       Headers = [Authorization = "Bearer " & TTKey]]))
        in Resp[data],
    Pages = List.Generate(
        () => Page(null),
        (p) => p <> null,
        (p) => if p[next_cursor] = null then null else Page(p[next_cursor])),
    Items = List.Combine(List.Transform(Pages, each _[items]))
in
    Items

1. Query FactDay:

let
    Items = TTPaged("days", [
        from = Date.ToText(FromDate, "yyyy-MM-dd"),
        to = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),
        limit = "500"]),
    T = Table.FromRecords(Items, {"id","placement_id","person_id","work_date","minutes","status","has_conflict",
        "resolution","approved_by_office_at","approved_by_client_at","export_run_id","updated_at"}, MissingField.UseNull),
    Typed = Table.TransformColumnTypes(T, {{"work_date", type date}, {"minutes", Int64.Type}, {"has_conflict", type logical}})
in
    Typed

1. Dimensions: TTPaged("people", [limit="500"]), TTPaged("clients", [limit="500"]), TTPaged("placements", [limit="500"]), TTPaged("exports/runs", [limit="500"]), each through Table.FromRecords.
2. Relationships: FactDay[placement_id] → DimPlacement[id], DimPlacement[client_id] → DimClient[id], FactDay[person_id] → DimPerson[id], FactDay[export_run_id] → FactRun[id].

RelativePath + Query keep the base URL static, which scheduled refresh in the Service requires.

6. Sync schedule and triggering events

Scheduled refresh (for example daily 06:00). With limit=500 a year of days for 200 placements is about 100 pages, well within 600 requests/minute. For large models, use Power BI incremental refresh with RangeStart/RangeEnd mapped to from/to, or keep a history table and refresh only updated_since the last refresh.

7. Corrections, conflicts and locked periods

• Days change until exported/locked (late claims, approvals, client corrections); updated_at and version change with them. Refresh a rolling window, or merge on id using updated_since.
• Conflicted days have minutes = null and has_conflict = true.
• GET /days includes days of inactive placements (unlike GET /org/week).

8. Testing checklist

• Monthly totals equal a manual check (GET /days?from&to summed).
• Paging stops (next_cursor null) and row counts match.
• Scheduled refresh succeeds in the Service.
• The key has only the read scope.

9. Troubleshooting

Problem	Fix
"This dataset includes a dynamic data source"	Use RelativePath/Query, not concatenated URLs.
422 "Invalid cursor"	Cursor reused across endpoints; restart paging.
422 on /days	from and to are required (YYYY-MM-DD).
401	Key revoked or its creator lost admin rights.
403 "API key is read-only" on POST /exports	Export preview needs a write key.
429	Stagger refreshes; wait 60 s.

10. Limits and known gaps

• No hour-type split for read keys except per committed run (GET /exports/runs/{id} → pay_lines). GET /leave and /expenses return plain arrays filtered by from/to, not cursor pages.
• Without limit/cursor/updated_since, GET /people|clients|placements return the old full-array shape; always pass limit for a stable shape.
• A bulk BI feed (Parquet/warehouse export) is Planned.

11. Build it with Claude

Write Power Query M for a Power BI model on the TimeTracking API (base https://app.timetracking.tech/api/v1,
Authorization: Bearer <read-only key> as parameter TTKey). Build a generic paging function using
Web.Contents(Base, [RelativePath, Query, Headers]) and List.Generate over {data:{items, next_cursor}} with
cursor=<next_cursor>. Tables: FactDay from GET days?from&to&limit=500 (id, placement_id, person_id, work_date,
minutes, status, has_conflict, resolution, approved_by_office_at, approved_by_client_at, export_run_id,
updated_at), optional FactClaim from GET claims, dimensions from GET people|clients|placements?limit=500, FactRun
from GET exports/runs. Add relationships, DAX measures (hours, approval rate, conflict rate) and incremental refresh.