Reporting tables for BI tools

Reporting tables let you point Power BI, Tableau, Excel or any SQL tool at your book. You connect with a read-only login and query a set of tables shaped for reporting. Every query reads the book as it stands at that moment. Nothing is copied overnight, so nothing is out of date.

This article lists the tables, shows how to join them safely, and explains the date setting and the caveats.

Getting a login

Your platform operator issues each BI login. You receive a connection string once. Copy it into your BI tool straight away, because it is not shown again and no copy is kept.

A login:

If your organisation’s licence for reporting tables ends, its logins stop working. The tables themselves stay in place.

How the tables fit together

The tables follow the Kimball dimensional model, the usual shape for BI. Fact tables hold the things you count and sum. Dimension tables describe them. You join a fact to a dimension on an id.

flowchart LR
  FA[fact_account] --> DAT[dim_account_type]
  FA --> DP[dim_product]
  DP --> DAT
  FA --> DC[dim_counterparty]
  FA --> DOE[dim_operating_entity]
  FA --> DS[dim_structure]
  FPB[fact_position_balance] --> FA
  FPB --> DBG[dim_balance_group]
  FT[fact_transaction] --> DTT[dim_transaction_type]
  BPP[bridge_position_party] --> FA
  BPP --> DC
  PH[property_history] --> DLV[dim_lookup_value]

Every table carries a tenant_id column. It always holds your organisation’s id.

The tables

View One row per What it holds
fact_account Account Account number, account type, product, currency, owner, operating entity, structure, start date and status.
fact_position_balance Account and position Each balance on the account, its balance group, the group’s role and the amount.
fact_position_balance_history Account, position and day-end date The balance after every day-end since the account opened.
exposure Account The headline balance plus arrears, in the account’s currency and in your base currency.
fact_transaction Posted transaction Type, ISO 20022 bank transaction code, amount, the parent transaction of a tax leg, trading date, value date and source.
fact_journal_line Journal entry and side The debit line and the credit line of each entry, with the general-ledger account, amounts in both currencies and a provisional flag.
bridge_position_party Account, party and role Every party holding a role on an account, except the owner.
property_history Subject, property and effective date Every value a property has held on an account or counterparty, with the dates it held.
dim_account_type Account type Label, nature (asset or liability) and product family.
dim_product Product Key, label, the account type it is sold on, and whether and when it is on sale.
dim_position_type Account type and position The position’s label, category and balance group.
dim_balance_group Balance group Name, label, platform role and display order.
dim_transaction_type Account type and transaction type Label and ISO 20022 bank transaction code.
dim_property Property and owning type Label, kind, lookup type, description, and whether it is sensitive.
dim_lookup_value Lookup value The value’s id, its code, its label today, and its hierarchy as level_1 to level_4, root first.
dim_counterparty Counterparty Number, name, type, status and registration date.
dim_operating_entity Operating entity Number, name, type, accounting currency, year-end month and status.
dim_structure Structure Number, description, type and status.
dim_currency_rate Currency and date The published mid rate on each date up to the as-at date, and whether it is still provisional.
account_<type> Account of that type One table per account type, described below.

Every column carries a description. Most BI tools show it when you hover over the column. The one exception is a property column whose property has no description.

One name per idea

A column means the same thing in every table it appears in. account_id always names an account, account_type always names an account type, and currency always names the account’s currency. Join on columns with the same name.

A table per account type

Each account type has its own table. Its name is account_ followed by the type’s name in lower case with underscores. A personal loan type is account_personal_loan.

It holds every column of fact_account, filtered to that type. It adds one column per property declared on the type, named the same way: purposeOfLoan becomes purpose_of_loan. A lookup or a reference property also gets a _label column beside it, such as purpose_of_loan_label.

These tables follow your configuration:

Joining parties without counting twice

The owner of an account is on fact_account. Every other party, such as a guarantor or a co-borrower, is on bridge_position_party.

