Analytical Workspace data extraction API
Function¶
Extracts data from Analytical Workspace partitions.
URL¶
<api-root>/aih/data/v1/database_id/workspace_code/
If the application is configured to manage the GDPR/DAC6regulations, the Analytical Workspace data values on which the Personal Data Protectionoption is active will be exported in encrypted form.
Functions¶
- Physical datasets
- Virtual Dataset
- Datasource
All functions relate to data from the Reporting Layer of the specified Analytical Workspace.
For each function, the system may ask for filter parameters to be specified for the Process, Scenario, Period and Entity, in order to limit the volume of data returned. The request will depend on the presence of the following elements in the dataset:
- the type of partitioning
- the presence of columns for the Scenario, Period and Entity dimensions.
The data returned are subject to the user's visibility restrictions for the Scenario, Period and Entity dimensions.
Parameters¶
Non-partitioned Dataset/Virtual Dataset or Datasource¶
Data are read from the only existing partition and are potentially filtered on the basis of the presence of fields related to the Scenario, Period and Entity dimensions.
| Parameter | Required | Description |
|---|---|---|
| Process | Optional | If defined, it must always be with a code (e.g. $PROCESS). |
| Scenarios | Only required if a column for the Scenario dimension is defined in the dataset. Only required if the Dataset has a column for the Scenario dimension defined. | Can be defined with: - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values |
| Period | Only required if a column for the Period dimension is defined in the dataset. Only required if the Dataset has a column for the Period dimension defined. | Can be defined with: - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values |
| Entity | Only required if a column for the Entity dimension is defined in the dataset. Only required if the Dataset has a column for the Entity dimension defined. | In datasets, it can be defined with: - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values In virtual datasets, it can only be defined with a single code. |
Datasets/Virtual Datasets partitioned by Business Cycle¶
The data are read from the partitions corresponding to the values assigned to the Process, Scenario and Period parameters and potentially filtered based on fields related to the Scenario, Period and Entity dimensions. If the selected process is a single submission process, the values of the Scenario and Period parameters are not used to obtain the partitions from which to read.
| Parameter | Required | Description |
|---|---|---|
| Process | Mandatory | Must always be defined with a code (e.g. $PROCESS). |
| Scenarios | Mandatory | Can be defined with: - - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values |
| Period | Mandatory | Can be defined with: - - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values |
| Entity | Only required if the Dataset has a column for the Entity dimension defined. | In datasets, it can be defined with: - - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values In virtual datasets, it can only be defined with a single code. |
Datasets and Virtual Datasets partitioned by Business Cycle and Entity¶
The data are read from the partitions corresponding to the values assigned to the Process, Scenario and Period parameters and potentially filtered based on fields related to the Scenario, Period and Entity dimensions. If the selected process is a single submission process, the values of the Scenario and Period parameters are not used to obtain the partitions from which to read.
| Parameter | Required | Description |
|---|---|---|
| Process | Mandatory | Must always be defined with a code (e.g. $PROCESS). |
| Scenarios | Mandatory | Can be defined with: - - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values |
| Period | Mandatory | Can be defined with: - - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values |
| Entity | Mandatory | In datasets, it can be defined with: - - a code (e.g. 001) - a list of codes separated by commas (e.g. 001,002,003) - the value $ALL to tell the system to select all values - In virtual datasets, it can only be defined with a single code. |
Request to check parameters by Function¶
Returns the list of mandatory parameters of a dataset's Functions:
<api-root>/aih/metadata/v1/database_id/workspace_code/dataset_code/Parameters
Examples¶
Examples related to physical datasets can be applied to virtual datasets by replacing the syntax Dataset with VirtualDataset.
For example, Dataset_000001(Scenario='2020ACT',Period='12',Entity='001') becomesVirtualDataset_000001(Scenario...).
For Datasources, the syntax is similar (Datasource_000001(Scenario...)) but the extraction logic differs due to the absence of data partitioning.
| Parameters | URL | Description |
|---|---|---|
| Non-partitioned datasets | ||
| None | <api-root>/data/v1/TGK_APP/001/Dataset_000001() |
Data are read unfiltered from the only existing partition. |
| Non-partitioned dataset with Scenario, Period and Entity fields | ||
| - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Scenario='2020ACT',Period='12',Entity='001') |
Data are read filtered by Scenario, Period and Entity from the only existing partition. |
| Dataset partitioned by Business Cycle in single submission Process | ||
| - Process - Scenarios - Period | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='BUDGET',Scenario='2020BDG',Period='12') |
Data are read ignoring the Scenario and Period filters from the only existing partition for the selected Process. |
| Dataset partitioned by Business Cycle with Scenario and Period fields in single submission Process | ||
| - Process - Scenarios - Period | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='BUDGET',Scenario='2020BDG',Period='12') |
Data are read filtered by Scenario and Period from the only existing partition for the selected Process. |
| Dataset partitioned by Business Cycle with Scenario, Period and Entity fields in single submission Process | ||
| - Process - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='BUDGET',Scenario='2020BDG',Period='12',Entity='001') |
Data are read filtered by Scenario, Period and Entity from the only existing partition for the selected Process. |
| Dataset partitioned by Business Cycle in Scenario/Period submission Process | ||
| - Process - Scenarios - Period | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='ACTUAL',Scenario='2020ACT',Period='12') |
The data is read unfiltered from the only existing partition for the selected Process, Scenario and Period. |
| Dataset partitioned by Business Cycle with Entity field in Scenario/Period submission Process | ||
| - Process - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='ACTUAL',Scenario='2020ACT',Period='12',Entity='001') |
Data are read filtered by Entity from the only existing partition for the selected Process, Scenario and Period. |
| Dataset partitioned by Business Cycle and Entity in single submission Process | ||
| - Process - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='BUDGET',Scenario='2020BDG',Period='12',Entity='001') |
Data are read ignoring the filters for Scenario and Period from the only existing partition for the selected Process and Entity. |
| Dataset partitioned by Business Cycle and Entity with Scenario and Period fields in single submission Process | ||
| - Process - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='BUDGET',Scenario='2020BDG',Period='12',Entity='001') |
Data are read filtered by Scenario and Period from the only existing partition for the selected Process and Entity. |
| Dataset partitioned by Business Cycle and Entity by selecting a Scenario/Period submission Process | ||
| - Process - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Dataset_000001(Process='ACTUAL',Scenario='2020ACT',Period='12',Entity='001') |
The data is read unfiltered from the only existing partition for the selected Process, Scenario, Period and Entity. |
| Datasource with Scenario, Period and Entity fields | ||
| - Scenarios - Period - Entity | <api-root>/aih/data/v1/TGK_APP/001/Datasource_000003(Scenario='2016BDG',Period='12',Entity='001) |
Data are read filtered by the selected Scenario, Period and Entity. |
| Datasource without Scenario, Period or Entity fields | ||
| None | <api-root>/aih/data/v1/TGK_APP/001/Datasource_000003() |
The data are read without filters. |
OData query options¶
A list of supported OData query options is provided below.
| Example | Description |
|---|---|
| $count | |
| http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?$count=true | Returns the number of records in the @odata.count annotation, but not the record values. |
| $top | |
| http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?$top=100 | Returns the first 100 records. |
| $skip | |
| http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?$top=100&$skip=50 | Returns only the first 100 records starting with record 51. This option cannot be used without the $top option. |
| $orderby | |
| http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?$orderby=Account desc,Entity asc | Returns records sorted by Account and Entity. The use of this option is advisable if the $top and $skip options (in packet transfer mode) are also being used, to indicate an order to be followed both to improve reading performance and to prevent transferring an individual record several times in different packets. |
| $select | |
| http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?$select=Scenario,Period,Entity,Account,Amount | Returns only values related to the Scenario, Period, Entity and Amount fields, for every record. This allows you to select the necessary information only, thus improving performance (transfer time and memory allocated by CCH Tagetik's application server). |
| $filter | |
| http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?$filter=Account eq '001' a Amount ne 0 | Returns only values with Account = 01 and Amount ≠ 0, for every record. |
$filter details¶
The filter value depends on the type of field to be filtered:
- for Text fields, the value must be delimited between single quotes (superscript),
- for Numeric fields, the value must not be delimited,
- for Timestamp fields, the value must not be delimited and must be indicated using the format yyyy-mm-ddThh:mi:ssZ (e.g. 2021-01-01T23:45:12Z), or yyyy-mm-ddThh:mi:ss.fffZ (e.g. 2021-01-01T02:08:06.123Z). The indicated value must be in UTC (Coordinated Universal Time).
Below are the details of the operators managed:
| Operator | Description | Example |
|---|---|---|
| Eq | Equal | /Suppliers?$filter=Address/City eq 'Redmond' |
| In | In | /Suppliers?$filter=Address/City in ('Redmond','London') |
| Ne | Not equal | /Suppliers?$filter=Address/City ne 'London' |
| Gt | Greater than | /Products?$filter=Price gt 20 |
| Ge | Greater than or equal | /Products?$filter=Price ge 10 |
| Lt | Less than | /Products?$filter=Price lt 20 |
| Le | Less than or equal | /Products?$filter=Price le 100 |
| And | Logical and | /Products?$filter=Price le 200 and Price gt 3.5 |
| Or | Logical or | /Products?$filter=Price le 3.5 or Price gt 200 |
| Not | Logical negation | /Products?$filter=not (Address/City eq 'Redmond') |
Other parameters¶
It is possible to specify a custom parameter, numberFormat, which controls the data type used for numeric fields: if the parameter value is double, the Double type will be used; if the parameter value is decimal, the Decimal type will be used
If the parameter is not specified, the Double type will be used.
Usage example:
http://127.0.0.1:8080/tagetikcpm/api/aih/data/v1/TGK_APP/001/Datasource_000003()?numberFormat=decimal
Important: API calls have a non-modifiable timeout of 10 minutes. To avoid reaching this timeout, you can reduce the amount of data retrieved by individual API calls by applying one or more of the following strategies:
- If the service has input parameters (Process, Scenario, Period, Entity), it is recommended to specify one or more values rather than selecting everything via the '$ALL' syntax.
- If you do not need all the fields in the dataset, you can extract a subset using the select syntax in the query options (e.g. ?$select=OID,ACCOUNT,COST_CENTER)
- To further limit the data, you can use the filter syntax in the query options (e.g. ?$filter=Account eq '01')
- In the case of large volumes of data, client-side pagination can be used in query options (e.g. ?$top=1000&$skip=1000)