# Data integration

> How data gets into Konstrukt — import configurations, the three transports that share one configuration key (KDI, REST, storage/SFTP), file formats, the GUI import wizard, import semantics, jobs and the integration platform.

Data enters a Konstrukt model through **import configurations**. A configuration defines *what* is imported and *how* (target object, import mode, whether new dimension values are created, who is notified on error). The *transport* — a command-line importer, a REST call or a file drop — is a free choice, because every transport routes the file by the same configuration key. For outbound data (SQL views, the Kex endpoint) see [APIs and SQL access](apis.html).

```flow
Source system | ERP, HR, CRM, files -> Transport | KDI.exe, REST, Azure storage, SFTP -> Import configuration | key (GUID), mode, options -> Target | value type, reference, dimension, property
```

## The Integration area

Integration (*Integration*) is a top-level entry in the left rail. Its panels:

| Panel | Swedish label | Content |
|---|---|---|
| Jobs | *Jobb* | [Managed integration](#managed-integrations) jobs, which can be run from here |
| Logs | *Loggar* | One entry per import, from every transport |
| Configurations | *Konfigurationer* | All import configurations, grouped by target type |
| Accounts | *Konton* | Transport credentials: SQL integration, Storage Account integration, SFTP integration |
| API keys | *API-nycklar* | Keys for the REST API, KDI and MCP |

### Logs

Each import writes one log entry, whatever the transport. The list shows:

- date and time (*Datum och tid*);
- the target's name and ID (*Namn*);
- the entity type (*Entitet*): value type or dimension;
- the event type (*Händelsetyp*), that is, the import mode, for example Add (*Lägg till*), Update (*Uppdatera*) or Replace (*Ersätt*);
- the configuration (*Konfiguration*);
- the result (*Resultat*), for example *Lyckades* (succeeded) or *Varning* (succeeded with warnings).

Expanding an entry shows the full report:

- the outcome and duration;
- the source, meaning the transport or integration and the user it ran as;
- the affected plan and value type or dimension;
- row counts: added, updated and deleted, plus how many duplicate rows were aggregated;
- warnings, with line numbers, for example *No such code … in dimension Project* for rows whose codes do not exist.

The panel *Skicka rapporter till (e-post)* sets default recipients for import reports. Files received over SFTP or Azure storage can be downloaded from the log to check what was imported. This is not possible for files that arrived by other routes.

Log entries and archived import files are currently kept without automatic purging.

### Jobs

The job list shows name, status, start time, next run (for example *Manuellt*, manual) and last run. Expanding a job shows its ID, run time, next run, status and a description written for the customer. **Run job** (*Kör jobb*) starts it immediately.

## Import configurations

The Configurations panel has one tab per target type: Value types, References, Dimensions, Properties (*Värdetyper · Referenser · Dimensioner · Egenskaper*). References are valid import targets in their own right — reference data is imported like plan data.

| Column | Meaning |
|---|---|
| Name | Target object name |
| Plan | Plan the target belongs to |
| Configuration (*Konfiguration*) | Configuration number |
| Configuration type (*Konfigurationstyp*) | Import mode, see below |
| Create new values (*Skapa nya värden*) | Yes/No — create dimension values that appear in the file but not in the dimension |
| Send e-mail to (*Skicka e-post till*) | Recipients of import reports |

The row menu has **Edit configuration** and **Delete configuration** (*Ändra konfiguration · Ta bort konfiguration*).

### Configuration settings

| Setting | Swedish label | Options |
|---|---|---|
| Configuration type | *Konfigurationstyp* | Add (*Lägg till*): add new rows only · Add & update (*Lägg till & Uppdatera*) · Update (*Uppdatera*): existing rows only · Replace (*Ersätt*): empty the value type, then write the file · Delete (*Ta bort*): the file is a [delete filter](#delete-filter-files) |
| Write to reference data | *Skriv till referensdata* | Import into a reference instead of plan data |
| Missing values | *Hantering av saknade värden* | Create new values (*Skapa nya värden*) |
| Report recipients | *Skicka rapporter till* | None (default) or a custom e-mail address, and which imports trigger an e-mail, for example imports with warnings |
| Aggregation of duplicates | *Aggregering* | Default, Sum, Average, Min, Max, or Fail on duplicates (*Misslyckas om dubbletter*) |

Recipients and aggregation are under **Show advanced** (*Visa avancerat*).

### Per-object Integration panel

Dimensions and value types have an Integration side panel (*Integration*) with two parts:

- **Import** (*Importera*) — one card per configuration feeding the object, with its mode (e.g. Add, Add & update) and a status marker. Row menu: Edit configuration, Delete configuration (*Ändra konfiguration · Ta bort konfiguration*). Button: Add configuration (*Lägg till konfiguration*).
- **Export** (*Exportera*) — the Enable export (*Aktivera export*) switch and a generated SQL statement; see [APIs and SQL access](apis.html#active-export).

For each configuration the panel generates ready-to-use instructions for every transport, with the tenant name and configuration key filled in. The generated instruction is the authoritative form of the call; the sections below describe the mechanism.

The configuration types on offer depend on the target:

| Target | Add | Add & update | Update | Replace | Delete |
|---|---|---|---|---|---|
| Value type (plan data) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Reference | ✓ | ✓ | ✓ | ✓ | ✓ |
| Dimension | ✓ | ✓ | ✓ | – | – |
| Property | ✓ | ✓ | ✓ | – | – |

Configurations for references and properties are created from the object's own Integration panel. A value-type configuration asks whether to write to plan data (*Plandata*) or to a reference (*Referens*); a dimension configuration asks whether to write to the dimension or to one of its properties (*Egenskap*). A dimension configuration can also allow new property values to be created (*Tillåt skapande av nya egenskapsvärden*).

## Transports

| Transport | Best for | Authentication | Processing |
|---|---|---|---|
| KDI command-line importer | On-premises Windows servers, scheduled tasks | API key | Not documented |
| REST API | Real-time integration with immediate feedback | OAuth 2.0 client credentials (API key) | Synchronous; result returned in the call |
| Azure storage container | Bulk and parallel uploads from tools with blob support | SAS token in the URL | Asynchronous; result e-mailed and logged |
| SFTP | Systems that can only deliver files | SFTP account | Asynchronous |

### KDI — command-line importer

KonstruktDataImporter (KDI) is a Windows command-line program for on-premises sources that cannot reach a cloud API directly. It sends data over HTTPS, so it also works behind a proxy. Schedule it with Windows Task Scheduler, an SSIS job or any other local tool that can run an `.exe` on a schedule.

The Integration panel offers **Download KDI.exe** (*Ladda ner KDI.exe*) and a generated command line for each configuration:

```
KonstruktDataImporter.exe {tenant} {api-key} {configuration-key}.csv
```

```
KonstruktDataImporter.exe [arguments] [options]
```

| Argument | Meaning |
|---|---|
| `Tenant` | Tenant domain: `mycompany` for `https://mycompany.konstrukt.app` |
| `ApiKey` | API key used to authenticate the calls |
| `FileName` | Path to the input file |

| Option | Meaning |
|---|---|
| `-p <planId>` | Plan to import into. Default: the current default plan |
| `-r <referenceId>` / `-rn <referenceName>` | Import value-type data into a reference instead of plan data. The name is not case-sensitive. No effect on dimension data |
| `-d` | Delete all existing value-type data before inserting. Without it, new rows are added and existing rows updated |
| `-dff` | The file is a [delete filter](#delete-filter-files) |
| `-a` | Add only: existing values are not updated. Cannot be combined with `-d` |
| `-c` | Create values. For value-type data: create missing dimension values. For dimension data: create missing properties and property values, which are otherwise ignored without error |
| `-e <email>` | Send an e-mail to this address if the import fails |
| `-nocfg` | Do not create an import configuration. By default, an import of value-type or dimension data creates one in the tenant if none exists |
| `-retry <n>` | Retry a failed import up to *n* times, for example to ride out network errors |
| `-origin <name>` | Short name for the data source, used in the log. Default: `KonstruktDataImporter.exe` |
| `-v` | Verbose logging |
| `-t users` / `-t accesses` | The file contains [users or access rules](#users-and-access-rules) instead of model data |
| `-app <appId>` | App to import access rules into. Required with `-t accesses` |

#### Choosing the target

- **Configured import:** if the file is named after an import configuration's key (`abc123….csv`), the configuration's stored settings are used.
- **By name:** otherwise the file name, up to the first `_`, must be an existing dimension ID or value-type ID, for example `Amount_20260322_142203.csv`. A file for a dimension property is named `DimensionId_PropertyName`, for example `Account_AccountGroup.csv`.
- **Dimension or value-type data:** a header with `DimValueNo` and `DimValueName` is read as dimension data. Anything else is read as value-type data, and the header must then contain the IDs of all the value type's dimensions. Missing value fields get default values (`DecimalValue` 0, `StringValue` null, `DateTimeValue` 0).
- **Property files** have the header fields `DimTagNo` and `DimTagName`.

#### Reading from a database

Instead of a data file, KDI can take a `.json` file that describes a query against Microsoft SQL Server or MySQL. The result is imported as if it were a delimited file named `Id`, with `Headers` as its header row:

```json
{
  "Type": "mssql",
  "Server": "sql-server-name",
  "Database": "database-name",
  "User": "myuser",
  "Password": "mypassword",
  "Integrated": false,
  "Id": "Amount",
  "Headers": ["Account", "Department", "Month", "DecimalValue", "StringValue"],
  "Query": "select account, department, month, amount, comment from mytable where filter = 1"
}
```

`Type` is `mssql` (default) or `mysql`. `Integrated: true` uses Windows authentication instead of a user and password.

#### Delete filter files

With `-dff`, the file lists which value-type data to delete, in plan data or in a reference.

- **Header:** one column for each dimension (`DimensionId`) or property (`DimensionId.PropertyName`) to filter on.
- **Rows:** each row is one set of data to delete, and data must match every value in the row. Empty cells ignore that column for the row. Rows with invalid values are ignored completely.

```
Department;Account;Month.Year
D100;3010;2026
;5010;2026
D100;;
```

A typical use is clearing all data for a period through an integration before new data is loaded.

#### Users and access rules

- **Users** (`-t users`):
    - `ID`
    - `UPN` (optional; used with single sign-on)
    - `Name`
    - `Type` (optional; the user type, for example `Konstrukt.SysAdmin` or `Konstrukt.BaseAdmin`; it depends on the tenant configuration)
    - `Plans` (optional; plan IDs separated by `|`, or `*` for all plans)
- **Access rules** (`-t accesses -app <appId>`, optionally `-p <planId>` and `-d` to replace existing rules):
    - `User`
    - `Pattern1` and `Pattern2` (used when dimension-based access is enabled)
    - `AccessLevel` (`Read` or `ReadWrite`; default `ReadWrite`)

### REST

Files can be posted to `/api/v1/import/{key}` on the tenant host. The request is authorized with a bearer token obtained from `/api/v1/token` using the client-credentials grant; see [APIs and SQL access](apis.html#authentication). The Integration panel generates a complete Bash and PowerShell script for each configuration in three steps: request a token, extract `access_token`, post the import file (with `TODO` markers where the key goes).

The endpoint processes the file immediately and returns the outcome in the same call. The request format is described in the auto-generated [REST API reference](https://acme.konstrukt.app/api/documentation/index.html) (each tenant serves it at `/api/documentation/index.html` on its own host).

### Azure storage container

Upload the file to the Konstrukt blob container, named after the configuration key:

```
https://konstruktdataimporter.blob.core.windows.net/konstruktdataimporter/{configuration-key}.{extension}?{sas-token}
```

| Part | Value |
|---|---|
| Storage account and container | Both `konstruktdataimporter` |
| `{configuration-key}` | Key of the import configuration, copied from the object's Integration panel |
| `{extension}` | `.csv`, `.txt`, `.tsv` or `.xlsx` |
| `{sas-token}` | Access key (SAS token) created under Integration → Accounts → Storage Account integration (*Skapa ny accessnyckel*). The key has an expiry date, shown under the key. |

Any tool with blob-storage support can upload. Without one, an `HTTP PUT` with header `x-ms-blob-type: BlockBlob` and the file as body is enough:

```bash
curl -H "x-ms-blob-type: BlockBlob" \
     --upload-file path/to/file.csv \
     --url "https://konstruktdataimporter.blob.core.windows.net/konstruktdataimporter/{configuration-key}.csv?{sas-token}"
```

Microsoft's storage SDKs (Python, JavaScript, C# and others) work the same way: container `konstruktdataimporter`, blob name `{configuration-key}.{extension}`, SAS token as credential.

Files uploaded to storage are processed in the background and can be uploaded in parallel. Results are e-mailed to the configured recipients and logged. Uploaded files are archived and can be downloaded from the log afterwards.

!!! note "Older importer setups"
    Earlier setups authenticated to the storage account with separate directory credentials and configured files outside the tenant. Current setups create the SAS token and configurations inside the tenant.

### SFTP

The SFTP server is `sftp.konstrukt.app`, port `22`. Files are named the same way as in the storage container, `{configuration-key}.{extension}`, and the configuration key decides which import runs. Files uploaded to the root (`/`) are imported automatically. `/inbox` is also read, for clients that cannot write to the root. The SFTP account under Integration → Accounts shows the server, port, host-key fingerprint (*Fingeravtryck*), user ID and password. Files received over SFTP or Azure storage can be downloaded afterwards, to check what was imported.

## File formats

| Format | Extension | Separator |
|---|---|---|
| CSV | `.csv` | Semicolon `;` |
| Text / TSV | `.txt`, `.tsv` | Tab |
| Excel | `.xlsx` | Sheet (select the sheet in the GUI or with `sheetName`) |

Files are flat tables with column headers in the first row. Encoding is detected in most cases; UTF-8 is the safe choice. Use `.` as decimal separator and no thousands separator.

### Value type files

One column per dimension (identified by dimension ID or name) and one column for the value:

```
Month;Amount
202401;10.0
202402;12.2
202403;12.9
```

### Dimension files

One column `Code`, optionally `Name`, and any number of property columns (identified by property ID, code or name). A property **omitted** from the file is not updated; a property column with an **empty cell** clears the value.

```
Code;Name;Active
202401;Jan 2024;0
202402;Feb 2024;0
202403;Mar 2024;1
```

### Dimension property files

Columns `Code` and `Name` only.

## Import in the GUI

Value types have an import wizard in their toolbar. It uses the same mechanism as the import APIs.

| Step | Content |
|---|---|
| 1. Import type | Full import (*Full import*): add new rows and update existing · Only new data (*Endast ny data*): add new rows only · Only update (*Endast uppdatering*): update existing rows only |
| 2. Column mapping | The file's columns with a target selector under each, and a numbered preview of the rows |
| 3. Settings | Duplicate handling — Sum, Average, Min, Max, or Fail on duplicates (*Summa · Genomsnitt · Min · Max · Misslyckas om dubbletter*) — and Create new dimension values (*Skapa nya dimensionsvärden*) |

The calculation ID cannot be mapped: provenance can be read out, not imported. A file exported from a value type can be imported back unchanged.

## Import semantics

All import surfaces share the same operations:

| Operation | Effect | GUI | MCP tool |
|---|---|---|---|
| Insert | Add new rows only | Only new data | `insert_*_data` |
| Update | Update existing rows only | Only update | `update_*_data` |
| Upsert | Add new and update existing; never delete | Full import | `upsert_*_data` |
| Replace | **Empty the value type**, then write the rows in the file (value types only) | Replace (*Ersätt*) | `replace_value_type_data` |
| Delete | Delete the rows matching a [delete filter](#delete-filter-files), for example all data for a period | Delete (*Ta bort*) | `delete_value_type_data` |
| Configured import | Run a pre-defined import configuration | — | `import_data(key)` |

Duplicate rows (same dimension values) are aggregated with `Sum`, `Min`, `Max`, `Avg`, `Concatenate`, or rejected (`Disabled`). Missing dimension values and property values can be created automatically. Notification e-mails can go to the default recipients, to none, or to a list.

Imported values are stored with edit state *suggested* and their origin is logged; see [Traceability](traceability.html). Import writes are kept in history like any other change.

!!! agent "For agents"
    Prefer `import_data(key)` when an import configuration exists — it applies the administrator's mode and options. Use `replace_value_type_data` only with explicit user confirmation, since rows missing from the payload are deleted. `fieldMappings` are index-based and comma-separated (empty entry = skip column); value type files map `Value` plus dimension IDs, dimension files `Code`, `Name` and property names. Set `dataOrigin` to a label that identifies your integration in the log. Details in [AI agents and MCP](ai-agents.html#data-import-parameters).

## Managed integrations

Besides the self-service transports above, Konstrukt offers **managed integrations**, which Konstrukt builds and operates. A managed integration runs as a job with three steps:

```flow
Extract | connectors fetch data from source systems -> Transform | shape the data for the model -> Load | through import configurations
```

- **Extract.** One or more connectors fetch data from the source systems. One job can combine several sources, and several companies or accounts in the same source system.
- **Transform.** The data is reshaped to match the model's dimensions and value types.
- **Load.** Each resulting table is loaded through an import configuration, which decides the target and import mode, as for the other transports. One job can write any number of tables.

Jobs appear under Integration → [Jobs](#jobs), where customers can run them. Their imports appear in the [Logs](#logs).

### Available connectors

The catalogue grows continuously. New source systems, and new endpoints in existing ones, can be added.

| Connector | Type | Data sets |
|---|---|---|
| Microsoft SQL Server | Database | Any table or query |
| MySQL | Database | Any table or query, including source systems that offer a MySQL analytics database, such as Kleer |
| Google BigQuery | Data warehouse | Complete tables |
| Fortnox | Accounting | Accounts, vouchers, customers, suppliers, invoices, supplier invoices, invoice and supplier payments, contracts, cost centres, employees, expenses, salary transactions, financial years |
| Microsoft Dynamics 365 Business Central | ERP | Companies, customers, vendors, accounts, dimensions and dimension values, general ledger entries with dimensions. Custom APIs are supported |
| Oracle NetSuite | ERP | Journal entries, transactions, accounts, departments, classes, locations, subsidiaries, customers, vendors, employees, budgets, currencies, accounting periods, consolidated exchange rates, items, custom segments; any SuiteQL query |
| Xero | Accounting | Accounts, budgets, contacts, currencies, invoices, organisation, payments, prepayments, purchase orders; profit and loss and balance sheet reports |
| QuickBooks Online | Accounting | Accounts, classes, locations, invoices, bills, journal entries; profit and loss and balance sheet reports |
| 24SevenOffice | Accounting | Accounts, customers, transaction lines, account balances, currencies, organisations, products and categories, bank accounts and transactions, taxes, transaction types, sales orders and types |
| Procountor | Accounting | Companies, currencies, dimensions, fiscal years, persons, invoices, ledger receipts, journals, ledger accounts, payments, products, general ledger |
| Tripletex | Accounting | Companies, contacts, currencies, departments, employees, accounts, accounting periods, projects, customers, suppliers, invoices, ledger, postings, vouchers, supplier invoices |
| Fennoa | Accounting | Customers, dimensions and dimension types, sales invoices, accounts, VAT codes, periods, locking periods, budgets, ledger, opening balances |
| Fiken | Accounting | Companies, accounts, account balances, contacts, products, invoices, sales, purchases, journal entries, transactions, bank accounts and balances, offers, inbox documents, projects |
| Kleer | Accounting | Vouchers with details, and an expanded general ledger with every transaction reference and the instance-specific code parts; dimensions, clients, client projects, activities, events, users, company information; a derived forecast of future supplier payments based on payment history. Data can also be read from Kleer's analytics database (*Analytics SQL*) through the MySQL connector, which is faster than the API for large volumes |
| Björn Lundén | Accounting | Accounts, articles, cost bearers, cost centres, financial years, projects, journals |
| RamBase | ERP | General ledger, ledger accounts and account groups, report account groups, dimension definitions, finance projects, departments, companies, customers, sales invoices, suppliers, supplier invoices, employees |
| Rillion One | AP automation | Supplier invoices, filtered by status and change date |
| HubSpot | CRM | Deals, companies, contacts, products, line items, owners, deal pipelines; search and associations |
| Pipedrive | CRM | Deals with products, organisations with custom fields, persons, stages, users |
| Upsales | CRM | Subscriptions, products, companies, orders |
| Younium | Subscription billing | Invoices, measurements, sales orders, units of measurement, products, accounts, subscriptions, usage, reports, insights metrics |
| Revny | Subscription billing | Customers, subscriptions, products, invoices |
| Plock.io | Subscription billing | Accounts, subscriptions, invoices |
| Blikk | Time and project | Time reports, projects, contacts, time codes, payment plans, time articles, activities, cost centres, users |
| Hailey HR | HR | Employees, employments, casual employments, salaries, salary types, departments, legal entities, cost centres, business areas, locations, titles, teams, custom fields, absence per employee and day |
| Zendesk | Customer support | Tickets, organisations, ticket fields |

!!! agent "Pitfalls for agents"
    Destructive import semantics (`replace` deletes missing rows, `Replace` mode on access imports) are summarised in the [Agent guide](agent-pitfalls.html#destructive-operations).

## Related

- [APIs and SQL access](apis.html)
- [Security and access](security.html)
- [Data model](data-model.html)
- [Traceability](traceability.html)
- [AI agents and MCP](ai-agents.html)
