SQL Analytics - Documentation

Database API access: how to connect, the data model, and worked SQL examples for dimension permutation groups.

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.

mysql --user=user_1_2 -p12345 --ssl-ca=ca-cert.pem --host=data.kleer.se report_external_v1

Once connected, verify with:

select * from whoami

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.

ssh-keygen -t rsa -b 4096

ssh -i private.key -N -L3306:localhost:3306 secure@data.kleer.se

# 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.

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.

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_<minor>_<fix> 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

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:

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

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.

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.

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).

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).

Functions

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.

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
• SQL Analytics - Produktblad
• SQL - How to set up ODBC Plugin for Excel and SQL Analytics