> For the complete documentation index, see [llms.txt](https://docs.qvantum-plan.de/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.qvantum-plan.de/dev/cell-data.md).

# Handling Cell Data

With a successfully uploaded planning model definition, a plan consists of a cube with all data cells being empty. In most cases, planners will require initial information as reference for their planning tasks. For example, planning the costs for the next year would usually require information about the planned costs for the rest of the current year (forecast) and actual costs of previous years. Thus, the empty cube should be prefilled with initial data, relevant for this particular plan. The compilation of such initial data is a substantial part of plan preparation. Additionally, export of planned data for further processing is often necessary. The next sections describe how operations for handling data can be performed with the QVANTUM public API.

{% hint style="info" %}
For the equivalent import/export workflow in the QVANTUM Web application's Daten tab, see the German user documentation: [Daten](/user/owner/planung/daten.md) (German). Note that the fine-grained `SINGLE`/`SUBSET`/`LEAVES`/`SUBTREES` filter types described below are only available via the Public API, not in the web application.
{% endhint %}

## Compile Initial Data

For later upload of initial data into a plan, it must be guaranteed that the uploaded data is compliant with the previously uploaded planning model definition. For this purpose, the QVANTUM Public API provides an endpoint for downloading a data template CSV with a structure compliant with the dimension definitions of the planning model. Afterwards, this data template must be prefilled with data vectors, compliant with the structure element definitions for the particular dimensions. This prefilling is usually the result of upstream [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) processes.

### Download Data Template

The endpoint for downloading a data template is available from the links section in a cube resource representation (cf. [Navigating to a cube](/dev/general-information.md#navigating-to-a-cube)).

**Download Cube Data Template**

```sh
curl --show-headers --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_TEMPLATE_LINK}"
```

### Fill Data Template

For the example planning model definition of this tutorial, the following excerpt from a valid sample initial data CSV file shows initial data for cost center `CCT020` (Administration). In particular, it includes actual and forecast costs for both staff costs (cost `CST010`) and energy costs (cost `CST030`). While staff costs went up from 50000.00$ (actual) to 60000.00$ (forecast), energy costs went down from 300.00$ (actual) to 250.99$ (forecast).

**Sample Cube Data File**

```csv
Cost;Cost Center;Scenario;Wert
CST010;CCT020;ACTUAL;50000.00
CST030;CCT020;ACTUAL;300.00
CST010;CCT020;FORECAST;60000.00
CST030;CCT020;FORECAST;250.99
```

## Upload Initial Data

With a valid cube cell data file at hand, initial data can be uploaded to QVANTUM in one single asynchronous API call with its result enabling status polling afterwards.

Unlike in previous requests, the request must specify a MIME type of `application/x.de.qvantum-plan.cube-cell-data`. Two parameters allow to further specify the format of the uploaded file:

* `charset`: the charset to be used. Valid values are `US-ASCII`, `ISO-8859-1`, `UTF-8`, `UTF-16`, `UTF-16BE`, `UTF-16LE`, and `Windows-1252`. If omitted, defaults to `UTF-8`. As for files encoded in UTF-8, any optionally included [BOM](https://en.wikipedia.org/wiki/Byte_order_mark#UTF-8) is safely ignored. If any explicit or implicit charset specification does not match the actual charset of the corresponding file, the upload will fail.
* `decimalSeparator`: the character used as decimal separator in numeric values. May be explicitly specified as hexadecimal character code (e.g. `decimalSeparator=0x2c`) or as character in double quotes (e.g. `decimalSeparator=",")`. If omitted, defaults to dot character (`.`).

{% hint style="warning" %}
Cell data files involving mixed use of different decimal separators are considered invalid and will thus lead to failure upon upload.
{% endhint %}

{% tabs %}
{% tab title="Default Cell Data Upload" %}

```sh
curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:application/x.de.qvantum-plan.cube-cell-data" \
  --data-binary "@${CUBE_DATA_PATH}" \
  "${CUBE_DATA_UPLOADS_LINK}"
```

{% endtab %}

{% tab title="Explicit Decimal Separator and Charset" %}

```sh
curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:application/x.de.qvantum-plan.cube-cell-data;decimalSeparator=0x2c;charset=UTF-16" \
  --data-binary "@${CUBE_DATA_PATH}" \
  "${CUBE_DATA_UPLOADS_LINK}"
```

{% endtab %}
{% endtabs %}

Uploading cube cell data with one of the above requests is asynchronous, i.e. the QVANTUM Public API will immediately send a response including a status polling URL in the `Location` header. In the next step, repeated status polling in possible as described for the model upload (cf. [Upload Status Polling](/dev/model.md#upload-status-polling)). Two additional fields are available for progress tracking:

* `importProgress`: data import progress percentage (0 <= x <= 1).
* `aggregateProgress`: data aggregation progress percentage (0 <= x <= 1).

## Deleting Planning Data

During a planning process it might be useful to delete specific data in a plan. A reasonable request might be: delete all forecast data in the year 2023.

{% hint style="info" %}
Data can only be deleted, when the underlying plan is not running, i. e. it is either paused or ended (cf. [Updating a Plan](/dev/update_workflow.md#plan-status)).
{% endhint %}

Data deletion is possible using an asynchronous request to the `dataDeletions` link in a cube. The API will immediately send a response including a status polling URL in the `Location` header (cf. [Upload Status Polling](/dev/model.md#upload-status-polling)).

**Default Data Deletion**

```sh
curl --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_DATA_DELETIONS_LINK}"
```

{% hint style="info" %}
It should be noted that with a default data deletion request like the above, all data in the cube will be deleted. This default behavior can be overridden with a set of filters, as described in the section [filtering planning data](#filtering-data).
{% endhint %}

### Deleting Filtered Data

The filters described in [Filtering Data](#filtering-data) are passed as query parameters of a data deletion request:

* `inputAllowedFilter`: one of the values described in [Filtering by Input Allowed](#filtering-by-input-allowed)
* `dimensionRestrictions`: a dimension restrictions filter (cf. [Filtering by Dimension Restrictions](#filtering-by-dimension-restrictions)) as JSON array in URL-encoded form, as described for [data exports](#filters-as-query-parameters)

**Deletion of Data With Filters**

```sh
curl --request DELETE \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_CELLDATA_DELETE_LINK}?inputAllowedFilter=ALL&dimensionRestrictions=${dimensionRestrictionsFilterEncoded}"
```

## Exporting Planning Data

With the availability of data collected from user inputs in the context of a given plan, the export of the collected data becomes reasonable, either during a planning process for a preview or at the end for report generation.

A default data export is possible with a single API request to the export link under a cube resource. The value for the variable `CUBE_CELLDATA_EXPORT_LINK` is retrieved by navigation to a cube resource, following its link `data`. In order to retrieve cube cell data, request header `Accept` must be set to the custom MIME type `application/x.de.qvantum-plan.cube-cell-data`. Two further parameters allow to configure the export format:

* `charset`: the charset to be used. Valid values are `US-ASCII`, `ISO-8859-1`, `UTF-8`, `UTF-16`, `UTF-16BE`, `UTF-16LE`, and `Windows-1252`. If omitted, defaults to `UTF-8`.
* `decimalSeparator`: the character used as decimal separator in numeric values. May be explicitly specified as hexadecimal character code (e.g. `decimalSeparator=0x2c`) or as character in double quotes (e.g. `decimalSeparator=",")`. If omitted, defaults to dot character (`.`).

{% tabs %}
{% tab title="Default Data Export" %}

```sh
curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-cell-data" \
  "${CUBE_CELLDATA_EXPORT_LINK}"
```

{% endtab %}

{% tab title="Charset UTF-16 and Decimal Separator ','" %}

```sh
curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-cell-data;charset=UTF-16;decimalSeparator=\",\"" \
  "${CUBE_CELLDATA_EXPORT_LINK}"
```

{% endtab %}
{% endtabs %}

In response to this request, cell data is downloaded in CSV format.

{% hint style="info" %}
It should be noted that with a default data export request like the above, all data of the cube will be exported. This default behavior can be overridden with a set of filters, as described in the section [filtering planning data](#filtering-data).
{% endhint %}

### Exporting Filtered Data

The filters described in [Filtering Data](#filtering-data) can be passed as query parameters of the export request. Alternatively, dimension restrictions can be passed in the request body.

#### Filters as Query Parameters

Both filter types can be passed as query parameters:

* `inputAllowedFilter`: one of the values described in [Filtering by Input Allowed](#filtering-by-input-allowed)
* `dimensionRestrictions`: a dimension restrictions filter (cf. [Filtering by Dimension Restrictions](#filtering-by-dimension-restrictions)) as JSON array in URL-encoded form as defined in [RFC 3986](https://tools.ietf.org/html/rfc3986)

URL-encoding is supported by default in most modern programming languages, command line tools like `urlencode` also exist for scripting purposes. The following example illustrates a valid request with a URL-encoding of the dimension restrictions filter from [Combining Multiple Dimension Restrictions](#combining-multiple-dimension-restrictions) for illustrative purposes.

**Data Export With Filters as Query Parameters**

```sh
dimensionRestrictionsFilterEncoded="%5B%20%7B%20%22dimensionKey%22%3A%22Cost%20Centers%22%2C%20%22type%22%3A%22LEAVES%22%2C%20%22structureKey%22%3A%22Cost%20Centers%22%20%7D%2C%20%7B%20%22dimensionKey%22%3A%22Costs%22%2C%20%22type%22%3A%22SINGLE%22%2C%20%22structureElementKey%22%3A%22CST020%22%20%7D%2C%20%7B%20%22dimensionKey%22%3A%22Scenarios%22%2C%20%22type%22%3A%22SUBSET%22%2C%20%22structureElementKeys%22%3A%5B%20%22ACTUAL%22%2C%20%22PLAN%22%20%5D%20%7D%20%5D"

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-cell-data" \
  "${CUBE_CELLDATA_EXPORT_LINK}?inputAllowedFilter=ALL&dimensionRestrictions=${dimensionRestrictionsFilterEncoded}"
```

{% hint style="warning" %}
Dimension restrictions filters enumerating many structure elements, e.g. a `SUBSET` restriction with several hundred element keys, quickly lead to very long URLs. The request line and headers of a request must not exceed 8 KB in total. Since headers like `Authorization` count towards this limit, the usable URL length is considerably smaller. Longer requests are rejected before reaching QVANTUM. For such filters, pass the dimension restrictions in the request body instead (cf. [Dimension Restrictions in the Request Body](#dimension-restrictions-in-the-request-body)).
{% endhint %}

#### Dimension Restrictions in the Request Body

Data can also be exported with a `POST` request to the same export link, passing the dimension restrictions filter as JSON array in the request body. The request body must be declared with header `Content-Type:application/json`; the JSON array must not be URL-encoded. The header `Accept` configures the export format as described above, and an input allowed filter is passed as query parameter `inputAllowedFilter`. If the request body is omitted, no dimension restrictions are applied.

**Data Export With Dimension Restrictions Filter in Request Body**

```sh
curl --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-cell-data" \
  --header "Content-Type:application/json" \
  --data-binary "@${DIMENSION_RESTRICTIONS_PATH}" \
  "${CUBE_CELLDATA_EXPORT_LINK}?inputAllowedFilter=ALL"
```

* `DIMENSION_RESTRICTIONS_PATH`: path to a file containing the dimension restrictions filter as JSON array, e.g. the filter from [Combining Multiple Dimension Restrictions](#combining-multiple-dimension-restrictions)

## Exporting the Changelog

QVANTUM records changes to the data of a cube in a changelog, which allows to trace who changed which data at what time.

The complete changelog is exported with a single API request. The value for the variable `CUBE_CHANGELOG_LINK` is retrieved by navigation to a cube resource, following its link `changelog`. Request header `Accept` must be set to the custom MIME type `application/x.de.qvantum-plan.cube-changelog`. Two further parameters allow to configure the export format:

* `charset`: the charset to be used. Valid values are `US-ASCII`, `ISO-8859-1`, `UTF-8`, `UTF-16`, `UTF-16BE`, `UTF-16LE`, and `Windows-1252`. If omitted, defaults to `UTF-8`.
* `decimalSeparator`: the character used as decimal separator in numeric values. May be explicitly specified as hexadecimal character code (e.g. `decimalSeparator=0x2c`) or as character in double quotes (e.g. `decimalSeparator=",")`. If omitted, defaults to dot character (`.`).

{% tabs %}
{% tab title="Default Changelog Export" %}

```sh
curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-changelog" \
  "${CUBE_CHANGELOG_LINK}"
```

{% endtab %}

{% tab title="Charset UTF-16 and Decimal Separator ','" %}

```sh
curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-changelog;charset=UTF-16;decimalSeparator=\",\"" \
  "${CUBE_CHANGELOG_LINK}"
```

{% endtab %}
{% endtabs %}

In response to this request, the changelog is downloaded in CSV format. Its columns and the possible types of changes are described in the German user documentation: [Änderungsprotokoll/Changelog](/user/owner/planung/daten.md#anderungsprotokoll-changelog).

## Filtering Data

Data export and deletion of data can be further refined by specifying combinations of different types of *filters*. In the following sections, these filter types are explained in detail. How filters are passed in requests is described in [Exporting Filtered Data](#exporting-filtered-data) and [Deleting Filtered Data](#deleting-filtered-data).

### Filtering by Input Allowed

Filtering by the input allowed property allows to either export data for *all* cube cells or only for those cube cells whose effective input allowed property evaluates to true (cf. [Input Allowed](/dev/model.md#input-allowed))

An input allowed filter has one of the following values:

* `ONLY_INPUT_ALLOWED`: only cube cells with effective input allowed true (default)
* `ALL`: all cube cells

### Filtering by Dimension Restrictions

{% hint style="warning" %}
Currently, the QVANTUM Public API does not support retrieval of information on structures or structure elements. Thus, this filtering feature is only usable, if the user has access to the full planning model definition.
{% endhint %}

Filtering by dimension restrictions allows to export or delete data based on restrictions for individual dimensions (cf. [Combining Multiple Dimension Restrictions](#combining-multiple-dimension-restrictions) for how several restrictions combine). The following four types of dimension restrictions are available, each defining a different set of elements to include.

#### SINGLE

Restricts a dimension to **exactly one element**. Only cell data for the specified element is included; no descendant elements are matched.

```json
{
  "dimensionKey" : "<DIMENSION_KEY>",
  "type" : "SINGLE",
  "structureElementKey" : "<STRUCTURE_ELEMENT_KEY>"
}
```

* `DIMENSION_KEY`: the plural label of the filter dimension
* `STRUCTURE_ELEMENT_KEY`: business key of the structure element to filter for

**Example:** Restricting the *Costs* dimension to element `CST020` (Rent) yields only cells for "Rent" — not for any other cost type:

**SINGLE Dimension Restriction**

```json
{
  "dimensionKey": "Costs",
  "type": "SINGLE",
  "structureElementKey": "CST020"
}
```

#### SUBSET

Restricts a dimension to an **explicitly enumerated set of elements**. Only the listed elements themselves are matched — their descendant elements in the dimension hierarchy are **not** included.

```json
{
  "dimensionKey" : "<DIMENSION_KEY>",
  "type" : "SUBSET",
  "structureElementKeys" : [
    "<STRUCTURE_ELEMENT_KEY_1>",
    "<STRUCTURE_ELEMENT_KEY_2>",
    ...
  ]
}
```

* `DIMENSION_KEY`: the plural label of the filter dimension
* `STRUCTURE_ELEMENT_KEY_N`: business keys of the structure elements to filter for

**Example:** Restricting the *Scenarios* dimension to `ACTUAL` and `PLAN` yields only cells for these two scenarios, not for `FORECAST`:

**SUBSET Dimension Restriction**

```json
{
  "dimensionKey": "Scenarios",
  "type": "SUBSET",
  "structureElementKeys": ["ACTUAL", "PLAN"]
}
```

{% hint style="success" %}
**SUBSET vs. SUBTREES:** Unlike `SUBTREES`, the `SUBSET` type matches *only the explicitly listed elements themselves* — descendant elements are **not** included. See [SUBTREES](#subtrees) below for a direct comparison.
{% endhint %}

#### LEAVES

Restricts a dimension to all its **leaf elements** — i.e., elements that have no children in the dimension hierarchy. This is a structural restriction and requires no element keys to be specified.

```json
{
  "dimensionKey" : "<DIMENSION_KEY>",
  "type" : "LEAVES"
}
```

* `DIMENSION_KEY`: the plural label of the filter dimension

**Example:** In the *Cost Centers* dimension of the tutorial planning model, the hierarchy is:

```
CCT000 (All Cost Centers)
├── CCT010 (Sales)                  ← leaf
└── CCT020 (Administration)
    ├── CCT021 (General Admin)      ← leaf
    └── CCT022 (IT Admin)           ← leaf
```

Restricting to `LEAVES` yields only `CCT010`, `CCT021`, and `CCT022`:

**LEAVES Dimension Restriction**

```json
{
  "dimensionKey": "Cost Centers",
  "type": "LEAVES"
}
```

#### SUBTREES

Restricts a dimension to one or more **subtrees** — that is, the specified root element(s) **together with all their descendants** in the dimension hierarchy.

```json
{
  "dimensionKey": "<DIMENSION_KEY>",
  "type": "SUBTREES",
  "structureElementKeys": [
    "<STRUCTURE_ELEMENT_KEY_1>",
    "<STRUCTURE_ELEMENT_KEY_2>",
    ...
  ]
}
```

* `DIMENSION_KEY`: the plural label of the dimension to restrict
* `STRUCTURE_ELEMENT_KEY_N`: business keys of the root elements of the subtrees

**Example:** In the *Cost Centers* dimension, restricting to `SUBTREES: [CCT020]` matches `CCT020` (Administration) **and** all its descendants — `CCT021` (General Administration) and `CCT022` (IT Administration):

```
CCT000 (All Cost Centers)
├── CCT010 (Sales)                  ← not matched
└── CCT020 (Administration)         ← matched (subtree root)
    ├── CCT021 (General Admin)      ← matched (descendant)
    └── CCT022 (IT Admin)           ← matched (descendant)
```

**SUBTREES Dimension Restriction**

```json
{
  "dimensionKey": "Cost Centers",
  "type": "SUBTREES",
  "structureElementKeys": ["CCT020"]
}
```

{% hint style="success" %}
**SUBTREES vs. SUBSET:** While `SUBSET` only matches the explicitly listed elements, `SUBTREES` additionally includes **all descendants** of those elements. This makes `SUBTREES` the right choice when targeting an entire branch of the hierarchy, while `SUBSET` is suited for selecting a fixed list of specific elements regardless of their position in the hierarchy.

Using the example above: `SUBSET: [CCT020]` would match **only** `CCT020` (Administration) itself — **not** its children `CCT021` or `CCT022`. `SUBTREES: [CCT020]` matches all three.
{% endhint %}

#### Combining Multiple Dimension Restrictions

A conjunctive combination of dimension restrictions is expressed as a JSON array, containing a JSON object for each restriction. The example below demonstrates a conjunction using the tutorial planning model. In particular, applying this filter will yield only cube cells describing planned and actual energy costs on the lowest level of cost centers, with no further restrictions on other dimensions.

**Dimension Restrictions Filter with Restrictions on Multiple Dimensions**

```json
[
  {
    "dimensionKey":"Cost Centers",
    "type":"LEAVES"
  },
  {
    "dimensionKey":"Costs",
    "type":"SINGLE",
    "structureElementKey":"CST020"
  },
  {
    "dimensionKey":"Scenarios",
    "type": "SUBSET",
    "structureElementKeys": [
      "ACTUAL",
      "PLAN"
    ]
  }
]
```

{% hint style="info" %}
Dimension restrictions filters are always expressed as JSON Arrays. Thus, even a dimension restrictions filter with just one involved dimension must be encapsulated in a JSON Array!
{% endhint %}

{% hint style="info" %}
Given that dimension restrictions filters are conjunctions of individual dimension restrictions, specifying multiple different dimension restrictions for the same dimension will effectively lead to an empty result!
{% endhint %}

## Combining Filters

The combination of different filter types is possible and follows conjunction semantics in the sense of *"return only cube cells" that fulfill filter 1 AND filter 2 AND ... AND filter n"*.

The following example demonstrates such a conjuntive combination. Goal of this example is to retrieve / delete data for all cube cells for scenario *Actual* (key `ACTUAL`) and for costs *Rent* (key `CST020`), as expressed in the following dimension restrictions filter.

**Dimension Restrictions Filter for Scenario & Cost Dimensions**

```json
[
  {
    "dimensionKey":"Scenarios",
    "type":"SINGLE",
    "structureElementKey":"ACTUAL"
  },
  {
    "dimensionKey":"Costs",
    "type":"SINGLE",
    "structureElementKey":"CST020"
  }
]
```

A query with this dimension restrictions filter only will yield / delete an empty result, since the scenario "Actual" is defined with input allowed set to false. In order to retrieve / delete the expected result set, an additional input allowed filter must be set to value `ALL`.