# Roiva's figures in Power BI and your data warehouse

> Read Roiva's reporting tables in Power BI or load them into Snowflake, Databricks or BigQuery: turning them on, tokens, queries, reconciling.
>
> Source: https://roiva.ai/docs/reporting-tables-power-bi-and-warehouses
> Checked against the product on October 04, 2026

If your finance and leadership reporting runs in Power BI or on a data warehouse, you don't have to rebuild Roiva's figures there. Roiva builds a set of **reporting tables** every night, flat and typed, one Parquet file each: initiatives, approved value with the formula version and inputs behind each figure, the metric readings and the documents each figure cites, costs, approvals, budgets against spend, portfolio decisions, locked months and AI spend. Power BI reads them directly, and any warehouse loads them.

Every table in a build is read at the same moment, and Roiva checks the build against its own reports, to the cent, before anyone can read it. The cash value and freed capacity measures below, for a quarter, give the figures on the Board Brief for that quarter once it has ended. A brief of a quarter to date stops at yesterday, while the tables' quarter runs to its last day. [Reporting tables](https://roiva.ai/docs/reference/reporting-tables) lists every table and column.

#### Who can set it up

An account Owner or Admin, on a plan that includes API access. The tables are read with a **reporting token**, which reads these tables and nothing else: not the rest of Roiva's API, and not the MCP server.

#### Turn them on

1. Open **Organization Settings → Reporting Tables**. Organization Settings is in the menu under your name at the bottom of the sidebar.
2. Choose **Turn on**. Roiva builds the tables straight away, then again every night at 07:00 UTC, after the day's syncs.
3. Under **Issue a reporting token**, give it a name you'll recognize later, such as "Power BI: AI portfolio", and choose **Issue**.
4. Copy the token right away. Roiva keeps only a one-way fingerprint of it and can't show it again.

#### Read them in Power BI

Each table is at `https://roiva.ai/api/v1/reporting/latest/<table>.parquet`, such as `reported_value.parquet`. In Power BI Desktop, first add one query that fetches any table: get data, choose Blank query, open the Advanced Editor, paste this, and name the query `RoivaTable`.

```
(table as text) =>
    Parquet.Document(
        Binary.Buffer(
            Web.Contents(
                "https://roiva.ai/api/v1",
                [RelativePath = "reporting/latest/" & table & ".parquet"]
            )
        )
    )
```

`Binary.Buffer` matters: Power Query's Parquet reader needs the whole file in hand, and refuses the download as it streams in ("Parquet.Document cannot be used with streamed binary values"). The tables are small enough to hold whole.

Then:

1. For each table you want, select `RoivaTable`, type the table's name (such as `reported_value`) as its parameter, choose Invoke, and rename the new query after the table.
2. When Power BI asks how to connect, choose Basic. Enter any user name, and paste the reporting token as the password. Set the privacy level to Organizational.

Keep the address exactly as above, with the table name in `RelativePath`. The Power BI service can only refresh a web source whose base address doesn't change.

To refresh in the Power BI service, publish the report, then in the semantic model's settings enter the same Basic credential under data source credentials, and schedule a refresh after 07:00 UTC. The credential is kept in Power BI's own encrypted store, not in the report file.

The same queries work in a Microsoft Fabric Dataflow Gen2: get data, choose Blank query for `RoivaTable`, invoke it for each table, then Save & run. The dataflow refreshes in the service with the stored credential, and no gateway is needed.

#### Build the model

Relate `initiatives[initiative_id]` to `initiative_id` in `reported_value`, `costs` and the other tables, one to many. For dates, relate your calendar table to `period_month` in `reported_value` and in `costs`. An entry counts in the period its `period_start` falls in, as in every Roiva report, so a twelve-month entry starting in January counts in Q1 and in no other quarter.

The headline measures, the way Roiva computes them. Value is two figures that are never added: cash value, which ROI is taken over, and freed capacity beside it. `entry_type` says which a row is. A `capacity_realized` row is freed capacity a person showed became cash, so it counts in cash value and comes out of that initiative's freed capacity for the month, never below zero:

```
Cash value     = DIVIDE(
                     CALCULATE(SUM(reported_value[usd_amount_cents]),
                         reported_value[entry_type] IN {"revenue_gain", "cost_savings", "cost_avoidance", "capacity_realized"}),
                     100)
Freed capacity = DIVIDE(
                     SUMX(
                         SUMMARIZE(reported_value, reported_value[initiative_id], reported_value[period_month]),
                         MAX(0,
                             CALCULATE(SUM(reported_value[usd_amount_cents]), reported_value[entry_type] = "capacity")
                           - CALCULATE(SUM(reported_value[usd_amount_cents]), reported_value[entry_type] = "capacity_realized"))),
                     100)
Total cost     = DIVIDE(SUM(costs[usd_amount_cents]), 100)
Net cash value = [Cash value] - [Total cost]
Cash ROI       = IF([Cash value] > 0 && [Total cost] > 0, DIVIDE([Net cash value], [Total cost]))
```

