Planning Models¶

With the ability to authenticate and to navigate within a tenant context, the first actual use case is the preparation of a planning application. Preparation consists of the following steps:

Build planning model: build a planning model definition as set of CSV files
Upload planning model: upload prepared planning model definition
Upload data: upload initial planning data as CSV file
Upload team: upload user information and permission assignments as CSV files
Import forms: import folders and forms as a JSON file

Build Planning Model¶

Each planning application requires a definition of an OLAP cube model. Conceptually, a cube requires the definition of a set of dimensions on the top-level. Each dimension is modeled by a set of basic dimension fields and an underlying structure. A structure in turn is modeled as a set of structure elements, upon which a certain structure is imposed. The QVANTUM Public API expects a complete cube model definition as a set of CSV files. The following sections demonstrate how to build up a complete planning model and the set of corresponding CSV files.

Planning Model Definition¶

A planning model definition basically consists of a list of basic dimension definitions. A basic dimension definition includes the following fields:

Number: a number serving as dimension identifier
Description: a full-text dimension description
Labels: singular and plural dimension labels
Role: dimension role of fixed type. The following types are available:
- Kennzahlen: special dimension role for key figures
- Planungseinheiten: special dimension role for planning units
- Zeit: special dimension role for time models
- ohne besondere Rolle: regular dimension without special role

The Kennzahlen and Planungseinheiten dimension roles must occur exactly once in every planning model definition. Zeit may appear once, but it is not required. There are no constraints on the occurrence of ohne besondere Rolle.

Important

In the context of a major planning model simplification, a number of special dimension role constraints were removed. For reasons of backward compatibility, models specifying dimensions with prior special dimension roles Szenarien, Varianten, Jahre and Monate are still accepted in planning model definitions, but effectively mapped to dimension role ohne besondere Rolle.

For later upload via the QVANTUM Public API, a cube model definition must be provided in the form of a valid CSV file, as shown in the example below.

Top-Level Model Definition CSV

number;singular-label;plural-label;role;description
1;Cost;Costs;Kennzahlen;All types of costs.
2;Cost Center;Cost Centers;Planungseinheiten;All cost centers.
3;Scenario;Scenarios;Szenarien;All scenarios.

Time Models¶

A dimension defined with the special dimension role Zeit (time) allows for the definition of a one-dimensional time model. A time model is necessary to use the timeOffset function, which allows modeling values that change over time, e.g. to track the stock of a product by planning the changes per month, not the absolute value.

Display & Calculation Order¶

During the design of a planning model, it is possible to control the display order of dimensions by their order of appearance in the planning model definition. The dimension number has no effect on display order.

Calculation order, i.e. the order of applying aggregation and formula calculation operations in a dimension-by-dimension manner, is implicitly given by the respective dimension roles. Regular formulas in the key figure dimension are always calculated first, followed by aggregation calculations in all other dimensions. Formulas for inner nodes are calculated last. For more details on formula calculations, consult Section Key Figure Formulas. Calculations take place with every change in planning application data, e.g. directly by data uploads, or indirectly by model updates.

Structure Definitions¶

Furthermore, for each dimension, a structure must be defined. A structure definition includes a set of dimension elements, upon which a hierarchical structure is imposed. A single structure element definition includes the following fields:

Child: element identifier of this element
Parent: element identifier of a parent element
Label: element label
Description: full-text element description
Input Allowed: data input allowed for this particular element
Layer Name: the name of the hierarchical layer of this element (optional)
Value Type: value type (only for role Kennzahlen)
Aggregation: aggregation operation (forbidden for role Kennzahlen, optional for all other roles)
Formula: a formula with which to calculate this element (only for role Kennzahlen, optional)
Formula for Inner Nodes in other Dimensions: an alternative formula to use if an element in another dimension is an inner node (only for role Kennzahlen, optional)

On top of the given fixed set of reserved fields, for structure elements it is possible to model custom attributes for all structure elements. Custom attributes allow to present or even collect additional information on structure elements that do not make sense to be modeled as separate structures. As an example, a dimension modeling sales units might include name and telephone number of relevant contact persons. This information can be configured to be shown or even edited in forms in the QVANTUM Web application.

Each structure is modeled as a forest of elements. There is a list of root elements which have an empty Parent field. Those elements will be displayed on the first level of the structure in the QVANTUM Web application. Every other element must have exactly one parent, i.e. it is not allowed for an element to have multiple parents. The order of the root elements and the order of all elements with the same parent is determined by the order in which they appear in the structure definition.

For later upload via the QVANTUM Public API, structure definitions must again be provided in form of valid CSV files, as shown in the examples below. CSV headers are the same for most dimension roles. Only the role Kennzahlen for key figures is exceptional, as its structure definition requires an additional column valuetype and don't include the aggregation column. More examples of structure definitions are available from the example data package in folder csv/metadata.

Structure CSV (Non-Key Figure)

child;parent;label;description;input
ACTUAL;;Actual;Scenario for actual figures.;no
FORECAST;;Forecast;Scenario for forecast figures.;yes
PLAN;;Plan;Scenario for plan figures.;yes