Always filter the bridge on role_type. A party can hold two roles on one account. An unfiltered join returns the account once per role.

Take a personal loan advanced 100,000.00 in rand on 30 September 2026. Alex owns it. Sam both guarantees it and co-signs it.

Query Rows for Sam Exposure summed for Sam
Bridge joined with no role filter 2 200,000.00 — wrong
Bridge filtered to guarantors 1 100,000.00
Alex, through fact_account.owner_id not in the bridge 100,000.00

The filtered join sums guaranteed exposure per guarantor, in your base currency:

select p.name, sum(e.exposure_base) as guaranteed_exposure
from exposure e
join bridge_position_party b
  on b.account_id = e.account_id
 and b.role_type = 'ledgertm:account_role_type:guarantor'
 and b.status = 'Active'
join dim_counterparty p on p.counterparty_id = b.party_id
group by p.name;

Role names are your organisation’s own. Run select distinct role_type from bridge_position_party to see the ones in use.

Reading the book for a past date

By default every table answers for now. To answer for a past date, run one statement when you connect:

set reporting.as_at = '2026-09-30';

Every table then answers for 30 September 2026 until you change it or disconnect. Run reset reporting.as_at to go back to now.

The date changes these tables:

Table With a past date set
fact_position_balance and exposure The balance after the day-end on that date, or the latest day-end before it.
account_<type> Each property’s value on that date.
exposure and dim_currency_rate The rate on that date, or the latest one before it.

fact_position_balance_history and property_history always hold the whole timeline. The date does not filter them.

Here is a worked example. A loan is advanced 100,000.00 on 30 September 2026, then 5,000.00 on 1 October, then 2,500.00 on 2 November. Day-end ran on 30 September and on 1 October.

reporting.as_at Principal balance
2026-09-29 empty — no day-end had run yet
2026-09-30 100,000.00
2026-10-15 105,000.00 — the 1 October day-end
not set 107,500.00 — the book now

If you set today’s trading date or a later date, you get the book now. If the value is not a date, the query fails with an error naming the bad value.

Converting to your base currency

exposure_base converts the exposure into your organisation’s base currency. It uses the mid rate published on the as-at date, or the latest one before it. On one date, a final rate wins over a provisional one. An account in the base currency converts at 1.

If no rate has been published for a currency, exposure_base is empty for its accounts. The account still has a row, so a missing rate never silently drops an account from a total. Check for empty values before you trust a base-currency sum.

Transaction codes

fact_transaction and dim_transaction_type carry each transaction’s ISO 20022 bank transaction code, such as LDAS/CSLN/FEES. It is the same code the platform puts on statements. A BI report and a statement therefore classify a transaction the same way. Group or filter on it to report fees, advances or repayments across products. The standards register’s ISO 20022 Bank Transaction Codes row describes the code set.

Caveats

Today’s figures are not final

Rates are provisional until day-end confirms them. A journal line on a provisional rate carries provisional = true. A correction adds new journal lines rather than changing old ones. A total for today can move until day-end has run.

A past balance uses today’s grouping

For a past date, fact_position_balance shows the balance from that day-end. The balance group and role beside it are today’s. If your organisation has since moved a position into another group, the past balance is reported under the new group.

Some things are never in a table

Other organisations’ sets of tables are listed by code

Any login can list every organisation’s set of reporting tables. Each set is named by a random 12-character code, such as reporting_3f9a0c41d27e. The code never carries an organisation’s name or short id. A login can never read another organisation’s tables.

Your BI logins carry your organisation’s code too, such as bi_3f9a0c41d27e_5c01aa. Your operator cannot issue a login under any other name.

Long organisation ids

An organisation’s short id no longer appears in any reporting name, so a long short id never stops it having reporting tables.

What your organisation configures

Your platform operator decides each login’s name and time limit.

From the LedgerTM product guide, as shipped with the software and written with it. Copied from the platform repository on 2026-11-11.