# Konstrukt documentation > Technical documentation of the Konstrukt planning platform — its data model, calculation engine, apps, security, integration and APIs — plus a separate best-practice section on the ready-made templates. Konstrukt is a low-code platform for financial planning, forecasting, reporting and other FP&A and finance-operations processes. You model your business as **plans** containing **dimensions**, **value types** and **calculations**, and build spreadsheet-like **apps** on top of them for the people who plan and report. This site has these parts: - **Platform documentation** describes the platform itself: its concepts, how data is stored and versioned, how calculations are written and run, how apps are configured, and how you secure, integrate and administer a tenant. It uses neutral examples and describes no particular model. - **Best practice** collects working rules and known pitfalls for building models, including a guide for AI agents. ## Platform
OverviewWhat the platform is for and how its parts fit together. Core conceptsTenant, plan, dimension, property, value type, reference, calculation, app. ArchitecturePlans as building blocks, the engine, history and interfaces. Data modelDimensions, properties, value types, precision, limits and line states. References and historySnapshots, point-in-time references and the full change history. CalculationsGuided and script calculations, dependency graph, run order. KexThe query and scripting language: clauses, joins, writes, variables, windowing. AppsDatasets, workspaces, components, actions and the configuration format. Security and accessRoles, groups, access lists, row-level access and SSO. Hosting and operationsSwedish hosting, tenant isolation, encryption, backup, release tracks. Data integrationImport configurations, file transports, REST and the import wizard. APIs and SQL accessREST API, the Kex endpoint and external SQL views. AI agents and MCPThe built-in MCP server, its tools and resources.
## Best practice
Agent guideWorking rules and known pitfalls for AI agents, per area.
## Auto-generated references Some reference material is generated from the product and kept up to date automatically. This site links to it instead of duplicating it — see [Reference documentation](reference-docs.html). | Reference | Covers | |---|---| | [REST API](https://acme.konstrukt.app/api/documentation/index.html) | Endpoints, request and response schemas | | [Kex reference](https://acme.konstrukt.app/api/documentation/kex) | Full Kex language reference and function list | | [Kex Lite reference](https://acme.konstrukt.app/api/documentation/kex-lite) | The expression language used in app datasets | ## For AI agents !!! agent "Reading this site as an agent" - [`llms.txt`](llms.txt) lists every page with a one-line summary. [`llms-full.txt`](llms-full.txt) contains the whole site as one Markdown document. - Every page has a Markdown source under `md/.md`, linked from the page header. - Read the [Agent guide: pitfalls and learnings](agent-pitfalls.html) before writing Kex, app configuration or data. - To work in a live tenant, connect to its MCP server; see [AI agents and MCP](ai-agents.html). The server's own resources (`konstrukt://resources/kex/*`, `konstrukt://TenantInstructions.md`) and the app-config schema from `get_app_config_schema` take precedence over this site for tenant-specific conventions. Back to [www.konstrukt.se](https://www.konstrukt.se) ↗ --- # Platform overview > What the Konstrukt platform is intended for, its main building blocks and how data flows from source systems through models and calculations to apps, reports and integrations. Konstrukt is a multi-tenant, browser-based platform for building planning and finance-operations solutions without writing application code. A solution is built from configuration: a data model, calculation scripts and app definitions. The platform provides storage, versioning, calculation scheduling, access control, the end-user interface and the integration surfaces. ## Intended use The platform is built for processes where people and systems enter, adjust, calculate and report numbers over several dimensions. Typical examples: - budgeting, forecasting and scenario planning; - personnel and payroll-cost planning; - revenue, agreement and recurring-revenue planning; - project and investment planning; - consolidation, eliminations, KPI reporting and month-end close support; - operational registers that feed financial calculations, such as agreements, projects or units. The same building blocks serve all of these. What differs between solutions is the model — the dimensions, value types, calculations and apps configured in each tenant. Konstrukt delivers ready-made models as templates. ## Building blocks | Building block | Role | Documented in | |---|---|---| | Tenant | An isolated customer environment with its own host name, users and plans | [Concepts](concepts.html#tenant) | | Plan | A complete data model: dimensions, value types, calculations, apps | [Concepts](concepts.html#plan) | | Dimension and property | Categorical axes (account, department, month) and their attributes | [Data model](data-model.html) | | Value type | A measure stored over a set of dimensions | [Data model](data-model.html#value-types) | | Reference | A stored or point-in-time version of plan data | [References and history](references-history.html) | | Calculation | A Kex script that derives and writes data automatically | [Calculations](calculations.html) | | Kex | The query and scripting language | [Kex](kex.html) | | App | The end-user interface: datasets, workspaces, components, actions | [Apps](apps.html) | | Access control | Roles, groups, access lists, row-level access | [Security](security.html) | | Integration and APIs | Imports, REST API, SQL views, MCP server | [Integration](integration.html), [APIs](apis.html), [AI agents](ai-agents.html) | ## How data flows ```flow Source systems | ERP, payroll, CRM, files -> Import | configurations, API, MCP -> Plan data | value types over dimensions -> Calculations | Kex, run on change -> Apps | tables, charts, actions Users | entry, spreading, actions -> Plan data | edits are versioned -> Calculations | dependants recalculated -> References | snapshots for comparison Plan data -> External access | SQL views, REST, MCP -> BI and downstream systems ``` 1. **Data enters** through configured imports (files, REST, storage), from users typing in apps, from actions, or from agents through the MCP server. 2. **Every change is versioned.** The platform keeps the full history of value-type data, so any earlier state can be queried. See [References and history](references-history.html). 3. **Calculations run automatically.** When data a calculation reads changes, the calculation and everything that depends on it is recalculated in dependency order. See [Calculations](calculations.html). 4. **Apps present and collect data.** Apps show datasets through tables, charts and metrics, let users enter and spread values, and run actions that execute Kex scripts. See [Apps](apps.html). 5. **Data leaves** through SQL views, the REST API, exports from apps and the MCP server. See [APIs and SQL access](apis.html). ## Configuration, not code Everything in a solution is configuration held in the tenant: - The data model is defined in the admin interface or through the MCP server. - Calculation logic is written in [Kex](kex.html), either directly or through the guided calculation editor, which generates Kex. - Apps are defined through the app editor. Underneath, each app is an XML configuration, which can also be read and written as JSON through the MCP server. See [App configuration](app-config.html). Because the whole solution is data, it can be copied between plans, inspected by agents and changed without deploying software. ## Interfaces at a glance | Interface | Audience | Purpose | |---|---|---| | End-user app interface | Planners, managers, controllers | Enter, review and report data | | Admin interface | Solution builders | Model, calculations, apps, security, integrations | | Kex console | Solution builders | Run ad-hoc Kex queries and scripts | | REST API | Integrations | Import, execute Kex, manage entities | | External SQL views | BI tools, data warehouses | Read plan and reference data with SQL | | MCP server | AI agents | Read and change the model and data with tools | ### Interface language The interface is available in **Swedish and English**, and more languages can be added. Each user chooses a language under **Settings** (the gear icon), see [Using apps](end-user.html#user-settings). All system texts follow the chosen language. Names defined in the model — dimensions, dimension values, value types, apps, workspaces, actions and so on — are stored as entered and are not translated. This documentation uses the English interface labels and concept names, and gives the Swedish label in parentheses where it helps users of the Swedish interface, for example **Initial values** (*Initialvärden*). The [glossary](glossary.html) lists both. ## Related - [Core concepts](concepts.html) - [Architecture](architecture.html) - [Glossary](glossary.html) --- # Core concepts > Definitions of the platform's core objects — tenant, plan, dimension, dimension value, property, value type, reference, history, calculation, Kex, dataset, app, workspace, component, action and access list — and how they relate. This page defines the objects the rest of the documentation builds on. Each definition links to the page that covers the object in detail. ```flow Tenant -> Plan | model container -> Dimensions | + properties -> Value types | measures over dimensions Plan -> Calculations | Kex scripts -> Value types | written data Plan -> Apps -> Datasets | value types + dimensions -> Workspaces -> Components | tables, charts, metrics, actions ``` ## Tenant A tenant is one customer environment. It has its own host name (`https://{tenant}.konstrukt.app`), users, plans, integrations and settings, and its data is isolated from other tenants. Each tenant serves its own REST API, SQL views and MCP server. ## Plan A plan is the top-level container for a complete data model: dimensions, value types, calculations and apps. A plan is **active** or **archived**; only archived plans can be deleted. A plan can be created as a copy of another active plan. One plan in each tenant is the **default plan**. Scripts run outside a plan context — from the console, the REST API or the MCP server — are resolved in the default plan, and this includes the target of `write` statements. A calculation runs in the context of the plan it belongs to. Data from other plans is read with `from plan `; this changes where data is read from, not where it is written. `use plan ` switches the context for the rest of a script, including write targets. Solutions usually consist of several plans with distinct roles — for example one for shared master data, one for planning and one for reporting — that read from each other. See [Architecture](architecture.html#plans-as-building-blocks). !!! note "Plan versions" Plans are either **v2** (current) or **v1** (legacy). v1 plans exist only on some older tenants. This documentation describes v2. ## Dimension A dimension is a categorical axis, such as `Account`, `Department`, `Employee` or `Month`. Its members are **dimension values**, each with a **code** and a **name**. A dimension belongs to a plan and can be used by any number of value types in that plan. Time is modelled as ordinary dimensions, such as `Month` with codes like `202601`. ## Property A property (*egenskap*) is an extra column on each value of a dimension: `Department.Region`, `Employee.StartDate`, `Account.Type`. A dimension can have any number of properties. A property can be **bound to another dimension**, so that its values are values of that dimension. This creates hierarchies and mappings, such as `Account → AccountGroup`. Properties can hold several values, separated by `;`. See [Data model](data-model.html#properties). ## Value type A value type (*värdetyp*) is a measure: one value column — number, date or text — stored over zero or more dimensions. `Amount` over `Account × Department × Month` is a three-dimensional cube; `Price` over `Product` is one-dimensional; a value type with no dimensions holds a single value. Think of it as a fact table with exactly one value column. See [Data model](data-model.html#value-types). Each stored row records who or what wrote it: a user, an import or a calculation. This is its line state (see [Line states](data-model.html#line-states)). ## Reference A reference (*referens*) is a named version of plan data kept for comparison, such as "Budget 2026 approved" or "Forecast March". It is either: - a **data reference**, which stores its own copy of the data; or - a **point-in-time reference**, which stores only a timestamp and reads the plan's history as of that moment. See [References and history](references-history.html). ## History Every change to value-type data is kept. Any earlier state of the data can be queried with `from history `. History underlies point-in-time references, auditing and traceability. See [References and history](references-history.html#history). ## Calculation A calculation (*beräkning*) is a saved Kex script that reads dimensions and value types and writes to one or more value types. Calculations run automatically when the data they read changes. The platform orders them by a dependency graph derived from what each one reads and writes. Calculations can be authored in a guided editor or as free script. See [Calculations](calculations.html). ## Kex Kex is Konstrukt's query and scripting language, for reading, filtering, joining, aggregating and writing data. It is used in calculations, actions, the Kex console, the REST API and the MCP server. App datasets use a lighter expression dialect, Kex Lite. See [Kex](kex.html). ## App An app is an access-controlled end-user interface that belongs to one plan. It consists of: - **Datasets**: selections of value types and dimensions with expressions, initial values, locked values and row-level access. See [Datasets](datasets.html). - **Workspaces**: pages with filters, data-type selection and a layout of components. See [Workspaces and components](components.html). - **Components**: tables (grids), text, metrics, charts and actions placed in a workspace. - **Actions**: forms that collect input and run a Kex script, for example "add employee" or "close month". See [Actions](actions.html). ## Access control - Users belong to **groups** and hold **roles**. - **Access lists** restrict which dimension values a user may see or edit. - Apps and datasets can be restricted to specific users and groups. - A value type can be marked sensitive, which hides its values from users who lack access to it. See [Security and access](security.html). ## How the objects relate | Object | Belongs to | Identified by | |---|---|---| | Plan | Tenant | Integer ID | | Dimension | Plan | String ID, e.g. `Account` | | Property | Dimension | Integer ID, plus code and name | | Value type | Plan | String ID, e.g. `Amount` | | Reference | Plan | Integer ID and name | | Calculation | Plan | Integer ID | | App | Plan | GUID | | Dataset, workspace, component | App | ID within the app configuration | ## Related - [Platform overview](overview.html) - [Data model](data-model.html) - [Calculations](calculations.html) - [Glossary](glossary.html) --- # Architecture > How a Konstrukt solution is structured — plans as building blocks, the calculation engine, change history, the interfaces and the hosting model — at a technical, implementation-neutral level. This page describes the platform's architecture at the level needed to design solutions and integrations. It deliberately avoids implementation details that may change between releases. ## Tenant and interfaces Each tenant is served on its own host, `https://{tenant}.konstrukt.app`. The following interfaces all work against the same model and data, and all enforce the same access control: ```flow Browser | end-user & admin UI -> Tenant API -> Plans | model + data Integrations | REST, files, storage -> Tenant API AI agents | MCP at /api/mcp -> Tenant API BI tools | SQL views -> Plans ``` - **Web interface.** The end-user app interface and the admin interface. - **REST API.** Imports, Kex execution and management endpoints, authenticated with OAuth client credentials. See [APIs](apis.html). - **MCP server.** Embedded in the tenant API at `/api/mcp`. Tools are filtered by the caller's privileges. See [AI agents and MCP](ai-agents.html). - **External SQL views.** A read-only SQL surface for BI and data-warehouse tools. See [APIs](apis.html#external-sql-views). - **Integration platform.** Import configurations reachable through files, storage and REST. See [Data integration](integration.html). ## Plans as building blocks A plan is a complete, self-contained model, but it can read data from other plans with `from plan `. Solutions are therefore usually split into plans with distinct responsibilities: ```flow Master data plan | shared dimensions, registers, actuals -> Planning plan | input, drivers, calculations -> Reporting plan | consolidation, KPIs, snapshots ``` | Role | Typical content | |---|---| | Master data | Shared dimensions (accounts, organisation, calendar), registers, imported actuals | | Planning | Input apps, driver value types, planning calculations | | Reporting | Aggregation, consolidation, KPIs, comparisons against references | | Process plans | Separate processes such as close checklists or invoicing, each isolated from the others | This hub-and-spoke structure keeps each plan small and focused. Master data is maintained once. Access and life cycle (copy, archive) can be managed per plan. Calculations in a reading plan re-run when the data they read in another plan changes. !!! note "Cross-plan reads" `from plan ` reads another plan's data using that plan's dimensions. The reading plan must have the dimensions the data uses. See the [agent guide](agent-pitfalls.html#kex) for related pitfalls. ## The calculation engine Kex scripts are compiled to set-based database queries. They are not evaluated cell by cell: - Each value type is stored as its own table, keyed by its dimensions. - A Kex query is translated into SQL over those tables. Joins are resolved from shared dimensions and bound properties. - Results are streamed to their destination: a value type, an API response or an app. - Writes are applied as set operations — insert, update, upsert or replace — scoped to the rows the writing calculation owns. The binding constraint on performance is usually **recalculation propagation**, not the size of a single query. A change to one input value type triggers every calculation that reads it, then every calculation that reads their output, and so on down the dependency graph. The depth and fan-out of that graph, and the grain of the value types it passes through, determine how long a change takes to settle. Solution design should therefore: - aggregate to the grain needed as early as possible; - avoid calculations that read broadly but change little; - keep high-frequency input value types from feeding large parts of the graph unnecessarily. The [dependency graph](calculations.html#dependency-graph) and [run order](calculations.html#run-order-and-status) are visible in the admin interface and through the MCP server. !!! tip "Rule of thumb" Size is best measured in rows per value type and in the number of rows touched per change. Large value types are fine; what matters is how many rows a typical change forces the engine to recalculate. ## History and versioning Value-type data is stored with system-versioned history: every change keeps the previous version with its validity period. This gives: - `from history ` in Kex — the data as it was at any moment; - **point-in-time references**, which are simply named timestamps; - the audit columns on every row: last edited, edited by, and writing calculation; - temporal queries (`for system_time as of`) in the external SQL views. History is **change-based**. Writing a value identical to the stored value creates no new history row. Deleting data removes it from the current state but not from history. See [References and history](references-history.html#history). ## Calculation scheduling Calculations run asynchronously after the change that triggered them. The platform queues affected calculations in dependency order. The admin interface and the MCP tools `get_running_calculations` and `get_all_running_calculations` show calculations that are running or waiting in the queue. Cycles are detected when a calculation is activated. See [Calculations](calculations.html#dependency-graph). ## App runtime An app's configuration is stored as XML. Name and description are held outside the configuration. At runtime the interface does the following: 1. It evaluates a dataset's value types, expressions, initial values and access filters on the server. 2. It sends the resulting rows to the browser. 3. Component-level filters, grouping and pivoting are applied in the browser. Edits are written back to value types through dataset writeback rules and then trigger calculations like any other change. See [Apps](apps.html). ## Hosting and operations The service is hosted in Sweden, and all platform processing takes place there. Each tenant has its own database. Backend services are shared and stateless apart from caching. Data is encrypted in transit and at rest. New versions are rolled out in release tracks (alpha, beta, feature, stable). See [Hosting and operations](operations.html) for sub-processors, backup, availability and releases. ## Related - [Core concepts](concepts.html) - [Calculations](calculations.html) - [References and history](references-history.html) - [APIs and SQL access](apis.html) --- # Data model > Dimensions, dimension values, properties and bindings, value types with format, precision, limits and sensitivity, internal columns, line states, and how data is loaded and traced across the model. A plan's data model consists of **dimensions**, their **properties**, and the **value types** that store measures over dimensions. All three are defined in the admin interface under *Datamodell* (Data model) or through the MCP server, and are visible in the external SQL views. ## Dimensions A dimension (*dimension*) is a list of members — **dimension values** — each with a **code** and a **name**. Codes are the stable identifiers used in data, imports and Kex; names are for display. | Example dimension | Example codes | |---|---| | `Account` | `3010`, `5010`, `7210` | | `Department` | `D100`, `D200` | | `Month` | `202601`, `202602` | | `Scenario` | `Base`, `Upside` | ### Time as dimensions The platform has no special time axis. Periods are ordinary dimensions such as `Month`, `Quarter` or `Year`, whose properties link them together (`Month.Quarter`, `Month.Year`) and carry calendar attributes. Properties such as a start or end month on a register entry are typically **bound to the period dimension** rather than stored as free dates. Period logic — "months from start to end", "open for planning" — is then expressed as joins and filters on dimension values. ### Dimension editor The dimension editor has these side panels: | Panel | Swedish label | Content | |---|---|---| | Properties | *Egenskaper* | The dimension's properties with IDs; bound properties are badged *Bunden till dimension* | | Connections | *Kopplingar* | Which value types use selected dimension values, with clean-up operations — see [Tracing dimension values](#tracing-dimension-values) | | Integration | *Integration* | Import configurations feeding the dimension; SQL export toggle and query | | Details | *Detaljer* | ID, size (rows), display format (*Visa som*), value types using the dimension, history, description | ### Display formats How a dimension value is shown — code, name or both — is set by a display format. The standard formats are: | ID | Pattern | Example | |---|---|---| | 1 | `{Code} - {Name}` | `3010 - Sales` | | 2 | `{Name} - {Code}` | `Sales - 3010` | | 3 | `{Code}` | `3010` | | 4 | `{Name}` | `Sales` | The display format can be set on the dimension, overridden per dataset in an app (*Visa som*), and chosen per variable in actions. In Kex, `$formatted` returns the formatted text. ## Properties A property (*egenskap*) adds a column to every value of a dimension. Properties are referenced in Kex as `Dimension.Property`, for example `Department.Region`. - **Unbound properties** hold free values: text, numbers or dates. - **Bound properties** hold values of another dimension. `Account.AccountGroup` bound to the dimension `AccountGroup` makes every account point to a member of that dimension. Bindings are how hierarchies, mappings and reporting structures are built. They are also how Kex joins value types that do not share a dimension directly (`include … on Account.AccountGroup`). - **Multi-valued properties** hold several values separated by `;`. In Kex they are aggregated as a string by default and expanded to one row per value with `expand Dim.Prop`. !!! warning "Bindings must not be circular" A property cannot be bound in a way that forms a cycle between dimensions. The same rule applies to calculations: the platform allows no cycles, either in model structure or in logic. ## Value types A value type (*värdetyp*) stores one value column over a fixed set of dimensions. Each combination of dimension values holds at most one value. | Setting | Meaning | |---|---| | ID and name | String ID used in Kex, e.g. `Amount`; display name | | Dimensions | The value type's grain, e.g. `Account × Department × Month` | | Format (*Format*) | Number, percentage, date, date-time or text | | Display precision (*Decimalpresentation*) | Decimals shown, optionally hiding decimals when the value is zero | | Entry precision (*Decimalinmatning*) | Decimals accepted and stored on entry | | Value limits (*Värdegränser*) | Hard minimum and maximum: entries outside are rejected | | Value warnings (*Värdevarningar*) | Soft lower and upper bounds: entries outside are warned about but accepted | | Sensitive (`isSensitive`) | Hides the value, but not the row, from users without access to sensitive data | | Description | Free text, also exposed to agents | !!! note "What is shown is not what is stored" Display precision and entry precision are separate. A value type can accept five decimals and display none. Tables, exports and Kex always work with the stored value. ### Sensitive value types A sensitive value type hides the **measure**, not the **row**. A user without access to sensitive data still sees which dimension combinations exist — for example, which employees have a salary row — but not the values. This allows roles such as a data administrator who maintains dimensions and runs imports without seeing salaries. See [Security](security.html). ### Changing a value type's dimensions Dimensions can be added to or removed from a value type only when it is **empty of both plan data and reference data**. Emptying a value type also removes its **history**, and with it the basis for every point-in-time reference that reads that value type. !!! warning "Irreversible" Emptying a value type to change its dimensions permanently removes its history. Point-in-time references to the value type will no longer return data for the period before the change. If the history matters, create a data reference first, or model the new grain as a new value type. The order of a value type's dimensions can be changed at any time (*Ändra dimensions-sortering*). The order affects default presentation only. ### Value type panels | Panel | Swedish label | Content | |---|---|---| | Dimensions | *Dimensioner* | The value type's grain and the change operation | | Value columns | *Värdekolumner* | Format, precision, limits, warnings, sensitivity | | Integration | *Integration* | Import configurations, SQL export toggle and query | | Details | *Detaljer* | Lineage upstream (calculations writing the value type) and downstream (calculations and apps reading it) — see [Traceability](traceability.html) | A view selector at the top of the value type switches between **plan data** and each **reference** of the plan. In reference view the data is read-only and the summary statistics are recalculated for the reference. ## Internal columns Every row carries system columns, readable in Kex with `X.**` and in the SQL views. | Column | On | Meaning | |---|---|---| | `$name`, `$formatted` | Dimension values | Name, and text formatted by the display format | | `$edittime`, `$editby` | Both | Time of last change and who made it | | `$edittype`, `$editid`, `$rowid` | Both | Change type, change ID and internal row ID | | `$calcid` | Value types | ID of the calculation that wrote the row; empty for user-entered or imported rows. See [Parallel values](#parallel-values) | | `$editstate` | Value types | Line state, see below | ### Parallel values `$calcid` is a hidden system dimension on every value type. For one combination of dimension values, a value type can therefore hold several values side by side: one entered or imported value without a calculation ID, and one value per calculation that writes to it. Every read sums them away: in apps, in exports, in Kex queries and when a calculation reads its sources. Use `expand X.$calcid` to see the parts, or `exclude calculated` to read only the non-calculated part. ## Line states Each value-type row records how it came to exist. The app interface uses this to decide styling and editability. | Code | State | Meaning | |---|---|---| | 0 | Suggested | Imported, or written by a suggestion calculation; a user may overwrite it | | 5 | Zero placeholder | Created when a user edits a cell generated from initial values but the value remains empty or zero | | 10 | Edited | Entered or changed by a user | | 30 | Calculated | Written by a calculation | | 40 | Initial value | Generated placeholder from a dataset's [initial values](datasets.html#initial-values) | Rows written through import tools, the REST API or MCP get line state 0. Line state 10 is set by edits in the app interface. Default [presentation rules](components.html) style suggested, read-only and calculated cells differently, so users can see where each number comes from. ## Loading data Data reaches value types and dimensions through several routes. All of them follow the same import semantics. | Route | Use | See | |---|---|---| | Import wizard on a value type or dimension | Ad-hoc file loads (CSV, TXT, xlsx) | below | | Import configurations | Repeatable loads over files, storage or REST | [Data integration](integration.html) | | App entry and actions | User input | [Apps](apps.html) | | Calculations | Derived data | [Calculations](calculations.html) | | MCP tools | Agent-driven loads and changes | [AI agents](ai-agents.html) | ### Import wizard The wizard on a value type (*Importera*) has three steps: 1. **Import type.** *Full import* adds new rows and updates existing ones. *Endast ny data* (only new) adds rows but updates none. *Endast uppdatering* (only update) updates existing rows but adds none. 2. **Column mapping.** Map file columns to dimensions and the value column, with a preview. The writing calculation (`$calcid`) cannot be mapped: lineage can be read but not imported. 3. **Settings.** - Choose how duplicate rows with the same dimension values are handled: sum, average, min, max, or fail. - Choose whether unknown codes create new dimension values (*Skapa nya dimensionsvärden*). An export from a value type can be imported again as-is. ## Tracing dimension values The **Connections** panel (*Kopplingar*) on a dimension shows, for selected dimension values, every value type in which they occur. From there you can remove the selected values' data from one value type, from a chosen subset in the advanced view, or from all value types. !!! warning "History is retained" Removing data through *Kopplingar* removes it from the current state of the value types. Earlier versions remain in [history](references-history.html#history) and can still be read through point-in-time references and `from history`. !!! agent "Pitfalls for agents" Pitfalls around identifiers, changing dimensions, creating dimension values on import and sensitive value types are in the [Agent guide](agent-pitfalls.html#data-model). ## Related - [Core concepts](concepts.html) - [References and history](references-history.html) - [Traceability](traceability.html) - [Data integration](integration.html) - [Datasets](datasets.html) --- # References and history > How Konstrukt keeps a full change history of value-type data, the two kinds of reference (stored data and point-in-time), how they are queried in Kex and used in apps, and what deleting data does and does not remove. Konstrukt keeps every earlier version of value-type data. **References** give names to versions you want to compare against, such as an approved budget, last month's forecast or the state before a reorganisation. There are two kinds of reference, and the difference between them follows from how history works. ## History All changes to value-type data are kept in system-versioned history. Each version of a row keeps its value and the period during which it was current. - **History is change-based, not write-based.** If a calculation or import writes the same value that is already stored, the row counts as unchanged and no history row is created. A recalculation that produces the same result leaves history untouched. This keeps point-in-time views stable across repeated recalculations, and makes history record *what changed* rather than *what was recomputed*. - History covers user entries, imports, actions and calculations alike. Each version carries its edit time, who made the edit and the writing calculation. ### Querying history In Kex, `from history` reads data as it was at a given moment: ```kex Amount from history '2026-03-31 23:59' Amount from history now() - days(7) Amount from history startofmonth() - milliseconds(1) values only ``` | Form | Behaviour | |---|---| | `from history ` | Value-type data and dimensions as they were at that moment | | `from history values only` | Historical value-type data, but **current** dimensions; historical rows that refer to dimension values since removed are filtered out | `from history` can be combined with `from plan` and `from reference`. The external SQL views support the same idea with `for system_time as of`. See [APIs and SQL access](apis.html#external-sql-views). ## Two kinds of reference | | Data reference | Point-in-time reference | |---|---|---| | Swedish GUI | *Referens* | *Tidpunkts-referens* (clock icon) | | Stores | Its own copy of data | Only a timestamp | | Content | Whatever is written to it: imported, copied or calculated | The plan's data as it was at the timestamp, read from history | | Creation cost | Proportional to the data written | Instant, whatever the data volume | | Can be changed after creation | Its data can be rewritten | The timestamp can be edited, which changes the whole content | | Typical use | Actuals from another system, approved budgets, external benchmarks | "As of month-end close", "last week", "before the reorganisation" | The type is fixed when the reference is created. A data reference cannot be converted into a point-in-time reference, or the other way round. ### Point-in-time references A point-in-time reference is a named query against history. Because it stores only the timestamp: - it is **retroactive**: you can create a reference for a moment that has already passed, with no preparation done at that moment; - editing its timestamp immediately changes its entire content, and nothing needs to be rebuilt; - it costs no storage. The timestamp is entered in local time. The interface shows the time zone explicitly, for example *(lokal tid GMT+02:00)*. In Kex, reading a point-in-time reference with `from reference` is rewritten automatically to `from history values only`. The query diagnostics show this step as *Rewrite point-in-time references*. ### Data references A data reference holds its own rows for any value type in the plan. You can write to it in the following ways: - with Kex: `write to Amount reference "Budget approved" <- …`; - with an import configuration or MCP import tool that targets the reference; - with a calculation that writes reference data. Deleting a data reference deletes its data. ## Creating and managing references References belong to a plan. You create them from the view selector on a value type (*Ny referens*) or from the reference panel on the value-type list: 1. Give the reference a name. 2. Optionally tick *Skapa tidpunkts-referens* (create point-in-time reference) and choose a date and time. The reference is available immediately. The reference panel shows each reference's ID, its timestamp (for point-in-time references), and the value types and apps that use it. Deleting a reference cannot be undone. ## References in Kex Compare plan data with a named reference: ```kex var Budget = Amount from reference "Budget approved" Diff = sum(Amount) - sum(Budget) ``` Write reference data: ```kex write to Amount reference "Budget approved" <- Amount ``` A reference can be addressed by ID or by name. See [Kex](kex.html). ## References in apps In apps, plan data and references are combined in a virtual **data type** dimension. For each workspace you choose which data types are shown: - **Plan data** can be selected, hidden, required or allowed. - **Each reference** can be the default, available or unavailable. This lets a user switch a table between plan data and, for example, last month's forecast, or show them side by side. Initial values can also be generated from reference data. See [Workspaces and components](components.html) and [Datasets](datasets.html#initial-values). Data type codes in the app configuration are `Plan`, `Reference_`, or a custom code. ## What deleting data does to history | Operation | Current data | History | |---|---|---| | Change a value | Updated | Previous version kept | | Delete value-type rows (user, import, Kex, MCP) | Removed | Previous versions kept | | Remove a dimension value's data via *Kopplingar* | Removed from the chosen value types | Previous versions kept | | Delete a dimension value | Removed from the dimension | Previous versions kept; historical rows referring to it are filtered out of `values only` reads | | Empty a value type to change its dimensions | Removed | **Removed** | | Delete a data reference | Its data removed | — | !!! note "Data removal and history" Deleting data does not remove it from history. Earlier versions stay readable through `from history` and point-in-time references for as long as the value type keeps its history. ### Retention History is currently kept until further notice. No specific retention period is guaranteed. Limits may be introduced for storage reasons, and may in future depend on the licence. History can be removed: - for one value type, by emptying it and changing its dimensions, which rebuilds its table; or - for a whole plan, by copying the plan (history tables are not copied), then archiving and deleting the original. Point-in-time references in the copy are then empty. See [Hosting and operations](operations.html#data-retention-in-the-platform). !!! agent "Pitfalls for agents" See the [Agent guide](agent-pitfalls.html#history) for what deletion does and does not remove, and how point-in-time references are rewritten. ## Related - [Data model](data-model.html) - [Kex](kex.html) - [Traceability](traceability.html) - [Workspaces and components](components.html) --- # Calculations > How calculations derive data in Konstrukt — guided and script calculations, sources and joins, destination mapping, the working-set and output previews, diagnostics, write modes including suggestions, the dependency graph, run order, status and cycle detection. A calculation (*beräkning*) is a saved [Kex](kex.html) script that reads value types and dimensions and writes to one or more value types. Calculations are **triggered by data changes**: when any source a calculation reads changes, the calculation runs again, and so does everything downstream of it. You do not schedule calculations or wire triggers; the platform works out the order from what each calculation reads and writes. ```flow Source data | value types, dimensions -> Working set | joined rows -> Formula | per row -> Output | destination grain -> Destination value type ``` ## Two editing modes, one execution model A calculation is always a Kex script. The admin interface offers two ways to edit one: | Mode | Calculation type label | Editing | |---|---|---| | Guided | *Beräkning* | A form: sources, joins, property defaults, formula, destination | | Script | *Skript* | Free Kex in a syntax-highlighted editor | A guided calculation is a Kex script with a model annotation that tells the editor to render it as a form: ```kex @@model: "Formula", version: "1.0" @("Planned salaries") var PlannedSalaries = Salary main Salary check includes … write to SalaryCost <- $result = … ``` `@@model` marks the script as a guided calculation, and `@("Alias")` gives a variable its display name in the form. **Edit in script mode** (*Redigera i skriptläge*) shows the underlying script. !!! warning "Converting to script mode is one-way" If you remove the `@@model` annotation and save, the calculation is permanently a script calculation. It can no longer be opened in the guided editor. Edits in either mode are held locally until you save. While there are unsaved changes, a bar offers **Reset** (*Återställ*) and **Save** (*Spara*). ## The guided editor The guided editor is laid out from top to bottom in this order: 1. **Source data** (*Källdata*). Choose the value types to read. Each source has an alias and a list of included dimensions. For each source you also choose whether it *requires all values in the working set* or *allows missing values*. 2. **Derived join**, stated in plain text, for example *"The system will assume 'Planned salaries' as the main source — a full outer join on 'Month'"*. **Configure** opens the join settings. 3. **Formula** (*Formel*). An expression editor with live validation. 4. **Working-set table** (*Arbetssettabell*). A preview of the rows the formula iterates over. 5. **Destination value type** (*Destinationsvärdetyp*), with dimension mapping, aggregation and write mode. 6. **Output** (*Utdata*). A preview of the result at the destination's grain. Side panels: **Sources** (*Källor*), **Advanced** (*Avancerat*, the join configuration), **Property defaults** (*Standardvärden för egenskaper*) and **Details** (*Detaljer*). ### Joins The Advanced panel sets how each source joins the main source. The GUI terms map to Kex include options: | GUI | Swedish | Kex | |---|---|---| | Main | *Main* | `main` | | Inner | *Inre* | `include` | | Full outer | *Fullständig yttre* | `include all` | | Left | *Vänster* | `include optional` | | Join on bound property | *Lägg till bunden egenskap* | `on Dimension.Property` | The main source contributes all its dimensions automatically. For other sources, you tick dimensions per source. A dimension that does not exist on the main source is flagged *(Dimension finns ej i Main)*. ### Aggregation of sources This is a two-stage process that is easy to confuse: - **Implicit aggregation, always applied.** Each source is summed (`sum()`) to its own dimensions before the join. A source may hold both user-entered and calculated rows for the same combination, and these are added together first. - **Pre-aggregation, optional.** In the Advanced panel you can explicitly remove dimensions from a source before the join. The source is then aggregated with a chosen function: sum, min, max, average or count. This lowers the source's grain. ### Property defaults A property default (*Standardvärden för egenskaper*) sets a value to use when a property used in the formula is missing, so that the expression does not fail. It corresponds to Kex `default Dimension.Property to `. ### Destination For each dimension of the destination value type you choose where its value comes from (*Från*, "From"): | From | Meaning | |---|---| | *Källa* (Source) | Taken from a source dimension, e.g. `Planned salaries.Month` | | *Kex* | A dynamic Kex expression | | *Fixed* | A constant dimension value | Every dimension of the destination must receive a value. You also set: - **Aggregation** (*Aggregering*): how rows are combined to the destination grain. - **Write mode**: write all values (*Skriv alla värden*) or only non-zero values (*Skriv endast icke-nollvärden*). Writing only non-zero values keeps the destination sparse, which reduces storage and downstream recalculation. ### Working set and output The working-set table and the output are a pair. Together they show how a formula turns input into results: | Table | Grain | Columns | |---|---|---| | Working set | The join grain, before the formula | Dimensions annotated with their source, one value column per source, and the formula result per row | | Output | The destination grain, after formula and aggregation | Destination dimensions and the value, with summary statistics (rows, sum, mean, max, min) | The previews show the first 1,000 rows and report the total count. Both tables support search, per-column filters, sorting, export and full screen. The difference in row count between the two — for example 6,396 working-set rows aggregating to 647 output rows — makes the calculation's grain visible. ### Diagnostics The info icon in the working-set table opens **Diagnostics**. It has two tabs: - **Transformation line**: every step the Kex compiler applies, with the query text before and after each step. - **Script**: the final script. Steps include: - *Initial global query* - *Create semantic query* - *Correct identifier names* - *Check includes* - *Determine product dimensions* - *Rewrite point-in-time references* - *Expand wildcards* - *Exclude calculated data* - *Resolve spreads* - *Resolve includes* - *Rewrite special functions* - *Aggregate scalars* - *Suggest select dimensions* Steps that change nothing are shown as *(No change)*. Diagnostics make Kex's implicit resolution inspectable. They are the main tool for answering "why did this join or aggregation happen?". ## Script calculations A script calculation is a Kex script with at least one `write` statement. The script panel is a line-numbered editor that can be opened in a modal. The output panel shows the last run, or the validation error with the identifier at fault, together with **Diagnostics** and **Retry** (*Försök igen*). Rules for calculation scripts: - A saved calculation must contain a valid `write`. - A calculation can write to several value types. Each `write` is a separate output in the dependency graph. - Inside a calculation, writes affect only the rows that calculation owns: rows whose `$calcid` is the calculation's ID. The first write to a target replaces the calculation's previous rows there, and later writes to the same target in the same run upsert. See [Kex write variants](kex.html#write-variants). - A calculation's own rows are separate from user-entered, imported and other calculations' rows for the same combination. Readers sum them together. ### Suggestions `write suggestion to` writes **suggested values** (line state 0). The user can overwrite a suggestion, and once edited the value belongs to the user: the calculation does not overwrite it again. Suggestions are styled separately in apps (presentation rule key `Suggested`, **Suggested value**, *Föreslaget värde*). Use them for defaults that users are expected to confirm or adjust, such as a start date derived from other data or a proposed budget line. | Variant | Effect | |---|---| | `write suggestion to` | Replace earlier suggestions in the target with the result | | `write insert suggestion to` | Suggest only where no data exists | | `write upsert suggestion to` | Update existing suggestions and add new ones | `suggestion` is valid only in calculations. ## Dependency graph The platform derives a directed graph from what each calculation **reads** (value types and dimensions) and **writes** (value types). A calculation depends on every calculation that writes something it reads. Cross-plan reads are part of the graph. **Show diagram** (*Visa diagram*) draws the graph for the whole plan or for selected calculations. Select rows in the calculation list, or use the row menu on a single calculation. | Element | Meaning | |---|---| | Coloured circles | Calculations, shaded by depth (level) | | Small grey nodes | Value types | | Layout *None* | Force-directed: shows clusters and isolated islands | | Layout *Radial* | Radial arrangement | | Layout *Top-down* | Dependency layers, i.e. the order in which calculations actually run | Hovering over a calculation shows its full contract without opening it: ID and level, name, every source with its condition, and the write target with its dimension mapping. !!! agent "Dependency graph via MCP" `get_calculation_graph` returns per calculation `planId`, `id`, `name`, `isEnabled`, `level`, `references[]` (value types and dimensions read, with plan IDs), `outputs[]` and `dependsOn[]`. Filter with `planIds`, `referencedEntityIds` or `outputEntityIds`. Use it to find everything affected by a change before editing a calculation. ## Run order and status | Status | Swedish | Meaning | |---|---|---| | Ok | *Ok* | Valid and enabled; runs on change and can be run manually | | Invalid | *Ogiltig* | Script validation fails; the calculation cannot run and has no place in the run order | | Inactive | *Inaktiv* | Disabled without being deleted | Validation errors are shown at two levels: a structured list, for example *"The formula expression is not valid"*, and the raw engine message naming the identifier at fault. Graph validation is separate from script validation. A calculation can be valid on its own but, when activated, form a **cycle** with other calculations. The list then shows a cycle marker with a tooltip naming both edges, for example *"Creates a cycle with other calculations: reads from 32 / writes to 82"*. Cycles are not allowed. The Details panel shows: - name, ID and type; - **run time** (*Körtid*) and **run order** (*Körordning*), meaning the level in the graph; - status, last run, last changed by; - tags and description. The row menu offers: go to calculation, activate or deactivate, copy, export, delete. ## Precalculations A precalculation (*förberäkning*) is an intermediate calculation that writes to no value type and is used only as a source in another calculation. It is referenced in scripts as `Calc`. Precalculations remain supported. For new work, prefer Kex variables inside the calculation, or an explicit value type where the intermediate result is useful in its own right. ## Flows Flows (*Flöden*) are saved, parameterised Kex scripts run **on demand**, not on data change — for example "start a new forecast round" with parameters for the previous forecast and the start period. - **Running a flow.** The *Flows* side tab on a plan lists the plan's flows. Choosing one opens a run form with its parameters, **Run** (*Kör*) and the flow's status. - **Editing flows.** **Flow editor** opens the plan's flow list (name, status, last run) with **New flow** (*Nytt Flow*). A flow has a name, ID, description, parameters and a script, plus panels for parameters and details. - **Parameters.** Each parameter has a name, an ID used in the script (for example `@startperiod`), a type and a description. | Parameter type | Swedish label | |---|---| | Decimal | *Decimal* | | Integer | *Heltal* | | Text | *Text* | | Date | *Datum* | | Date and time | *Datum och tid* | | Boolean | *Booleansk* | | Dimension value (of a chosen dimension) | *Dimensionsvärde* | | Plan | *Plan* | | Reference | *Referens* | **Who uses flows.** Flows are for planners and other administrators, to automate larger operations that should run at a chosen moment rather than on every change. Examples: - re-flagging many properties at once; - moving or copying data between references and value types; - starting a forecast round: data is fetched from the master data plan once, when the round starts, and then stays locked, instead of following master data continuously. ## Designing calculations - **Aggregate early.** Read sources at the grain you need, and use pre-aggregation or a `var` to drop unneeded dimensions before windowing or joining. - **Write sparsely.** Use *write only non-zero values*, or `having $result != 0`, where zeros carry no meaning. - **Keep the graph shallow and narrow.** Every level adds recalculation latency to changes that pass through it. See [Architecture](architecture.html#the-calculation-engine). - **Name for the graph.** Names that state the source and target make the graph and lineage panels readable. - **Validate before saving.** Use the output preview, the working-set table and Diagnostics. Agents should call `validate_script` first. !!! agent "Pitfalls for agents" Known pitfalls when changing calculations — the one-way script conversion, cycles detected on activation, invalid calculations and recalculation load — are in the [Agent guide](agent-pitfalls.html#calculations). ## Related - [Kex](kex.html) - [Traceability](traceability.html) - [Architecture](architecture.html) - [Data model](data-model.html#line-states) --- # Kex language > A structured introduction to Kex, Konstrukt's query and scripting language — statements and variables, clause order, implicit resolution, joins, write variants, dimension operators, windowing, Kex Lite — with links to the auto-generated full reference. Kex is Konstrukt's language for reading, filtering, joining, aggregating and writing data. The same language is used in calculations, actions, flows, the Kex console, the REST endpoint `/api/v1/kex` and the MCP tools. You state *what* you want in terms of value types, dimensions and properties, and the engine works out the joins and grouping. Kex compiles to set-based SQL, one table per value type (see [Architecture](architecture.html#the-calculation-engine)). This page is an orientation. The complete language reference, with every function and option, is generated from the product: **[Kex reference ↗](https://acme.konstrukt.app/api/documentation/kex)**. !!! agent "Authoritative sources for agents" The MCP server exposes the reference as resources: `KexOverview`, `KexDocumentation` (full reference), `KexGuidelines` (rules for generated scripts), `KexKnowledge` (pitfalls) and `KexScriptExamples`, under `konstrukt://resources/kex/`. Load the overview first and the full reference when needed. Always run `validate_script` before `execute_write_script`, and never retry a failed write script automatically. ## A first query ```kex Amount where Month.Year == "2026" ``` This returns `Amount` for 2026 with all of its dimensions projected automatically. Aggregation to fewer dimensions is expressed by naming the dimensions you want: ```kex Total = sum(Amount), Department where Month.Year == "2026" ``` Keywords are lowercase and case-sensitive. Whitespace is not significant. ## Statements and variables A script is one or more statements. | Statement | Purpose | |---|---| | `var X = ` | A table variable: dimensions plus one or more value columns. Later columns are addressed by alias. | | `scalar X = ` | A single value (the first row); extra columns as `X.Alias` | | `dim X = ` | A custom dimension from unique first-column values, or `dim X = X from plan 10` to import one | | `evaluate [Name] = ` | A named result set; bare expressions are also evaluated | | `write … <- ` | Write to a value type, see [Write variants](#write-variants) | Static data can be written inline: ```kex var Rates = (Currency, Rate) { ("EUR", 11.2), ("USD", 10.4) } dim Months = {202601, 202602, 202603} dim Weeks = range('2026-01-05', '2026-03-30', days(7)) ``` `range(start, stop, step?)` generates numeric, date and time, or single-character sequences. The start and stop values are inclusive. A variable can shadow a value type of the same name. Dimensions cannot be shadowed. Start variable names with a capital letter to avoid clashing with future keywords. ## Clause order Clauses must appear in this order: | # | Clause | Examples | |---|---|---| | 1 | Select expressions | `Total = sum(Amount), Department` · `only` suppresses automatic dimension projection | | 2 | Include options | `main X`, `include X`, `include optional X`, `include all X`, `include cross X`, `on D.Prop`, `default D.Prop to C`, `expand D.Prop`, `exclude calculated` | | 3 | Filter | `where …` | | 4 | Grouping and order | `group by`, `having`, `order by` | | 5 | Data source | `from plan `, `from reference `, `from history [values only]` | | 6 | Output options | `limit n`, `check duplication`, `check includes`, `check dim values`, `apply dim formatting`, `apply dim filtering` | ## Implicit resolution Most scripts need few include options, because Kex infers them: - **Dimensions** of the main table are projected automatically. - **The main table** is chosen heuristically: value tables first, then dimensions, then the table sharing the most dimensions, then the first one named. - **Joins** are resolved from shared dimensions and bound properties. - **Grouping and ordering** follow from the selected dimensions. `check includes` turns off much of this inference and requires explicit includes. Guided calculations use it. The calculation editor's [Diagnostics](calculations.html#diagnostics) shows every resolution step applied to a query. ### Identifier expansion | Form | Meaning | |---|---| | `X.*` | All columns of `X` | | `X.**` | Including internal columns (`$calcid`, `$edittime`, …), useful for troubleshooting | | `Amount.*.*` | Multi-step expansion through properties | | `Region.*.**$name*` | Expansion filtered by name pattern | ## Joins | Include option | Join | Guided editor term | |---|---|---| | `main X` | Driving table | Main | | `include X` | Inner | *Inre* | | `include optional X` | Left outer | *Vänster* | | `include all X` | Full outer | *Fullständig yttre* | | `include cross X` | Cross product (applied before `all`) | — | | `… on Dim.Prop` | Join on a bound property | *Bunden egenskap* | `default Dim.Prop to ` supplies a value where a property is missing. ## Write variants ```kex write [[insert|upsert] suggestion | insert | update | replacement] [to] Target [reference ] <- ``` | Variant | Outside a calculation | Inside a calculation | |---|---|---| | *(no keyword)* | Upsert: update matching rows, insert new ones | Replace the calculation's own rows in the target | | `insert` | Only rows without data | Same, within the calculation's rows | | `update` | Only existing rows; no inserts or deletes | Same | | `replacement` | Replace target contents with the result, deleting non-matching rows. **Calculated rows are not deleted.** | Same as no keyword | | `suggestion` | Not allowed | Write suggested values the user may overwrite, see [Suggestions](calculations.html#suggestions) | | `insert suggestion` | Not allowed | Suggest only where no data exists | | `upsert suggestion` | Not allowed | Update and add suggestions | | `… reference ` | Write reference data instead of plan data | Same | Rules that follow from this: - **Row ownership.** Inside a calculation, writes touch only rows carrying that calculation's `$calcid`. The first write to a target clears the calculation's earlier rows; later writes to the same target in the same run upsert. - **Write target.** The target is resolved in the plan the script runs in: the calculation's plan, or the [default plan](concepts.html#plan) for console, API and MCP scripts. `from plan` in the query does not move the target; [`use plan`](#use-plan) does. - **Selected columns.** In a write, the value column is named `$result`, and each dimension of the target must be assigned, either implicitly or with `Dim = …`. ```kex write to SalaryCost <- $result = sum(Salary) * (1 + EmployerRate), Employee, Month having $result != 0 ``` ### Excluding calculated rows `exclude calculated`, in the include-option position, leaves calculated rows out of the query, so that only user-entered and imported rows are read. It is often used together with `write replacement` in actions, which rewrite user data without touching calculated data: ```kex write replacement to Amount <- Amount.* exclude calculated where not (Department in @Department && Month in @Month) ``` The documented equivalent is `expand X.$calcid where X.$calcid == null`. Suggested values carry no calculation ID, so `exclude calculated` still returns them. - The option can stand anywhere among the include options (`main`, `include`, `default`) and directly after the select list, but not after `where`, `check` or `from plan`. - It applies to every table in the query, not only to `main`. - It works in plain reads too: `count() only main Amount exclude calculated`. - It cannot be combined with a reference to `$calcid` in the same query: *The 'exclude calculated' option cannot be used when the '$calcid' column is referenced elsewhere in the query.* - There is no `exclude suggestions`. ## Dimension operators on variables | Operator | Effect | |---|---| | `dimadd Dim = ` | Add an existing plan dimension to a variable that lacks it, enabling joins across value types that share no dimension. Filters apply to the added dimension. | | `dimset Dim = ` | Override a dimension already on the variable. The expression affects projection and grouping; **filtering still uses the original dimension**. | Non-existent dimension codes are filtered lazily, only when the dimension is referenced. `check dim values`, `apply dim filtering` or re-assignment force the check. ## Expressions - **Constants:** - text: `"abc"` - numbers - dates and times: `'2026-05-17'`, `'12:34:22'`, `'2026-05-17 12:00'` - interpolated strings: `` `Total {x}` `` - **Arithmetic and text:** `^ * / + -`, `&` concatenation, `??` coalesce. - **Comparison:** `==`, `!=` (alias `<>`), `<`, `>`, `<=`, `>=`. - **Pattern matching:** `=~` and `!~` with `*` and `?`. - **Membership:** `in {…}` and `not in {…}`, also on multi-valued properties and on tuples `(a, b) in {(…)}`. - **Logic:** `&&`, `||`, `not`. - **Null semantics.** `x == null` is true for null. `x == y` is false when both values are null. - **Multi-valued text.** Values are separated by `;`. Escape a literal `;` as `\;` and a backslash as `\\`. ### Functions Kex has about 95 functions. See the [reference ↗](https://acme.konstrukt.app/api/documentation/kex) for signatures. | Category | Examples | |---|---| | Conditional | `if`, `case`, `switch` | | Conversion | `convert(expr, type)` (null on failure), casts `int()`, `string()` (throw on failure) | | Date and time | `dateadd`, `datediff`, `now`, `today`, `startofmonth`, `endofyear`, `year`, `month`, `week`, `yyyymm`, `days()`, `months()` | | Aggregate | `sum`, `min`, `max`, `average`, `count`, `distinctcount`, `product`, `concatenatex` | | Window | `first`, `last`, `lag`, `lead`, and any aggregate with window bounds | | Text | `concatenate`, `left`, `right`, `substring`, `length`, `upper`, `lower`, `substitute`, `padstart` | | Math | `mod`, `abs`, `floor`, `ceiling`, `round`, `log`, `exp`, `rangemax`, `rangemin`, `coalesce` | | Special | `autodim`, `typeof`, `aggregationof` | ### Windowing ```kex Cumulative = sum(Amount) over all previous rows ordered by (Month) partitioned by (Account) ``` The general form is `FUNC [bound] [ordered by (…)] [partitioned by (…)]`. The bounds are: - `for previous row`, `for next row`, `for row offset N`; - `over N previous rows`, `over all previous rows`, `over N next rows`, `over all next rows`; - `over rows between … and …`; - `over all rows`. A bound includes the current row. A trailing `-` excludes it. Windowed functions do not aggregate; nest them in an aggregate to group. ## Control flow and messages Scripts can branch and stop. This is mostly used in actions, to validate input before writing. ```kex scalar [Code] = makecode(@Employee.Name) within (Employee) if any (Employee == [Code]) { fatal `Employee '{[Code]}' already exists.` } write insert to Employee <- Employee = [Code], $name = @Employee.Name ``` | Form | Condition | |---|---| | `if (cond) { … }` | A condition on scalars only. A value type or dimension gives *Top-level conditions may only reference scalar values.* | | `if any (cond) { … }` | True when at least one row satisfies the condition. The condition can read value types, dimensions, properties and variables, and accepts `where` and `from plan`: `if any (Amount > 200 && Month == "202602" from plan 12)`. `main` and `include` are not allowed inside it. | | `if not any (cond) { … }` | True when no row satisfies the condition | | `else { … }`, `else if [any] (…) { … }` | Alternative branches. Blocks can be nested. | A block may contain `if`, `for`, `for row`, `scalar`, `dim`, `var`, `set`, `fatal` and annotations. **Queries and `write` statements are not allowed inside a block**; branch on the condition, then write after the block, or stop with `fatal`. ### fatal ``fatal `message` `` stops the script, and the message is returned as the error: *There was an error executing the Kex script. Employee 'DemoPerson' already exists.* `fatal` is only allowed inside a block, and the message must be a back-quoted string. `{…}` in the message inserts the value of an expression over scalars: `{[Code]}`, `{Code}`, `{[Code] & "-x"}` or `{1+2}`. A value type or dimension gives *Fatal messages may only reference scalar values.* There are no other message levels; `warning`, `info` and `error` are unknown identifiers. !!! agent "For agents" If a result set is evaluated before the `fatal`, `execute_read_script` returns a generic tool error and the `fatal` message is lost. Test guards in scripts that evaluate nothing before them. ### makecode `makecode(text) within (Dim)` turns a name into a dimension code that is unique in `Dim`. It is used in actions that create dimension values. | Input | Result | |---|---| | `"Account 1"` | `Account1` | | `"lead and trail"` | `LeadAndTrail` | | `"åäö ÅÄÖ Émile"` | `aaoAAOEmile` | | `"a.b-c_d/e & f! (g)"` | `a.b-c_d/e&F!(g)` | | `"Demo Person"`, when `DemoPerson` … `DemoPerson_4` exist | `DemoPerson_5` | | `""` in a dimension with numeric codes | the highest numeric code + 1 | - Whitespace and the characters ``; , ' ? # * ~ { } |`` are removed, and the character after them is upper-cased. Otherwise the case is kept. - `å ä ö é è ü` are transliterated to ASCII. Other letters are kept as they are. - The code is at most 100 characters. - Uniqueness is checked against existing **codes**, ignoring case, not against names. On a clash, `_2`, `_3` … is appended. - Without `within` there is no uniqueness check, and an empty text gives a GUID. - The argument must be a constant string or an `@` input: *Only constant string expressions are supported by the 'makecode' keyword.* `within` takes one dimension. `within (Dim)` looks the dimension up in the plan the script runs in. For a console, API or MCP script in another plan, start with [`use plan`](#use-plan). ### Action inputs In an action script, `@Identifier` refers to an input of the action, character for character, including dots: `@Employee`, `@Employee.Name`, `@Amount.Value`. The value behaves as a constant, so it can be used in `makecode`. Inputs cannot be declared in the script (`set @X = …` is a syntax error). Outside an action, an input gives *Unknown identifier: '@Employee.Name'.* See [Actions](actions.html#variables). ### Editor annotations Guided calculations carry metadata for the calculation editor: - `@@(model: "Formula", version: "1.0")` must be the first statement. `"PreCalcFormula"` marks a precalculation, which ends with a result query and has no `write`. Values must be strings. - `@("Label")` before a `var` or `scalar` is the display label of a source in the editor. Neither affects execution. Removing `@@(…)` and saving turns a guided calculation into a script calculation permanently; see [Calculations](calculations.html). ### use plan `use plan ` or `use plan ""` makes that plan the context for every statement after it: reads without `from plan`, write targets and `makecode … within`. An explicit `from plan` still overrides it for its query. The last `use plan` wins, and statements before it are not affected; a scalar declared earlier keeps its plan. ```kex use plan 12 write to Forecast <- sum(Actuals) * 1.05, Account, Month ``` `use plan` takes a literal, not a variable, and is not allowed inside blocks. An unknown plan gives *No such plan: 9999*. ### set `set` changes a variable that is already declared with `var`, `scalar` or `dim`. The operators are `=`, `+=`, `-=`, `*=`, `/=`, `&=` (text), `^=` and `??=` (assign if null). ```kex scalar Total = 0 for X in sum(Amount), Account { set Total += sum(X) } ``` - `set` never declares: *The 'set' keyword cannot be used to assign undeclared variable 'X'.* - The columns must stay the same: *The 'set' keyword cannot be used to change the columns of variable 'X'.* - On a table variable, `set V = …` replaces all its rows, and `set V += 1000` applies to every row. - A scalar is evaluated when it is first used. A scalar defined from another variable, but first read after a `set` of that variable, gets the new value. ### for and for row `for X in { … }` runs the block once per row of the query; `X` is a one-row table, so read it with aggregates such as `sum(X)` or `min(X.Account)`. `for row in { … }` is the same loop with the variable named `row`. The query can have `where` and `from plan`, and loops can be nested. An empty query runs zero times. The body follows the block rules: `var`, `scalar`, `dim`, `set`, `if`, `for` and `fatal`, but no queries and no `write`. Variables declared in the body are not visible after the loop; use `set` on an outer variable to carry results out. ### spread `spread (Source [evenly | proportionally] over Target)` is an expression that distributes a coarser source down to the grain of `Target`. Without a mode it spreads **proportionally** to the target's values: ```kex var Budget = sum(Qty), Account // 20 for account A1 spread (Budget evenly over Amount) // 10 and 10 over A1's two rows spread (Budget over Amount) // 5 and 15, weighted by Amount 100 and 300 ``` The result has the target's dimensions and can be used in `var`, in a query or as the source of a `write`. Only numeric data can be spread; the source cannot have the same dimensions as the target, or dimensions the target lacks (*Pre-aggregate the source table to exclude any extraneous dimensions.*). ## Pitfalls !!! agent "Known pitfalls" There are known pitfalls when writing Kex — windowing over un-aggregated value types, duplicated rows from custom grouping, cross-plan reads and write targets. They are documented for agents in the [Agent guide](agent-pitfalls.html#kex), with write-specific pitfalls under [Writes](agent-pitfalls.html#writes). ## Kex Lite App dataset expressions use **Kex Lite**, a restricted dialect evaluated per slice of a dataset. The platform generates a context for each expression, for example: ```kex dim Department = $slice.Department limit 1 var Amount = pivot($slice, "Amount") ``` The expression then refers to the dataset's value types and dimensions by name. Writeback expressions use `$input` for the entered value. See [Datasets](datasets.html#expressions) and the **[Kex Lite reference ↗](https://acme.konstrukt.app/api/documentation/kex-lite)**. ## Where Kex runs | Context | Reads | Writes | Notes | |---|---|---|---| | Calculation | Yes | Yes (own rows) | Triggered on source change | | Action | Yes | Yes | Run by a user, with `@Variable` inputs | | Flow | Yes | Yes | Run on demand with parameters | | Kex console | Yes | Yes | Admin tool, see [Administration](administration.html) | | REST `/api/v1/kex/script/*` | Yes | Yes, in the default plan | Separate validate, read and write endpoints, see [APIs](apis.html#kex-endpoints) | | MCP | `execute_read_script` | `execute_write_script` | `validate_script` first, see [AI agents](ai-agents.html) | | Dataset expressions | Kex Lite | Via writeback | See [Datasets](datasets.html) | !!! note "Not in the generated reference" `exclude calculated`, `makecode`, `if` blocks, `fatal`, `@` inputs and the editor annotations are missing from the auto-generated Kex reference. They are described on this page. ## Related - [Calculations](calculations.html) - [Actions](actions.html) - [References and history](references-history.html) - [Reference documentation](reference-docs.html) --- # Kex quick card > The rules and examples an agent needs before writing its first Kex query: clause order, plans, filters, aggregation, joins and windows, with checked examples from the base template. This card is short on purpose, so it can sit in an agent's context at all times. The full language is described in [Kex](kex.html). The function reference is generated from the product at `/api/documentation/kex` on each tenant host. ## Before the first query 1. **Find the plan and the names.** Plan ids differ between tenants, so call `list_plans`, then `list_value_types` and `list_dimensions` with the plan id. Use the ids (`PnL`, `YearMonth`), not the display names. 2. **Read the tenant's own guide if it has one.** Many tenants describe their model and common questions in MCP resources (`list_mcp_resources`). It says which value type answers which question. 3. **Validate, then run.** Call `validate_script` before `execute_read_script`. Read the error, fix that exact thing and validate again. Do not rewrite the query from scratch. ## The shape of a query Clauses must come in this order. Every clause except the first is optional. ```text