> 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/model.md). # Planning Models With the ability to authenticate and to navigate within a tenant context, the first actual use case is the preparation of a plan. Preparation consists of the following steps: 1. [*Build planning model*](#build-planning-model): build a planning model definition as set of CSV files 2. [*Upload planning model*](#upload-planning-model): upload prepared planning model definition 3. [*Upload data*](/dev/cell-data.md#upload-initial-data): upload initial planning data as CSV file 4. [*Upload team*](/dev/team.md#upload-user-definition): upload user information and permission assignments as CSV files 5. [*Import forms*](/dev/forms.md#folders-and-forms-import): import folders and forms as a JSON file ## Build Planning Model Each plan requires a definition of an [*OLAP cube*](https://en.wikipedia.org/wiki/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`. {% hint style="info" %} For a detailed explanation of what each dimension role means for your planning application and how to choose one, see the German user documentation: [Arbeiten mit der Modellvorlage](/user/owner/planung/modell/arbeiten_mit_der_modellvorlage.md#planungsmodell) (German) {% endhint %} {% hint style="info" %} 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`. {% endhint %} 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** ```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](#key-figure-formulas), 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](#key-figure-formulas). Calculations take place with every change in plan 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*](https://en.wikipedia.org/wiki/Tree_\(graph_theory\)#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`. {% tabs %} {% tab title="Structure CSV (Non-Key Figure)" %} ```csv 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 ``` {% endtab %} {% tab title="Structure CSV (Key Figure)" %} ```csv 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;; ``` {% endtab %} {% endtabs %} #### 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** ```csv 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** ```csv 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. {% hint style="info" %} For the business rationale behind these per-value-type aggregation rules, see the German user documentation: [Systemeinstellungen](/user/owner/planung/systemeinstellungen.md#wertetypen) (German) {% endhint %} #### Aggregation Use of the *Aggregation* field determines an operation used for aggregation. This field is not defined for dimensions of role `Kennzahlen`. 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. {% hint style="info" %} For a conceptual introduction to aggregation across dimensions, with a worked example, see the German user documentation: [Arbeiten mit der Modellvorlage](/user/owner/planung/modell/arbeiten_mit_der_modellvorlage.md#aggregation) (German) {% endhint %} 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. {% tabs %} {% tab title="Explicit aggregation" %} ```csv child;parent;label;description;input;aggregation ROOT;;All costs;;yes;sum CHILD1;ROOT;Staff;;yes;sum CHILD2;ROOT;Energy;;yes;sum ``` {% endtab %} {% tab title="Aggregation values omitted, effectless no aggregation" %} ```csv child;parent;label;description;input;aggregation ROOT;;All costs;;yes; CHILD1;ROOT;Staff;;yes;no aggregation CHILD2;ROOT;Energy;;yes;sum ``` {% endtab %} {% tab title="Aggregation column omitted" %} ```csv child;parent;label;description;input ROOT;;All costs;;yes CHILD1;ROOT;Staff;;yes CHILD2;ROOT;Energy;;yes ``` {% endtab %} {% endtabs %} #### Key Figure Formulas {% hint style="info" %} For the general formula syntax and the complete function reference (including all German and English function names), see the German user documentation: [Formeln](/user/owner/planung/modell/rechenoperationen_und_formelsyntax.md) (German) {% endhint %} 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. {% hint style="info" %} `formula` and `formulaForInnerNodes` are the equivalent of what the German user documentation calls "Formel" and "Alternative Formel für aggregierte Ebenen", see: [Arbeiten mit der Modellvorlage](/user/owner/planung/modell/arbeiten_mit_der_modellvorlage.md#formel) (German) {% endhint %} 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** ```csv 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** ```csv 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 plan. Regular formulas are calculated according to the given calculation order. Formulas for inner nodes are always calculated after regular calculations over all dimensions. {% hint style="warning" %} Formulas that violate the [general formula validation rules](/user/owner/planung/modell/rechenoperationen_und_formelsyntax.md#validierungsregeln) (German) — constant formulas and cyclic dependencies — will cause the upload of a planning model definition to fail with an error message. {% endhint %} #### Custom Attributes {% hint style="info" %} For a conceptual introduction to custom attributes and a typical use case, see the German user documentation (the German term is "Merkmal"): [Bearbeiten des Modells in QVANTUM](/user/owner/planung/modell/bearbeiten_des_modells_in_qvantum.md#merkmale) (German) {% endhint %} 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 * `` : 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. * `[ - 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** ```csv 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. {% hint style="warning" %} With a successful upload of a new planning model definition, any previously existing planning model including all forms, all data and all users will effectively be lost in favor of a new planning model. However, updating an existing planning model definition is supported, as described in [upload full model update](#upload-full-model-update). {% endhint %} 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 plan](/dev/general-information.md#navigating-to-a-plan)). ### 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](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. {% tabs %} {% tab title="Upload Planning Model Definition" %} ```sh 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_LINK}" ``` {% endtab %} {% tab title="Upload Planning Model Definition with Explicit Charsets" %} ```sh 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" \ "${MODEL_UPLOAD_LINK}" ``` {% endtab %} {% endtabs %} 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. {% hint style="info" %} 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)! {% endhint %} ### Upload status polling Status polling should be done repeatedly to the same upload history entry resource URL, as demonstrated below. **Status Polling** ```sh curl --show-headers --request GET \ --header "Authorization:Bearer ${TOKEN}" \ "${UPLOAD_LINK}" ``` 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](/dev/general-information.md#localization). {% tabs %} {% tab title="Status Information - Processing" %} ```json { "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/plans/1/uploads/1" } }, "_embedded": { "logEntries": [] } } ``` {% endtab %} {% tab title="Status Information - Complete" %} ```json { "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/plans/1/uploads/1" } }, "_embedded": { "logEntries": [] } } ``` {% endtab %} {% tab title="Status Information - Failed" %} ```json { "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/plans/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]" } ] } } ``` {% endtab %} {% endtabs %} ## Updating Planning Models 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. {% hint style="info" %} 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. {% endhint %} ### 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](#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](/dev/team.md#define-team)). In extreme cases, users can lose all access rights and are thus effectively removed from a plan. {% hint style="info" %} For a worked example of this scenario (deleting a region vs. deleting one of its sub-elements), see the German user documentation: [Bearbeiten des Modells in QVANTUM](/user/owner/planung/modell/bearbeiten_des_modells_in_qvantum.md#auswirkungen-von-strukturanderungen) (German) {% endhint %} **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. {% hint style="info" %} For a worked example of this scenario, see the German user documentation: [Bearbeiten des Modells in QVANTUM](/user/owner/planung/modell/bearbeiten_des_modells_in_qvantum.md#auswirkungen-von-strukturanderungen) (German) {% endhint %} **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](#structure-definitions)) 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](/dev/general-information.md#navigating-to-a-structure)). In both cases the link to the update URL is given in the scope of a structure resource: ```sh # link to structure update URL in scope of structure resource STRUCTURE_UPLOAD_URL=$(curl --silent --request GET \ --header "Authorization:Bearer ${TOKEN}" \ "${STRUCTURE_LINK}" | 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](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. {% tabs %} {% tab title="Upload Structure Update" %} ```sh 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_LINK}" ``` {% endtab %} {% tab title="Upload Structure Update - Explicit Charset" %} ```sh 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_LINK}" ``` {% endtab %} {% endtabs %} #### Uploading a structure with a JSON representation The following representations describe the payload for a JSON structure upload. 1. The structure representation ```json { "businessKey": "structure", "displayName": "structureDisplayName", "type": "KEY_FIGURES | STANDARD", "layers" : [, ... , ], "customAttributes" : [, ... , ], "rootElements": [, ... , ] } ``` 2. The hierarchy layer definition (optional within the structure representation) : ```json { "businessKey": "layer name" } ``` 3. The custom attribute definition (optional within the structure representation) : ```json { "businessKey": "custom attribute name" } ``` 4. The structure representation contains a list of element definitions (one for every root node in the structure) : ```json { "businessKey" : "businessKey", "displayName" : "displayName", "description" : "description", "inputAllowed" : "MAYBE", "aggregationRule" : "SUM", "valueTypeReference": { "businessKey" : "valueType" }, "customAttributes" : [, ... ], "children : [, ..., ] } ``` '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. 5. The custom attribute definition for structure elements (optional within a element representation): ```json { "customAttributeReference" : { "businessKey" : "attribute name in structure definition" }, "value" : "attribute value", "inputAllowed" : "MAYBE" } ``` 'value' and 'inputAllowed' are optional properties here. ```sh 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_LINK}" ``` 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](#upload-status-polling) ### 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](#uploading-a-structure-with-a-json-representation). The link to the update URL is given in the scope of the [underlying plan resource](/dev/general-information.md#plan-json-representation): ```sh # link to structure update URL in scope of a plan resource STRUCTURES_UPLOAD_LINK=$(curl --silent --request GET \ --header "Authorization:Bearer ${TOKEN}" \ "${PLAN_LINK}" | jq --raw-output ._links.structuresUploads.href) ``` The structures representation: ```json { "structures": [ ..., ] } ``` The structure definition is described in the chapter above. The upload can be performed with the following curl-statement: ```sh 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_LINK}" ``` ### 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](#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 plan, 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](/dev/general-information.md#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](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. {% tabs %} {% tab title="Upload Planning Model Update" %} ```sh # link to model update URL in scope of cube resource MODEL_UPDATE_URL=$(curl --silent --request GET \ --header "Authorization:Bearer ${TOKEN}" \ "${CUBE_LINK}" | 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_LINK}" ``` {% endtab %} {% tab title="Upload Planning Model Update - Explicit Charsets" %} ```sh # 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_LINK}" ``` {% endtab %} {% endtabs %} 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. ## Exporting Planning Models Once a planning model has been defined in the context of a plan (cf. [planning model definition](#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](/dev/general-information.md#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`. {% tabs %} {% tab title="Structure Export (CSV with Windows-1252 encoding)" %} ```sh curl --request GET \ --header "Authorization:Bearer ${TOKEN}" \ --header "Accept:application/x.de.qvantum-plan.aspect-with-structure;charset=Windows-1252" \ --output structure.csv \ "${STRUCTURE_LINK}" ``` {% endtab %} {% tab title="Structure Export (JSON)" %} ```sh curl --request GET \ --header "Authorization:Bearer ${TOKEN}" \ --header "Accept:application/x.de.qvantum-plan.external-structure+json" \ "${STRUCTURE_LINK}" ``` {% endtab %} {% endtabs %} The result of this request is the same structure representation as described in the structure upload (cf. [uploading a structure with a JSON representation](#uploading-a-structure-with-a-json-representation)). ### Export all Structures All structures in a plan can be exported with this single API request to the allStructes resource (cf. [navigating to all structures](/dev/general-information.md#navigating-to-all-structures)): ```sh curl --request GET \ --header "Authorization:Bearer ${TOKEN}" \ --header "Accept:application/x.de.qvantum-plan.external-structures+json" \ "${ALL_STRUCTUREs_LINK}" ``` The result of this request is a JSON representation as described in the chapter (cf. [upload more than one structure](#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. {% hint style="info" %} The export of a planning model only includes a [planning model definition](#planning-model-definition). Definitions of forms or planning teams are explicitly not included. {% endhint %} 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. {% tabs %} {% tab title="Planning Model Export (zipped CSV)" %} ```sh curl --request GET \ --header "Authorization:Bearer ${TOKEN}" \ --header "Accept:application/zip" \ --output model.zip \ "${CUBE_LINK}" ``` {% endtab %} {% tab title="Planning Model Export (Excel)" %} ```sh curl --request GET \ --header "Authorization:Bearer ${TOKEN}" \ --header "Accept:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" \ --output model.xlsx \ "${CUBE_LINK}" ``` {% endtab %} {% endtabs %}