--- title: "SQL Analytics - Documentation" description: "Database API access: how to connect, the data model, and worked SQL examples for dimension permutation groups." lastModified: "2026-06-15" search: tags: - "sql" - "analytics" - "documentation" - "teknisk" - "dokumentation" - "database api access" --- This page is in English, matching the source material — no Swedish translation exists yet. This document describes the database access that Kleer provides. With it you get access to your data through a standard SQL interface. If you're interested in this access, contact us. ## Database The backing database is a MySQL instance, reachable from most programming languages and tools. As a first step, we recommend verifying the connection with the standard `mysql` command-line client — this guide uses it to demonstrate connections. ## Connect Connections must always be encrypted. Two solutions are supported: - Direct connection - SSH tunnel With either solution you'll be given a username and a token. The username has the form `user_{user_id}_{company_id}`, where `{user_id}` is the ID of the Kleer user the access is performed as, and `{company_id}` the ID of the company being accessed. The password is always identical to the token used when connecting to the Web API. ### Direct connection Recommended when possible, since it encrypts your data end-to-end. Connect to `data.kleer.se` on the default MySQL port `3306`. A pre-approved IP address must have been given to Kleer — only connections from that address are allowed. SSL is enforced for all connections; the client must know the public CA certificate, downloadable from `https://static.kleer.se/download/ca.crt`. ```bash mysql --user=user_1_2 -p12345 --ssl-ca=ca-cert.pem --host=data.kleer.se report_external_v1 ``` Once connected, verify with: ```sql select * from whoami ``` :::details[Troubleshooting direct connection] 1. Verify you're connecting from the pre-approved IP address. 2. Verify outgoing connections to `data.kleer.se:3306` aren't blocked on your end. 3. Verify username and password. 4. Verify you're using the certificate. ```powershell # Windows (PowerShell) Test-NetConnection data.kleer.se -Port 3306 ``` ```bash # Linux (among others) telnet data.kleer.se 3306 ``` ::: ### SSH tunnel Establish an SSH connection to `data.kleer.se` on port `22` as user `secure`, with a local tunnel to `localhost:3306`, then connect through the tunnel with a MySQL client. A pre-approved IP address is recommended but not required here — useful when connections come from many unknown locations. Only RSA keys are supported, at least 2048 bits. Your public key (and any IP restriction) must be given to Kleer, in `x.509 SubjectPublicInfo PEM`, `OpenSSH public key`, or `SSH public key` format. ```bash ssh-keygen -t rsa -b 4096 ``` ```bash ssh -i private.key -N -L3306:localhost:3306 secure@data.kleer.se ``` ```bash # in another terminal mysql --user=user_1_2 -p12345 report_external_v1 ``` The `-N` flag is important — you're not allowed to execute remote commands over this connection, only forward ports. :::details[Troubleshooting SSH tunnel] 1. Verify you're connecting from the pre-approved IP address (if used). 2. Verify outgoing connections to `data.kleer.se:22` aren't blocked on your end. 3. Verify username and password. ```powershell # Windows (PowerShell) Test-NetConnection data.kleer.se -Port 22 ``` ```bash # Linux (among others) telnet data.kleer.se 22 ``` ::: ## Restrictions A user is generally restricted to **10 simultaneous connections**, each allowed up to **3600 SELECT queries per rolling hour**. If those limits don't fit your use case, let us know. :::note Access is **read-only** — `UPDATE` and `DELETE` queries are not allowed. ::: ## Real-time Data observed through this access is near real-time — under normal operation, changes in the database are visible with sub-second delay, continuously monitored on Kleer's side. ## Versioning Access is versioned with major, minor and fix numbers. The current major version is `1`, reflected in the database name `report_external_v1`. Inside it, a table named `_version__` gives the exact version — e.g. `_version_2_4` means API version `1.2.4`. Only backwards-compatible changes happen within a major version: **Considered minor** (safe to ignore if you follow the rules below): - New tables - New columns on existing tables - Reordering of existing columns - Changed default row ordering **Considered major** (breaking): - Removing a table - Changing a column's definition - Removing a column :::tip Client applications should never depend on column order, or on a fixed column count from a wildcard query. Use explicit `ORDER BY` whenever row order matters. ::: When a new major version ships, previous major versions continue to exist for a grace period so clients can migrate. New accesses always start on the latest major version. ## Data model A full ER diagram of every table and how they link (an outgoing link means the originating table holds a foreign key in that direction) can be downloaded from `https://my.kleer.se/web/service/download/report-external/diagram/Version1`. ### General **Amounts.** Stored as signed or unsigned long integers — unsigned only where the context guarantees a single sign. By convention, a negative number implies credit, positive implies debit. An amount column is named `amount` if only one logical value exists in the table, otherwise with a descriptive prefix plus `_amount` (e.g. `accounting_amount`). Amounts are stored in the smallest denomination for the currency (öre for SEK, cents for EUR) — fractions of that unit are never stored. **Dates.** Always guaranteed valid — values like `0000-00-00`, `9999-99-99`, or `2017-02-30` never occur. **Percentages.** Stored as decimals with 4 decimal points: `1.0000` = 100%, `0.0000` = 0%, `0.1234` = 12.34%. Useful directly as a multiplication factor. ### Transaction — dimension permutation groups The `transaction` table is the base for the accounting domain — by itself it lets you summarize amounts by account number and/or date. Dimensions add finer-grained detail: a `dimension` has zero or more `dimension_entry` rows, and any `transaction` can be tagged with zero or more of them (all dimensions in Kleer are independent, with no limit on how many you use). Consider a transaction of 100.00 SEK. For each dimension you use, you assign one or more dimension entries with a percentage each (never exceeding 100% within one dimension) — that percentage answers "of the 100.00 SEK, what share belongs to this entry?". **Worked example.** Two dimensions: *Department* (Development, Sales) and *Region* (Stockholm, Malmö). A user tags the transaction 40% Development / 60% Sales, and 10% Stockholm / 90% Malmö. "How much went to Development?" is simple: `100.00 × 40% = 40.00 SEK`. "How much went to Development *and* Malmö?" is not `40.00 + 90.00 = 130.00` — that would create money that doesn't exist. It's a multiplication of percentages instead: `100.00 × 40% × 90% = 36.00 SEK`. This is implemented as **dimension permutation groups** ("dimension groups"): every possible combination of one dimension entry per used dimension gets its own group, with its own amount. In this example, four groups are created: | Group | Amount | | --- | --- | | Development, Stockholm | 4.00 SEK | | Development, Malmö | 36.00 SEK | | Sales, Stockholm | 6.00 SEK | | Sales, Malmö | 54.00 SEK | Three properties always hold: - The sum of all groups equals the original transaction amount. - Every group has the same number of dimension entries. - Every group has exactly one entry per used dimension. So "how much went to Development" is the sum of every group that includes Development, and "Development and Malmö" is the sum of groups that include *both* — ordinary AND/OR/NOT logic over the group set. Groups and their entries live in two tables: - `transaction_dimension_group` - `transaction_dimension_group_entry` Distribution doesn't have to reach 100% — untagged amounts are represented as `NULL` in `transaction_dimension_group_entry.dimension_entry_id` (each row still references its `dimension`, so there's no ambiguity). The three properties above still hold even when a dimension is only partially tagged, which also makes it straightforward to query for under-distributed dimensions. ### SQL examples ```sql title="Net result of Development (Business area) for the current year" SELECT SUM(g.`accounting_amount`) AS net FROM transaction_dimension_group g INNER JOIN transaction_dimension_group_entry e ON e.`transaction_dimension_group_id` = g.`id` INNER JOIN `dimension_entry` de ON de.`id` = e.`dimension_entry_id` AND de.`name` = 'Development' INNER JOIN `dimension` d ON d.id = de.dimension_id AND d.name = 'Business area' INNER JOIN `transaction` t ON t.`id` = g.`transaction_id` AND YEAR(t.`date`) = YEAR(CURRENT_DATE()) ``` Sums every dimension group that includes the entry "Development" under dimension "Business area", for transactions dated in the current calendar year. ```sql title="Revenue of Development (Business area) for the current year" SELECT ABS(SUM(g.`accounting_amount`)) AS revenue FROM transaction_dimension_group g INNER JOIN transaction_dimension_group_entry e ON e.`transaction_dimension_group_id` = g.`id` INNER JOIN `dimension_entry` de ON de.`id` = e.`dimension_entry_id` AND de.`name` = 'Development' INNER JOIN `dimension` d ON d.id = de.dimension_id AND d.name = 'Business area' INNER JOIN `transaction` t ON t.`id` = g.`transaction_id` AND YEAR(t.`date`) = YEAR(CURRENT_DATE()) AND LEFT(t.`account_nr`, 1) = 3 ``` Same as above, restricted to accounts starting with `3` (revenue). Revenue accounts are credited, so `ABS()` gives a more intuitive positive result. ```sql title="Cost of Development (Business area) for the current year" SELECT SUM(g.`accounting_amount`) AS cost FROM transaction_dimension_group g INNER JOIN transaction_dimension_group_entry e ON e.`transaction_dimension_group_id` = g.`id` INNER JOIN `dimension_entry` de ON de.`id` = e.`dimension_entry_id` AND de.`name` = 'Development' INNER JOIN `dimension` d ON d.id = de.dimension_id AND d.name = 'Business area' INNER JOIN `transaction` t ON t.`id` = g.`transaction_id` AND YEAR(t.`date`) = YEAR(CURRENT_DATE()) AND LEFT(t.`account_nr`, 1) BETWEEN 4 AND 8 ``` Same shape, restricted to accounts starting with `4`–`8` (cost). ```sql title="Net result of employee Kalle Kula for the current year" SELECT SUM(g.`accounting_amount`) AS net FROM transaction_dimension_group g INNER JOIN transaction_dimension_group_entry e ON e.`transaction_dimension_group_id` = g.`id` INNER JOIN `dimension_entry` de ON de.`id` = e.`dimension_entry_id` AND de.`name` = 'Kalle Kula' INNER JOIN `dimension` d ON d.id = de.dimension_id AND d.user = TRUE INNER JOIN `transaction` t ON t.`id` = g.`transaction_id` AND YEAR(t.`date`) = YEAR(CURRENT_DATE()) ``` Same pattern applied to the dimension flagged `user = TRUE` — Kleer's built-in per-user dimension. ## All tables The same information can be retrieved live with `select * from doc` (the returned language depends on the connecting user's language setting in Kleer). | Table | Description | | --- | --- | | `account` | Accounting accounts | | `accounting_year` | Fiscal years | | `client` | Clients | | `client_invoice` | Client invoices | | `client_invoice_credit` | Client invoice credit links | | `company` | Companies | | `date` | Date table | | `dimension` | Dimensions | | `dimension_entry` | Dimension entries | | `doc` | Documentation | | `group_voucher_transaction` | Group voucher/transactions | | `month` | Month table | | `payroll_user` | Employment contract | | `project` | Project | | `supplier` | Suppliers | | `supplier_invoice` | Supplier invoices | | `transaction` | Transactions | | `transaction_dimension_group` | Permutation groups per transaction | | `transaction_dimension_group_entry` | Dimension entries per permutation group | | `transaction_reference` | References per transaction | | `user` | User | | `voucher` | Vouchers | | `whoami` | Information about your user | ## Functions | Function | Parameters | Returns | | --- | --- | --- | | `employee_count` | `start_date DATE, end_date DATE` | Number of employees employed at any point in the interval | | `get_user_name` | — | The name part of a MySQL user (internal use) | | `in_balance` | `in_date DATE, start_account_nr INTEGER, end_account_nr INTEGER` | Sum of balances at the start of `in_date` across the account interval | | `period_sum` | `start_date DATE, end_date DATE, start_account_nr INTEGER, end_account_nr INTEGER` | Sum of transactions in the date and account interval | | `period_sum_dimension_entry` | above, plus `dimension_name CHAR(255), dimension_entry_name CHAR(255)` | Same, filtered to a specific dimension entry | ## Beta access For faster iteration on new features, Kleer also offers **beta access** — a separate database, `report_external_beta`, holding tables not yet considered stable for general availability. New tables typically launch here first, then graduate to `report_external_v1` once stabilized. :::warning The [Versioning](#versioning) guarantees above do **not** apply to the beta database. Changes can happen at any time and may break applications relying on it. ::: By default only `report_external_v1` access is granted — contact Kleer if you also want beta access. A diagram for the beta schema is available at `https://my.kleer.se/web/service/download/report-external/diagram/VersionBeta`. ## Relaterade guider - [SQL Analytics - Guide to navigate the Kleer database](/teknisk-dokumentation/sql-analytics-guide-to-navigate-the-kleer-database) - [SQL Analytics - Produktblad](/teknisk-dokumentation/sql-analytics-produktblad) - [SQL - How to set up ODBC Plugin for Excel and SQL Analytics](/teknisk-dokumentation/sql-how-to-set-up-odbc-plugin-for-excel-and-sql-analytics)