Structure CSV (Key Figure)

child;parent;label;description;input;valuetype;formula;formulaForInnerNodes
CST000;;All costs;;yes;Betrag;[CST010]+[CST020]+[CST030]+[CST040];
CST010;CST000;Staff;;yes;Betrag;;
CST010P;CST000;Staff Percent;;yes;Prozentsatz;[CST010]/[CST000]*100;
CST020;CST000;Rent;;yes;Betrag;;
CST020P;CST000;Rent Percent;;yes;Prozentsatz;[CST020]/[CST000]*100;
CST030;CST000;Energy;;yes;Betrag;;
CST040;CST000;Other;;yes;Betrag;;
CST050;CST000;External;;yes;Betrag;;
CST051;CST050;Catering;;yes;Betrag;;
CST052;CST050;Beverages;;yes;Betrag;;

Input Allowed¶

Use of the Input Allowed field determines if data input on a particular structure element is allowed (value yes) or not (value no). In general, for a given cube cell addressed by an n-dimensional vector of structure elements, input is allowed iff input is allowed for all structure elements. For dimensions with permissions, this general input allowed notion is additionally combined with user permissions defined for each structure element. However, use of the QVANTUM Public API always involves a user with role central controller and thus full permissions on all cube cells. Evaluation of the input allowed property on a cube cell will thus effectively not involve any user-related permission restrictions. The definition of input allowed does not automatically propagate upwards or downwards in the hierarchy, but must rather be explicitly defined for each structure element.

Layer Names¶

Often, the hierarchy of a structure consists of elements in related layers. With the column layerName, those layers can be named, such as in the following example:

Month Structure with Layer Names

child;parent;label;description;input;layerName
Y;;Year;;yes;Year
Q1;Y;First Quarter;;yes;Quarter
Jan;Q1;January;;yes;Month
Feb;Q1;February;;yes;Month
Mar;Q1;March;;yes;Month
Q2;Y;Second Quarter;;yes;Quarter
Apr;Q2;April;;yes;Month
May;Q2;May;;yes;Month
Jun;Q2;June;;yes;Month

Here, the first/top layer only contains the year. The second layer consists of the quarters and the third/bottom layer contains all months.

To avoid unnecessary repetition, not all elements within the same hierarchical layer need to define a layer name. For example, it would be enough to set the layer name for january, but not for the other months. However, if the layer name feature is used for a structure, every layer must have at least one element with a specified layer name.

Month Structure with Minimal Layer Names

child;parent;label;description;input;layerName
Y;;Year;;yes;Year
Q1;Y;First Quarter;;yes;Quarter
Jan;Q1;January;;yes;Month
Feb;Q1;February;;yes;
Mar;Q1;March;;yes;
Q2;Y;Second Quarter;;yes;
Apr;Q2;April;;yes;
May;Q2;May;;yes;
Jun;Q2;June;;yes;

Value Types¶

Use of the Value Type field is only defined for dimensions of role Kennzahlen. Currently, QVANTUM supports five value types:

Betrag: an amount of money
Menge: a quantity
Preis: a price
Prozentsatz: a percentage
Bestand: a stock (inventory)

Key figures of value types Betrag and Menge are aggregated across dimensions. Key figures of value types Preis, Prozentsatz and Bestand are not aggregated across dimensions. For Preis and Protzentsatz this follows the rationale that aggregation in terms of summing usually does not make sense. For Bestand this may change in the future, for example to only disable aggregation in dimensions which are part of the time model.

Aggregation¶

Use of the Aggregation field determines an operation used for aggregation. This field is not defined for dimensions of role Kennzahlen. Besides simple storage of the data explicitly uploaded, QVANTUM performs data aggregation in the OLAP sense over structures in different dimensions of a multidimensional cube. Aggregation involves computing hierarchical data relationships over one or more dimensions. For example, summing up all costs over all cost centers for a specific year to get yearly total costs is an aggregation operation over two dimensions (costs and cost centers). If we additionally sum up total yearly costs over multiple years to receive total costs ever, we even involved a third dimension into aggregation. Currently, QVANTUM only supports the following values for aggregation operations:

sum (default): aggregation sums up values of all children of this element, if any.
no aggregation: aggregation is suppressed for this element.

The aggregation column may be omitted, thus effectively using sum as default aggregation operation. Individual values for aggregation may also be omitted in the same sense that sum is accepted as default aggregation operation. If sum is considered as default aggregation operation for all structure elements, the whole aggregation column can be omitted. Aggregation rule sum or no aggregation defined on leaves is effectless per definition and can thus be omitted.

Following these specifications, all data examples shown below are equivalent.

Explicit aggregation

child;parent;label;description;input;aggregation
ROOT;;All costs;;yes;sum
CHILD1;ROOT;Staff;;yes;sum
CHILD2;ROOT;Energy;;yes;sum

Aggregation values omitted, effectless no aggregation

child;parent;label;description;input;aggregation
ROOT;;All costs;;yes;
CHILD1;ROOT;Staff;;yes;no aggregation
CHILD2;ROOT;Energy;;yes;sum