The measures add `usd_amount_cents`, not `amount_cents`. An entry keeps the currency its source gave it, so a deal won in euros has its `amount_cents` in euros. `usd_amount_cents` is its value in dollars, converted at the European Central Bank's average rate over its period (`usd_rate`, `usd_rate_from` and `usd_rate_through` say which), and it equals `amount_cents` for a dollar entry. It is empty while an entry waits on its rate, and Roiva's figures leave that entry out until it has one.

Costs work the same way, with one difference: a cost doesn't wait. While its period's rates aren't all published, it counts at the latest ECB rate and `usd_rate_provisional` is true; a later snapshot carries the period's average, and the month can't be locked until it does. So a known cost in a currency the ECB publishes is never left out of total cost. A cost in a currency it doesn't publish has no dollar figure and is in no total.

Don't sum across every row either: that adds freed capacity to cash, and counts a realized dollar twice.

An entry with no period counts in all-time figures only: with no date filter it's included, and any date filter leaves it out, as Roiva's reports do.

#### Check the figures against Roiva

`reported_totals` holds the figures Roiva's own reports compute: for the whole portfolio and for each initiative, all time, each fiscal quarter and each fiscal year. For any row, the measures above over its dates give the same cash value, freed capacity and total cost, and Roiva refuses to publish a build where they don't. A quick check in Power BI:

```
Roiva cash value, all time =
    CALCULATE(
        DIVIDE(SUM(reported_totals[cash_value_cents]), 100),
        reported_totals[scope] = "portfolio",
        reported_totals[window_key] = "all"
    )
```

With no filters applied, it equals **Cash value**. `freed_capacity_cents` checks **Freed capacity** the same way.

#### Deliver them to your own bucket

Roiva can write each night's build into an Amazon S3 bucket you own, and your warehouse loads it from there. Roiva writes through a role you create for it, which can add files to one folder and do nothing else: it can't read, list or delete anything in the bucket, and it never touches your warehouse.

1. On **Organization Settings → Reporting Tables**, under **Deliver to your storage**, enter the bucket, its region, and optionally a folder, and choose **Add destination**.
2. Roiva shows three AWS CLI commands, filled in with your bucket and a unique ExternalId. Run them in the AWS account that owns the bucket. They create a role named `roiva-reporting-…` that only Roiva can assume, and only with that ExternalId, and give it `s3:PutObject` on Roiva's folder. If your AWS CLI has several accounts set up, add `--profile` with the bucket's account after `aws` in each command: a role created in another account can't write to the bucket, and the account number in the ARN the last command prints shows where it went.
3. Paste the role's ARN, which the last command prints, and choose **Check access**. Roiva assumes the role, writes a small check file, then confirms it can't read, list or delete. Any step that fails says what to run to fix it, and a role that can do more than write is flagged with the permission to remove.

Once the check passes, Roiva delivers the newest build straight away, then each night's build after 07:00 UTC. Each build gets a folder of its own, and nothing already in the bucket is ever replaced:

```
s3://your-bucket/<folder>/roiva/v1/snapshots/20260915T070000Z/reported_value.parquet
s3://your-bucket/<folder>/roiva/v1/snapshots/20260915T070000Z/manifest.json
```

`manifest.json` is written after every table beside it, so a folder with a manifest is complete. Old builds stay until your bucket's lifecycle rule removes them, which also lets you see what the figures were on any given day. If deliveries fail three nights running, Owners and Admins are emailed, and Roiva keeps trying each night.

Each destination can also take a **weekly full-account export**: the same ZIP of CSVs as **Export all data**, written every Sunday at 08:00 UTC to `roiva/v1/archives/<time>/` beside the snapshots, through the same role. It holds much of what the tables leave out, including value entries in every status, the audit log, synced records and uploaded documents, contracts and statements of work among them, so turn it on (under the destination, **Weekly full-account export → Turn on**) only where everyone who can read the bucket may see all of that. Every Owner is emailed when it's turned on or off, the first one goes straight away, and Owners and Admins are emailed if one doesn't arrive. Roiva keeps no copy once it's written. The loaders below read only `snapshots/`, so the archives don't get in their way.

Load them from the bucket with a read-only identity of your own, never Roiva's role. Append each build, then read the newest one through a view. Load the `snapshot` table last, and filter every view on it, so the views only move to a build once all of its tables have loaded.

**Snowflake**, with a storage integration and stage on `s3://your-bucket/<folder>/roiva/v1/`:

