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:
- Connects only over an encrypted connection. The connection string requires it, and the connection fails rather than fall back to an unencrypted one.
- Reads only your organisation’s tables. It cannot see another organisation’s data.
- Cannot change anything. Every table is read-only.
- Finds the tables without a schema prefix. Write
select * from fact_account, not a qualified name. - Cancels any single query that runs longer than its time limit. The usual limit is 30 seconds. Ask your operator if a report needs longer.
- Cancels any single query that needs more temporary disk than its cap. The usual cap is 256 MB. A query that sorts or groups a large table spills to disk, so filter or aggregate it first. Ask your operator if a report needs more.
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:
- If someone publishes a change to an account type, its table changes at once. A new property becomes a new column.
- If a property has a description, the description becomes the column’s description.
- If a stored value cannot be read as the column’s type, the column shows empty rather than failing your query.
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
- A property marked Sensitive never reaches a reporting table. It is missing from
property_historyand from everyaccount_<type>table.dim_propertystill lists it, flagged as sensitive, so you can see that it exists. - The owner of an account is never in
bridge_position_party. Usefact_account.owner_id.
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
- Whether reporting tables are licensed.
- The account types, and so the
account_<type>tables and their columns. - Each property’s label, description and Sensitive switch, in property types.
- The balance groups and their roles, which decide what
exposuresums. - The account roles that appear in
bridge_position_party. - Each transaction type’s bank transaction code.
- Your base currency and the rates you publish, in today’s exchange rates.
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.