Aggregation column omitted

child;parent;label;description;input
ROOT;;All costs;;yes
CHILD1;ROOT;Staff;;yes
CHILD2;ROOT;Energy;;yes

Key Figure Formulas¶

For dimensions defined with the special dimension role Kennzahlen (key figures), QVANTUM supports the definition of formulas on key figure structure elements.

Modeling formulas is supported with the use of two optional columns formula and formulaForInnerNodes, allowing formulas on key figures in their respective cells.

A formula is expressed as a formula string supporting the following syntax constructs:

integers (e.g. 1) or floats using decimal comma (e.g. 29,99)
key figure references by business key in square brackets (e.g. [CST000])
supported named constants, currently BLANK
signed expressions (e.g. -[CST000] or -1,1)
basic algebraic operators +, -, *, and /
comparison operators >, >=, <, <=, =, != and <>
boolean negation !
boolean operators && and ||
supported functions (e.g. abs([CST000]))
round brackets (e.g. -(1+2))
white space (e.g. 1 + 2 as equivalent to 1+2)

Supported functions:

abs: absolute value of argument (e.g. abs([A]) evaluates to 2, given value of A is -2)
sign: computes the sign of the given number (e.g. sign(-42) evaluates to -1, sign(42) to 1 and sign(0) to 0)
round: rounded value of first argument (e.g. round([A]) evaluates to 2, given value of A is 1.52), optionally controlling for rounding digits with second argument (e.g. round([A]; -3) evaluates to 1000 given value of A is 995; round([A]; 3) evaluates to 9.556, given value of A is 9.5557).
divide: divides the first by the second value and selects an alternative in case of a division by zero. Per default, this alternative is an empty value but it can be specific as a third argument to the function. For example, divide(6;2;42) evaluates to 3, divide(6;[A]) to empty if A is empty or zero and divide(6;[A];42) to 42 if A is empty or zero.
quotient: evaluates to the integer result of the division without remainder. For example, quotient(5;2) and quotient(-5;-2) evaluate to 2, quotient(-5;2) and quotient(5;-2) to -2. If the second argument is empty or zero a division by zero would be performed so the function evaluates to the invalid value.
modulo: evaluates to the remainder of the integer devision while always having the same sign as the second argument. For example, modulo(5;3) evaluates to 2, modulo(5;-3) to -1, modulo(-5;3) to 1 and modulo(-5;-3) to -2. If the second argument is empty or zero a division by zero would be performed so the function evaluates to the invalid value.
coalesce: evaluates to the first non-empty argument. For example, if A is empty coalesce([A], BLANK, 42, 5) evaluates to 42.
minimum: evaluates to the minimum of the given arguments. Empty arguments are ignored. If one of the arguments is invalid, the function evaluates to invalid. For example, minimum(BLANK;42;2) evaluates to 2 and minimum(2;1/0) to invalid.
maximum: evaluates to the maximum of the given arguments. Empty arguments are ignored. If one of the arguments is invalid, the function evaluates to invalid. For example, maximum(BLANK;-42;-2) evaluates to -2 and minimum(2;1/0) to invalid.
sumOfChildren: evaluates to the sum of the children of the current element. If the element has no children, it evaluates to the empty value. If any of the children has an invalid value, the function evaluates to invalid. For example, if A has the children B and C with values 3 and 2, sumOfChildren() evaluates to 5 for A and to the empty value for C.
timeOffset: takes the value of the referenced key figure from a previous element in the time model dimension. For example, if the time model dimension is the usual month dimension, and the cube contains a value 42.42 for key figure A in JANUARY, then a formula of timeOffset([A];-1) + 1 for key figure B would result in value43.42forBinFEBRUARY. In contrast to every other place in formulas, the reference of atimeOffset` formula may be a self-reference. The second argument determines the relative element in the time dimension. It must be negative, i.e. only access "past" values, not "future" ones.
if: takes a boolean argument and two number arguments. If the boolean argument evaluates to true, the first number argument is returned, otherwise the second argument is returned. For example, if(3 > 1; 4; 2) evaluates to 4 and if(3 < 1; 4; 2) to 2.
isBlank: evaluates whether the given argument is empty. For example, isBlank(BLANK) evaluates to true and isBlank(2) to false.
isValid: evaluates whether the given argument is a valid number (or blank). For example, isValid(2) and isValid(BLANK) evaluate to true and isValid(1/0) evaluates to false
and: function version of &&. For example, and(1 > 0, 2 = 2) evaluates to true and and(1 > 0, 2 != 2) to false.
or: function version of ||. For example, or(1 > 0, 2 != 2) evaluates to true and and(1 > 5, 2 != 2) to false.
not: function version of !. For example, not(1 > 0) evaluates to false and not(2 > 3) to true.

Given by key-figure-first calculation order, regular formulas from column formula are calculated first for all cube cells. Then, aggregation calculations take place for all non-key-figure dimensions. After all previous calculations have taken place, an optional further calculation may be modeled to take place for all cube cells involving a key figure structure element with an additional formula for inner nodes and an inner node in at least one non-key-figure dimension, effectively overwriting cell values produced in all previous calculations. Typical use cases for this additional formula are key figures, for which regular aggregations do not make sense (e.g. for key figures of value type price), but that still require reasonable cell values to be calculated on inner nodes.

Consider the following simple example, involving two dimensions Key Figures and Products with definitions as specified below.

Key Figure Dimension

child;parent;label;input;valuetype;description;formula;formulaForInnerNodes
UNITS;;Units Sold;yes;Menge;;;
PRICE;;Price per Unit;yes;Preis;;;"round([SALES]/[UNITS]; 2)"
SALES;;Sales;no;Betrag;;[UNITS]*[PRICE];

Product Dimension

child;parent;label;input;description;aggregation
ALL;;All Products;no;;sum
P1;ALL;Product 1;yes;;
P2;ALL;Product 2;yes;;

Now consider that values for key figures PRICE and UNITS are entered by a user for the leaf product structure elements P1 and P2, as indicated below.

	SALES	UNITS	PRICE
ALL
P1		10	3
P2		5	2

In a first calculation step, key figure formulas from colum formula are calculated for P1 and P2, thus effectively adding cell values for key figure SALES on P1 and P2, as indicated below.

	SALES	UNITS	PRICE
ALL
P1	30	10	3
P2	10	5	2

In the next calculation step, aggregation takes place in the Product dimension. Effectively, this calculation adds key figure values for root element ALL. Note that key figure PRICE is not aggregated due to its value type, thus leaving the corresponding cell empty.

	SALES	UNITS	PRICE
ALL	40	15
P1	30	10	3
P2	10	5	2

In a final step, all formulas from column formulaForInnerNodes are calculated for inner nodes in all non-key-figure dimensions, in this example in the product dimension. Given the aggregated values for SALES and UNITS, the specified formula for inner nodes for key figure PRICE effectively calculates an average product price, weighted over the units sold per product, rounded to two decimal places, as indicated below.

	SALES	UNITS	PRICE
ALL	40	15	2.67
P1	30	10	3
P2	10	5	2

The calculation of formulas takes place upon any operation effectively changing the planning data persisted within the context of a planning application. Regular formulas are calculated according to the given calculation order. Formulas for inner nodes are always calculated after regular calculations over all dimensions.

Warning

With the given syntax, it is possible to define constant formulas, i.e. formulas without references to other key figures (e.g. 10 or -(20+40)) QVANTUM explicitly forbids the use of constant formulas. Any upload of a planning application model defining constant formulas will thus fail.

Warning

The definition of a formula system, i.e. a set of multiple formulas, entails the risk of modeling cyclic dependencies between formulas. Note that the two different formula columns formula and formulaForInnerNodes define separate formula systems. The most trivial example possible is shown in the example below for regular formulas (A=B and B=A). With cyclic dependencies it is impossible to perform formula calculation. Thus, any upload of a planning model defining a formula system involving cyclic dependencies will fail with an error message providing a hint on all detected cycles.

Formula system with trivial cyclic dependency

child;parent;label;description;input;valuetype;formula;formulaForInnerNodes
A;;Element A;;yes;Betrag;[B];
B;;Element B;;yes;Betrag;[A];

Custom Attributes¶

Custom attributes are modeled in the context of structure elements. As such, all structure elements of the same structure carry the same list of custom attributes. Each custom attribute defines a key unique among the custom attributes of the same dimension, a string value, and an optional input allowed configuration whether the value of the given attribute may be edited.

As such, a custom attribute is modeled as two additional columns: a value column defining the attribute key in the header and an input allowed column for defining the input allowed property of the given attribute for a given structure element. Custom attribute list order is given with the order of appearance of the respective value columns.

The syntax for column headers is given as

<KEY> : string business key; must not exceed a maximum length 255 characters, must not contain square brackets, names of reserved fields must not be used as key.
[<KEY> - input] : input allowed column for the attribute; column may be omitted.

The value of a custom attribute must not exceed a maximum length of 1024 characters. The input allowed property of a custom attribute can be defined as allowed (value yes) or not (value no). If no value is given, the property is interpreted as no. If the column is omitted, no is assumed for all structure elements.

The following example of a planning unit structure defines a custom attribute "Representative". While some names for representatives of a planning unit are included with input allowed set to no, others are to be collected, thus modeled with input allowed set to yes.

Structure with Custom Attribute

child;parent;label;description;input;aggregation;Representative;[Representative - input]
CCT000;;All Cost Centers;;yes;sum;Cevin Costner;no
CCT010;CCT000;Sales;;yes;sum;Sally Sales;no
CCT020;CCT000;Administration;;yes;sum;;yes
CCT021;CCT020;General Administration;;yes;sum;;yes
CCT022;CCT020;IT Administration;;yes;sum;Irvin Tecnic;no

Upload planning model¶

A valid planning model definition consists of one top-level planning model definition CSV file and a set of n structure definition CSV files, with n being the number of dimensions defined. With a complete and valid set of definition files at hand, a planning model definition can be uploaded to QVANTUM in one single asynchronous API call with its result enabling status polling afterwards.

Warning

With a successful upload of a new planning model definition, any previously existing planning application including all forms, all data and all users will effectively be lost in favor of a new planning application. However, updating an existing planning model definition is supported, as described in upload full model update.

The example data bundled with this tutorial consists of several CSV files, one for the top-level planning model definition and one for each of the involved dimensions. In the following request example, we assume an environment variable MODEL_UPLOAD_URL to hold the URL for uploading a planning model (cf. Section Navigating to a planning application).

Asynchronous upload¶

Technically, a planning model definition upload is formulated as a multipart request with some parts being defined as key-value pairs (cubeKey and cubeLabel) and the remaining parts being defined as files. The file parts relate to all files in a complete and valid planning model definition. The top-level planning model definition file must be configured for part planning-model. Each dimension is configured for the part according to the number column value, specified in the planning model top-level definition file, e.g. 1 for the costs dimension or 3 for the scenarios dimension. Each uploaded file allows to further specify the used charset with an additional parameter:

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.

Upload Planning Model Definition

MODEL_META_PATH=/absolute/path/to/tutorial/data/csv/metadata
PLANNING_MODEL_PATH=${MODEL_META_PATH}/planning-model.csv
DIM_COSTS_PATH=${MODEL_META_PATH}/1.csv
DIM_COST_CENTERS_PATH=${MODEL_META_PATH}/2.csv
DIM_SCENARIOS_PATH=${MODEL_META_PATH}/3.csv

curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:multipart/form-data" \
  --form "cubeKey=1" \
  --form "cubeLabel=Cube" \
  --form "planning-model=@${PLANNING_MODEL_PATH};type=text/csv" \
  --form "1=@${DIM_COSTS_PATH};type=text/csv" \
  --form "2=@${DIM_COST_CENTERS_PATH};type=text/csv" \
  --form "3=@${DIM_SCENARIOS_PATH};type=text/csv" \
  "${MODEL_UPLOAD_URL}"

Upload Planning Model Definition with Explicit Charsets

MODEL_META_PATH=/absolute/path/to/tutorial/data/csv/metadata
PLANNING_MODEL_PATH=${MODEL_META_PATH}/planning-model.csv
DIM_COSTS_PATH=${MODEL_META_PATH}/1.csv
DIM_COST_CENTERS_PATH=${MODEL_META_PATH}/2.csv
DIM_SCENARIOS_PATH=${MODEL_META_PATH}/3.csv

curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:multipart/form-data" \
  --form "cubeKey=1" \
  --form "cubeLabel=Cube" \
  --form "planning-model=@${PLANNING_MODEL_PATH};type=text/csv;charset=UTF-16" \
  --form "1=@${DIM_COSTS_PATH};type=text/csv" \
  --form "2=@${DIM_COST_CENTERS_PATH};type=text/csv;charset=UTF-16" \
  --form "3=@${DIM_SCENARIOS_PATH};type=text/csv" \
  "${PLANNING_APP_URL}/uploads"

Uploading a planning model definition with the above request 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 allows to monitor the status of planning model definition processing on QVANTUM side. The returned URL leads to an upload history entry resource such as ${PLANNING_APP_URL}/uploads/1.

Important

It might be tempting to misinterpret a successful response to the above upload request as successful processing of a planning model definition. Actual information on processing status and result is only available via status polling (cf. next section)!

Important

A full interaction path with the QVANTUM public API regarding planning model definitions is demonstrated in the script qvantum-model-definition.sh in our sample data set.

Upload status polling¶

Status polling should be done repeatedly to the same upload history entry resource URL, as demonstrated below.

Status Polling

curl --show-headers --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  "${PLANNING_APP_URL}/uploads/1"

The response payload includes a JSON representation of a history entry resource with the following information fields:

id: identifier of current history entry
userId: subject identifier of uploading user
cubeKey: key of cube context
uploadStarted: UTC time of starting the upload on client side
processingStarted: UTC time of starting upload processing on QVANTUM side
processingEnd: UTC time of ending upload processing on QVANTUM side
status: current processing status with one of the following fixed values
- PROCESSING : processing still ongoing
- COMPLETED: processing complete
- PROCESSING_FAILED: processing failed, usually due to passing invalid CSV files
result: current processing result with fixed value
- NOT_MODIFIED: current planning model remains unmodified
- COMPLETE: planning model definition completed successfully

The example payloads below demonstrate status information for different situations, i.e. processing is still ongoing, processing completed successfully, and processing failed. In the case processing failed, the response payload includes embedded log entries containing error messages. These log entries are available in different languages.

Status Information - Processing

{
  "id": 1,
  "userId": "dad642e5-aec5-4096-a9e3-008d8eff496b",
  "cubeKey": "1",
  "uploadStarted": "2019-08-22T08:55:47.805612Z",
  "processingStarted": "2019-08-22T08:55:47.999988Z",
  "processingEnd": null,
  "status": "PROCESSING",
  "result": "NOT_MODIFIED",
  "_links":{
    "self":{
      "href": "https://api.qvantum-plan.de/api/v1/tenants/1/planningApplications/1/uploads/1"
     }
  },
  "_embedded": {
    "logEntries": []
  }
}

Status Information - Complete

{
  "id": 1,
  "userId": "dad642e5-aec5-4096-a9e3-008d8eff496b",
  "cubeKey": "1",
  "uploadStarted": "2019-08-22T08:55:47.805612Z",
  "processingStarted": "2019-08-22T08:55:47.999988Z",
  "processingEnd": "2019-08-22T08:55:49.691576Z",
  "status": "COMPLETED",
  "result": "CREATED",
  "_links":{
    "self":{
      "href": "https://api.qvantum-plan.de/api/v1/tenants/1/planningApplications/1/uploads/1"
     }
  },
  "_embedded": {
    "logEntries": []
  }
}

Status Information - Failed

{
  "id": 1,
  "userId": "dad642e5-aec5-4096-a9e3-008d8eff496b",
  "cubeKey": "1",
  "uploadStarted": "2019-08-22T08:55:47.805612Z",
  "processingStarted": "2019-08-22T08:55:47.999988Z",
  "processingEnd": "2019-08-22T08:55:49.691576Z",
  "status": "PROCESSING_FAILED",
  "result": "NOT_MODIFIED",
  "_links":{
    "self":{
      "href": "https://api.qvantum-plan.de/api/v1/tenants/1/planningApplications/1/uploads/1"
     }
  },
  "_embedded": {
    "logEntries": [
      {
        "timeStamp": "2019-08-22T08:55:48.691576Z",
        "logLevel": "ERROR",
        "description": "No reader for 4. Have: [1, 2, 3, planning-model]"
      }
    ]
  }
}

Important

A full interaction path with the QVANTUM public API regarding planning model definition is demonstrated in the script qvantum-model-definition.sh in our sample data set.

Updating Planning Applications¶

Most planning processes require in-process updates of the planning model. Among the most common use cases are adding new elements or whole sub-structures into a structure (e.g. for a newly developed sales region with new branch offices), removing elements or substructures (e.g. after phase-out of a legacy product group), or moving elements or substructures to other places in an existing structure (e.g. reorganization of responsibilities in planning team hierarchies). After such updates, already gathered planning data must be re-organized and re-calculated to guarantee data consistency.

For such situations, the QVANTUM Public API supports single dimension updates and full model updates. A single dimension update affects the underlying structure of a single dimension and triggers automatic re-organization and re-aggregation of cube cell data upon completion. A full model update allows to execute multiple single dimension updates in a batch and additionally changes in the overarching planning model.

Important

Dimension updates in turn have further implications. These implications should be well-known to API client developers and are thus documented in the next section as primer to the actual execution of dimension updates.

Implications of dimension updates¶

Dimension updates can be understood as combinations of changes on the underlying structure of the dimension. Typical operations are adding, deleting, modifying and moving structure elements.

While some operations typically have less critical or no further implications (e.g. adding or modifying), other operations (e.g. deleting or moving) have quite intense implications (e.g. loss of planning data, loss of user access, form invalidation).

Implications of structure element addition¶

Loss of cell data: If a dimension element is added as new leaf under an existing leaf, all cell data related to the existing leaf is lost.

Implications of structure element deletion¶

Loss of cell data: If a dimension element is deleted, all data cells involving this particular element are deleted as well.

Loss of access rights: In particular for the planning unit dimension within a planning application (cf. planning model definition), removal of elements can also cause partial or even complete loss of access rights for users assigned to these elements (cf. define team). In extreme cases, users can lose all access rights and are thus effectively removed from a planning application. Consider the following simple example of a hierarchical planning region structure.

Simple Planning Region Structure

* All regions
  * Europe
    * Germany
    * Austria
    * Switzerland
  * Asia

Updated Structure (no Germany)

* All regions
  * Europe
-   * Germany
    * Austria
    * Switzerland
  * Asia

Updated Structure (no Europe)

* All regions
-  * Europe
-    * Germany
-    * Austria
-    * Switzerland
  * Asia

Furthermore assume that a decentral planner was assigned planning responsibility for Europe. Again, if we remove the element Germany, then this element is removed, including all related cell data. Additionally, the planner will effectively lose responsibility for Germany, but still remain responsible for Austria and Switzerland. If we remove the element Europe, along with its children, the planner loses all access rights and will effectively not be able to participate in the planning application anymore.

Change/invalidation of forms: Dimension elements and their structural properties can play substantial roles in form definitions in QVANTUM. Removal of dimension elements involved in form definitions may cause form changes, i.e. form rows or columns disappear or previously selected form header elements are replaced with still existing elements. In extreme cases, whole forms can become invalid and thus unusable.

Implications of structure element moves¶

Loss/gain of access rights: in the same manner as for element deletion, moving elements can cause partial loss of access rights for decentral planners. Element move operations can also cause a gain of access rights for decentral planners. Consider the following simple example of a planning unit structure.

Simple Planning Unit Structure

* Electronics
  * Smartphones
  * Tablets
    * Child Tablets
* Toys
  * Puppets
  * Superhero Figurines

Updated Structure

* Electronics
  * Smartphones
  * Tablets
-    * Child Tablets
* Toys
  * Puppets
  * Superhero Figurines
+   * Child Tablets

Furthermore assume that there are two decentral planners Alice and Bob, Alice responsible for electronics, Bob for toys. A move from tablets for children into the toys segment effectively causes a gain of access rights for Bob and a loss of access rights for Alice.

Change/loss of data: in the same manner as for element deletion, moving elements can cause loss of data in cases where previous leaf elements become inner elements, because other elements or even subtrees were appended in the context of a move operation. If the new leaves did not contain data before, data in the now inner element is lost. If the new leaves did contain data, data in the now inner element is replaced by new calculation results. In general, QVANTUM makes best efforts to keep existing data where reasonable.

Upload single structure update¶

A structure update as such consists of the upload of an updated single structure definition in the form of a CSV file (cf.structure definition) or in form of a JSON-Representation. A valid structure can be uploaded to QVANTUM in one single asynchronous API call with its result enabling status polling afterwards.

Asynchronous upload¶

The update of a structure requires prior navigation to the structure of the dimension to be updated (cf. navigating to a structure).

In both cases the link to the update URL is given in the scope of a structure resource:

# link to structure update URL in scope of structure resource
STRUCTURE_UPLOAD_URL=$(curl --silent --request GET \
   --header "Authorization:Bearer ${TOKEN}" \
   "${STRUCTURE_URL}" | jq --raw-output ._links.upload.href)

Uploading a structure with a CSV file¶

The upload of a single structure CSV file is a single request with the MIME type application/x.de.qvantum-plan.aspect-with-structure. Optionally, the format of the CSV file can be specified:

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.

Upload Structure Update

CSVFILE="/path/to/dimension-update.csv"
HISTORY_ENTRY_URL=$(curl --silent --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:application/x.de.qvantum-plan.aspect-with-structure" \
  --data-binary "@${CSVFILE}" \
  "${STRUCTURE_UPLOAD_URL}"

Upload Structure Update - Explicit Charset

CSVFILE="/path/to/dimension-update.csv"
HISTORY_ENTRY_URL=$(curl --silent --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:application/x.de.qvantum-plan.aspect-with-structure;charset=UTF-16" \
  --data-binary "@${CSVFILE}" \
  "${STRUCTURE_UPLOAD_URL}"

Uploading a structure with a JSON representation¶

The following representations describe the payload for a JSON structure upload.

The structure representation

 {
      "businessKey": "structure",
      "displayName": "structureDisplayName",
      "type": "KEY_FIGURES | STANDARD",
      "layers" : [<HIERARCHYLAYER_DEFINITION>, ... , <HIERARCHYLAYER_DEFINITION>], 
      "customAttributes" : [<CUSTOM_ATTRIBUTE_DEFINITION>, ... , <CUSTOM_ATTRIBUTE_DEFINITION>],
      "rootElements": [<ELEMENT_DEFINITION>, ... , <ELEMENT_DEFINITION>]
 }

The hierarchy layer definition (optional within the structure representation) :
```
 {
      "businessKey": "layer name"
 }