```sql
CREATE FILE FORMAT roiva_parquet TYPE = PARQUET;
CREATE STAGE roiva STORAGE_INTEGRATION = roiva_reporting
  URL = 's3://your-bucket/<folder>/roiva/v1/' FILE_FORMAT = roiva_parquet;

CREATE TABLE reported_value USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) FROM TABLE(INFER_SCHEMA(
    LOCATION => '@roiva/snapshots/', FILE_FORMAT => 'roiva_parquet', IGNORE_CASE => TRUE,
    FILES => '20260915T070000Z/reported_value.parquet')));

COPY INTO reported_value FROM @roiva/snapshots/
  PATTERN = '.*/reported_value[.]parquet' MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

CREATE VIEW reported_value_latest AS
  SELECT * FROM reported_value WHERE snapshot_id = (SELECT MAX(snapshot_id) FROM snapshot);
```

Run the `COPY INTO` statements in a task after 08:00 UTC. Snowflake remembers which files it has loaded, so each run adds only the new build. Snowflake creates the time columns as `TIMESTAMP_NTZ`; the values are UTC.

**Databricks**, with an external location on `s3://your-bucket/<folder>/roiva/v1/`. When you create the location, mark it read-only, under its advanced options: Databricks otherwise tests that its role can write and delete there, which a read-only role rightly can't.

```sql
CREATE TABLE IF NOT EXISTS roiva.reported_value;

COPY INTO roiva.reported_value
  FROM 's3://your-bucket/<folder>/roiva/v1/snapshots/'
  FILEFORMAT = PARQUET PATTERN = '*/reported_value.parquet'
  FORMAT_OPTIONS ('mergeSchema' = 'true') COPY_OPTIONS ('mergeSchema' = 'true');
```

`COPY INTO` skips files it has already loaded, and fills in the empty table's columns from the files.

**Microsoft Fabric**: add a OneLake shortcut to the bucket's `roiva/v1/snapshots/` folder, and load the Parquet files into Lakehouse tables from there.

**BigQuery**: the BigQuery Data Transfer Service for Amazon S3 loads the files on a schedule, with a read-only key of its own for the bucket. Or use the loader below with `bq load --source_format=PARQUET`.

Parquet keeps the types: cents arrive as 64-bit integers, dates as dates and times as UTC timestamps.

#### Or fetch them yourself

Without a bucket, any scheduler can fetch the files after 07:00 UTC. The manifest at `https://roiva.ai/api/v1/reporting/latest` lists every table with its URL, row count and SHA-256. In Python:

```python
import requests

auth = ("roiva", REPORTING_TOKEN)
manifest = requests.get("https://roiva.ai/api/v1/reporting/latest", auth=auth).json()
for table in manifest["tables"]:
    with open(table["file"], "wb") as f:
        f.write(requests.get(table["url"], auth=auth).content)
```

Then load each file with your warehouse's Parquet loader. Replace each table whole, or append each build and keep the newest `snapshot_id` as above.

#### What's in them, and what isn't

- **Approved value only** in `reported_value`, as in every Roiva report, cash and freed capacity alike. `approval_decisions` records what was decided about every entry, including those sent back.
- **What each figure rests on.** Each value row's `basis` says whether it was compared with a measured, reconstructed or estimated before-figure, or none, and each reading's `baseline_method` says how a baseline was measured. Splitting cash value and freed capacity by `basis` gives the same split Roiva's reports show.
- **No sample data.** Sample initiatives, and anything recorded against them, are left out of every table.
- **People are a name and an id**, never an email address.
- **Free text is included:** notes on figures and readings, cost descriptions, and the reasons given for approvals, budgets and decisions. These can name people or customers, so treat the tables as you would the account itself. The reference marks each free-text column.

#### Tokens, rotation and turning it off

- **Rotate** next to a token issues a new one with the same name. The old one keeps working for 7 days, time to paste the new one into Power BI or your loader, then stops.
- **Revoke** cuts a token off on its next request. A token also stops working when it expires, when the person it was issued to leaves the account, or if your plan no longer includes API access.
- Every build, and every table read, is recorded in your audit log.
- Roiva keeps each build's files for 14 days, and the newest one until a newer build replaces it.
- **Turn off** stops the nightly builds. Power BI or a loader keeps reading the last build until its files expire.

Roiva never writes into your warehouse or your Power BI workspace. The most it can do is add files to the one folder you gave its role; nothing in Roiva can change what you've loaded.

#### Troubleshooting

- **401 Unauthorized**: the token is missing, wrong, expired or revoked, or the person it was issued to has left the account. In Power BI, check the credential is Basic with the token as the password.
- **403 Forbidden**: a reporting token was sent to another part of Roiva's API, which it can't read, your plan no longer includes API access, or Roiva has locked the account. The response says which.
- **404 Not Found** on `latest`: reporting tables aren't turned on yet, or the first build hasn't finished. Check **Organization Settings → Reporting Tables**.
- **A build shows as Refused**: its figures disagreed with Roiva's own reports, so Roiva didn't publish it and was alerted. Power BI and your loader keep reading the previous build.