Handling Cell Data¶

With a successfully uploaded planning model definition, a planning application 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 planning application. The compilation of such initial data is a substantial part of planning application 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.

Compile Initial Data¶

For later upload of initial data into a planning application, 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 processes.

Download Data Template¶

The endpoint for downloading a data template is available from the links section in a cube resource representation. In the following request example, we assume an environment variable CUBE_URL to hold the URL to a cube after navigation (cf. Navigating to a cube).

Download Cube Data Template

curl --show-headers --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_TEMPLATE_URL}/template'

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 a best-case variant (variant BC) of sales costs (cost center CCT010). In particular, it includes data for actual costs in January 2018 (year 2018, scenario IS) and forecast costs for January 2019 (year 2019, scenario FORECAST). While staff costs (costs CST010) went up from 50000.00$ to 60000.00$, energy costs (costs CST030) went down from 300.00$ to 250.99$.

Sample Cube Data File

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

Warning

Cell data files involving mixed use of different decimal separators are considered invalid and will thus lead to failure upon upload.

Default Cell Data Upload

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_URL}"

Cell Data Upload - Explicit Decimal Separator and Charset

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_URL}"

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). 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 planning application. A reasonable request might be: delete all forecast data in the year 2023.

Important

Data can only be deleted when there is no workflow running. To change the state of a workflow status you can follow this description.

A default data deletion is possible with a single API request to the delete link under a cube resource. The value for the variable CUBE_CELLDATA_DELETE_URL is retrieved by navigation to a cube resource, following its link data.

Default Data Deletion

curl --request DELETE \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_CELLDATA_DELETE_URL}"

Important

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.

Exporting Planning Data¶

With the availability of data collected from user inputs in the context of a given planning application, 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_URL 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 (.).

Default Data Export

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

Data Export with Charset UTF-16 and Decimal Separator ','

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

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

Important

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.

Important

A full interaction path with the QVANTUM public API regarding data exports with filtering is demonstrated in the script qvantum-data-export.sh in our sample data set.

Filtering Data¶

Data export and deletion of data can be further refined by specifying combinations of different types of filters. All filter types are expressed as additional query parameters, added to the cube data cell URL used for default data exports or deletions. In the following sections, these filter types are explained in detail.

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)

An input allowed filter can be expressed as request parameter inputAllowedFilter with one of the following values:

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

The example request below exports / deletes only those cube cells, where input is allowed, thus overriding the default behavior that all cells are exported resp. deleted.

Data Export With Input Allowed Filter

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.cube-cell-data" \
  "${CUBE_CELLDATA_EXPORT_URL}?inputAllowedFilter=ONLY_INPUT_ALLOWED"

Deletion of Data With Input Allowed Filter

curl --request DELETE \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_CELLDATA_DELETE_URL}?inputAllowedFilter=ONLY_INPUT_ALLOWED"

Filtering by Dimension Restrictions¶

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.

Filtering by dimension restrictions allows to export data for a conjunction of restrictions for individual dimensions.

The following types of restrictions are available:

Restriction of a dimension to a single element:
```
{
  "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

Restriction of a dimension to a subset of elements:

{
  "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

Restriction of a dimension to the leaf elements:
```
{
  "dimensionKey" : "<DIMENSION_KEY>",
  "type" : "LEAVES"
}
```
- DIMENSION_KEY: the plural label of the filter dimension
Restriction of a dimension to data in given subtrees:
```
{
  "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 roots of the subtrees to which the dimension is restricted

A conjunctive combination of dimension restrictions is expressed as JSON array, containing JSON objects for each element of the conjunction. The example below demonstrates a conjunction, using the tutorial planning model. In particular, appyling 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

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

A dimension restrictions filter is passed as query parameter dimensionRestrictions with its value being the corresponding JSON array in URL-encoded form as defined in RFC 3986. 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 above dimension restrictions filter for illustrative purposes.

Request with URL-Encoded Dimension Restrictions Filter

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_URL}?dimensionRestrictions=${dimensionRestrictionsFilterEncoded}"

curl --request DELETE \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_CELLDATA_DELETE_URL}?dimensionRestrictions=${dimensionRestrictionsFilterEncoded}"

Important

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!

Important

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!

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 Energy (key CST030), as expressed in the following dimension restrictions filter.

Dimension Restrictions Filter for Scenario & Cost Dimensions

[
  {
    "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, as indicated below.

Combining Input Allowed & Dimension Restrictions Filters

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

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