```
The custom attribute definition (optional within the structure representation) :
```
 {
      "businessKey": "custom attribute name"
 }
```

The structure representation contains a list of element definitions (one for every root node in the structure) :

 {
      "businessKey" : "businessKey",
      "displayName" : "displayName",
      "description" : "description",
      "inputAllowed" : "MAYBE",
      "aggregationRule" : "SUM",
      "valueTypeReference": {
        "businessKey" : "valueType"
      },
      "customAttributes" : [<CUSTOM_ATTRIBUTE_DEFINITION_FOR_ELEMENTS>, ... <CUSTOM_ATTRIBUTE_DEFINITION_FOR_ELEMENTS>],
      "children : [<ELEMENT_DEFINITION>, ..., <ELEMENT__DEFINITION>]
 }

'businessKey', 'displayName' and 'inputAllowed' are mandatory properties. The other properties are optional depending on the structure type. 'children' and 'customAttributes' may be omitted if no children or custom attributes exist.

The custom attribute definition for structure elements (optional within a element representation):

 {
      "customAttributeReference" : {
         "businessKey" : "attribute name in structure definition"
      },
      "value" : "attribute value",
      "inputAllowed" : "MAYBE"
 }

'value' and 'inputAllowed' are optional properties here.

curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:application/x.de.qvantum-plan.external-structure+json" \
  --data @/absolute/path/to/structure.json \
  "${STRUCTURE_UPLOAD_URL}"

Uploading structure updates 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. Polling for status updates works like for the initial model upload

Important

A full interaction path with the QVANTUM public API regarding dimension updates is demonstrated in the script qvantum-dimension-update.sh in our sample data set.

Upload more than one structure¶

An upload of more than one structure is quite similar to the asynchronous update of one structure with a JSON representation as described above.

The link to the update URL is given in the scope of the underlying planning application resource:

# link to structure update URL in scope of a planning application resource
STRUCTURES_UPLOAD_URL=$(curl --silent --request GET \
   --header "Authorization:Bearer ${TOKEN}" \
   "${PLANNING_APP_URL}" | jq --raw-output ._links.structuresUploads.href)

The structures representation:

 {
      "structures": [<STRUCTURE_DEFINITION> ..., <STRUCTURE_DEFINITION>]
 }

The structure definition is described in the chapter above. The upload can be performed with the following curl-statement:

curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:application/x.de.qvantum-plan.external-structures+json" \
  --data @/absolute/path/to/structures.json \
  "${STRUCTURES_UPLOAD_URL}"

Upload full model update¶

A full model update consists of the upload of an updated full planning model definition as described in planning model definition. Given by the observation that some partial update operations factually create new models instead of changing existing ones and internal technical limitations, some update operations are not supported:

adding new/removing existing dimensions
changes of dimension roles, except for "Zeit"
changes in dimension singular/plural labels

Asynchronous upload¶

Technically, a planning model update upload is formulated in the same way as a model definition upload with the upload URL being the only difference. While a planning model definition upload is done in the scope of a planning application, in which a cube must first be defined, a planning model update is done in the scope of a cube. The particular upload URL is available in the link section of a cube resource (cf. navigating to a cube). As for planning model definitions, each uploaded file allows to further specify the used charset with an additional parameter:

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.

Upload Planning Model Update

# link to model update URL in scope of cube resource
MODEL_UPDATE_URL=$(curl --silent --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_URL}" | jq --raw-output ._links.modelUploads.href)``

# variable definitions analogous to planning model definition
curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:multipart/form-data" \
  --form "cubeKey=1" \
  --form "cubeLabel=Cube" \
  --form "planning-model=@${PLANNING_MODEL_PATH};type=text/csv" \
  --form "1=@${DIM_COSTS_PATH};type=text/csv" \
  --form "2=@${DIM_COST_CENTERS_PATH};type=text/csv" \
  --form "3=@${DIM_SCENARIOS_PATH};type=text/csv" \
  "${MODEL_UPDATE_URL}"

Upload Planning Model Update - Explicit Charsets

# link to model update URL in scope of cube resource
MODEL_UPDATE_URL=$(curl --silent --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  "${CUBE_URL}" | jq --raw-output ._links.modelUploads.href)``

# variable definitions analogous to planning model definition
curl --show-headers --request POST \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Content-Type:multipart/form-data" \
  --form "cubeKey=1" \
  --form "cubeLabel=Cube" \
  --form "planning-model=@${PLANNING_MODEL_PATH};type=text/csv;charset=UTF-16" \
  --form "1=@${DIM_COSTS_PATH};type=text/csv" \
  --form "2=@${DIM_COST_CENTERS_PATH};type=text/csv;charset=UTF-16" \
  --form "3=@${DIM_SCENARIOS_PATH};type=text/csv" \
  "${MODEL_UPDATE_URL}"

Uploading a planning model update with the above request is again 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 allows to monitor the status of planning model definition processing on QVANTUM side. In error cases, error messages are included in the corresponding history entry payloads.

Important

A full interaction path with the QVANTUM public API regarding planning model updates is demonstrated in the script qvantum-model-update.sh in our sample data set.

Exporting Planning Applications¶

Once a planning model has been defined in the context of a planning application (cf. planning model definition), it is possible to export individual structures or the complete model.

Structure Export¶

Individual structures can be exported as a CSV file or as a JSON representation with a single API request to the structure resource (cf. navigating to a structure). The accept header must be set to application/x.de.qvantum-plan.aspect-with-structure and supports the following (optional) parameter:

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.

Structure Export (CSV with Windows-1252 encoding)

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.aspect-with-structure;charset=Windows-1252" \
  --output structure.csv \
  "${STRUCTURE_URL}"

Structure Export (JSON)

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.external-structure+json" \
  "${STRUCTURE_URL}"

The result of this request is the same structure representation as described in the structure upload (cf. uploading a structure with a JSON representation).

Export all Structures¶

All structures in a planning application can be exported with this single API request to the allStructes resource (cf. navigating to all structures):

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/x.de.qvantum-plan.external-structures+json" \
  "${ALL_STRUCTUREs_URL}"

The result of this request is a JSON representation as described in the chapter (cf. upload more than one structure).

Full Model Export¶

The complete model can be exported in two different formats, i.e. as an Excel file or as a ZIP-archived collection of CSV files.

Important

The export of a planning model only includes a planning model definition. Definitions of forms or planning teams are explicitly not included.

A planning model export is done with a single API request to the cube resource. The export format is controlled with the specification of an Accept header, as demonstrated in the requests below.

Planning Application Model Export (zipped CSV)

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/zip" \
  --output model.zip \
  "${CUBE_URL}"

Planning Application Model Export (Excel)

curl --request GET \
  --header "Authorization:Bearer ${TOKEN}" \
  --header "Accept:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" \
  --output model.xlsx \
  "${CUBE_URL}"