Interacting With the DEE
Extracting Data
This document will help guid you through the different ways of extracting data from an ActivePivot instance as well as how to use the various REST endpoints provided by the DEE.
Order File Formats
The DEE order format allow you to execute the extraction of one or multiple queries which can be:
- getAggregatesQuery, using the locations to find the data
- mdxQuery, same as what can be done in ActiveUI
- searchFactsQuery, to extract the fact from the indexer
Every DEE order shares the same base format which includes:
- name, simple name defining the kind of query
- priority, defines the order in which the extraction requests are executed. The values are:
VERY_LOW
,LOW
,NORMAL
,HIGH
,VERY_HIGH
,CRITICAL
andEXCEPTIONAL
. For standard extraction job it is recommended to use theNORMAL
priority so it does not interfere too much with the internal jobs - deeOutput/deeCsvOutput, defines which data are part of the extraction
Specifying multiple queries implies the following limitations:
- All the query types must be the same (e.g. only MDX)
- All the queries must target the same cube or store
- All the queries are sharing the same output columns, queries must make sure they provide the underlying data
DEE Order default values
List of the default values used by the DEE Order if none are specified
Property | Description | Default value |
---|---|---|
priority |
Priority of the DEE order | NORMAL |
locale |
Locale to use | Locale.getDefault() |
filenameRoot |
File name root for the generated file | <taskId> -<order name with "_" instead of spaces> |
hasHeader |
(DEPRECATED) Flag value for indicating whether/not the file has a header | true |
hasColumnNamesHeader |
Flag value for indicating whether/not the file has a header made of the column names | true |
tailoredColumnsHeader |
If provided, will create the custom header printed as first row in the file | |
surroundWrittenDataWithQuotes |
Flag value for surrounding the data with quotes | false |
transformNAToEmpty |
Flag value to replace the N/A values to an empty string | false |
measureValueIfNull |
Replacement value if the measure is NULL | Empty string |
csvSeparator |
CSV column separator | , |
maxRowsPerFile |
Max rows per file | 1000000 |
maxRawDataSizePerFile |
Max raw data size generated per file, use -1 for unlimited |
1073741824 (1GB) |
compressionType |
Type of compression, possible values are: NONE , ZIP and GZIP |
ZIP |
keepOutputFileIfFailure |
Flag value to keep the output file generated in case of failure | true |
format |
Format used by the measures | ##0.000 |
Output columns
Several types of output columns are provided by the DEE
Type | Description | |
---|---|---|
echo |
Output a constant | |
level |
Allows to output the current member of the provided level | |
lowestLevel |
Allows to always output the lowest level member of the provided hierarchy | |
measure |
Allows to output the measure value | |
field |
When doing a SearchFact query, it allows to output the field value | |
extractionProcedureValue |
When configuring an extraction procedure, it allows an output value from it |
The output columns can have different parameters (c.f. examples below). Nevertheless, all of them are sharing the following one
NAme | Description | |
---|---|---|
header |
Header column value | |
prefix |
Prefix added to the value before the output | |
suffix |
Suffix added to the value before the output |
Get aggregates query DEE order
Retrieves the aggregates measures for the given locations.
The getAggregatesQuery is defined by:
- pivotId, the name of the cube to execute the query on
- contextValues, the values used during the context of the extraction (e.g. subCubeProperties, queriesTimeLimit, …)
- locations, the specific cells part of the extraction. Location can be provided in the following formats:
- Full location string, Ex:
AllMember|AllMember\MemberValue
- Level member value pairs, Ex:
Level@Hierarchy@Dimension=Value;Level@Hierarchy@Dimension=[*]
.- You will only need to specify expressed levels, all Hierarchies expressed at the AllMember level can be omitted.
- “Level@Hierarchy@Dimension=MemberValue” - Matches exact member value.
- “Level@Hierarchy@Dimension=[*]” - expands all values
- “Level@Hierarchy@Dimension” - All Member, not needed as all Hierarchies not defined will default to AllMember.
- Full location string, Ex:
- measureSelections, the aggregates part of the extraction
JSON format
{
"name": "DEE Get Aggregates Query Test",
"priority": "LOW",
"queries": [
{
"@type": "GetAggregatesQuery",
"pivotId": "MyCube",
"context": {},
"locations": [
"Level@Hierarchy@Dimension=ExplicitMemberValue;Level@Hierarchy@Dimension=[*]"
],
"measureSelections": [
"contributors.COUNT",
"custom.MyMeasure"
]
}
],
"output": {
"@type": "fileOutput",
"locale": "fr",
"hasColumnNamesHeader": false,
"filenameRoot": "test%1$TY%1$Tm%1$Td%1$TH%1$TM%1$TS",
"columns": [
{
"@type": "levelOutputColumn",
"header": "Business Date",
"dimensionName": "BusinessDate",
"hierarchyName": "BusinessDate",
"levelName": "Date"
},
{
"@type": "echoOutputColumn",
"header": "Constant of the Day",
"value": "This is my Constant of the Day"
},
{
"@type": "levelOutputColumn",
"header": "Characteristic A",
"dimensionName": "MyHierarchyDim1",
"hierarchyName": "MyHierarchyDim1",
"levelName": "MyHierarchyLevel1a"
},
{
"@type": "levelOutputColumn",
"header": "Characteristic B",
"dimensionName": "MyHierarchyDim2",
"hierarchyName": "MyHierarchyDim2",
"levelName": "MyHierarchyLevel2a"
},
{
"@type": "levelOutputColumn",
"header": "Characteristic C",
"dimensionName": "MySlicerDim",
"hierarchyName": "MySlicerDim",
"levelName": "MySlicerLevel"
},
{
"@type": "measureOutputColumn",
"header": "Contributors Count",
"name": "contributors.COUNT"
},
{
"@type": "measureOutputColumn",
"header": "My Custom Measure",
"name": "custom.MyMeasure"
}
]
}
}
MDX query DEE order
Retrieves the aggregates measures for the given MDX.
The mdxQuery is defined by:
- cellsOnly, indicates if the axis needs to be included into the output CellSetDTO. This value must be false as we need the axis to extract the level members
- contextValues, the values used during the context of the extraction (e.g. subCubeProperties, queriesTimeLimit, …)
- MDX, query to execute
JSON format
{
"name": "DEE MDX Query Test",
"priority": "HIGH",
"queries": [
{
"@type": "MdxQuery",
"mdx": "SELECT NON EMPTY CrossJoin({[Measures].[contributors.COUNT],[Measures].[custom.MyMeasure]}, Hierarchize(DrilldownLevel([MyHierarchyDim1].[ALL].[AllMember]))) ON COLUMNS FROM [MyCube] WHERE ([BusinessDate].DefaultMember, [MySlicerDim].[MySlicerLevel].[SLICER1])",
"context": {}
}
],
"output": {
"@type": "fileOutput",
"filenameRoot": "myfileroot.csv",
"tailoredColumnsHeader": ["COL1", "COL2", "COL3"],
"columns": [
{
"@type": "levelOutputColumn",
"header": "Business Date",
"dimensionName": "BusinessDate",
"hierarchyName": "BusinessDate",
"levelName": "Date"
},
{
"@type": "echoOutputColumn",
"header": "Constant of the Day",
"value": "This is my Constant of the Day"
},
{
"@type": "levelOutputColumn",
"header": "Characteristic A",
"dimensionName": "MyHierarchyDim1",
"hierarchyName": "MyHierarchyDim1",
"levelName": "MyHierarchyLevel1a"
},
{
"@type": "levelOutputColumn",
"header": "Characteristic B",
"dimensionName": "MyHierarchyDim2",
"hierarchyName": "MyHierarchyDim2",
"levelName": "MyHierarchyLevel2a"
},
{
"@type": "levelOutputColumn",
"header": "Characteristic C",
"dimensionName": "MySlicerDim",
"hierarchyName": "MySlicerDim",
"levelName": "MySlicerLevel"
},
{
"@type": "lowestLevelOutputColumn",
"header": "Characteristic D",
"hierarchyName": "MyHierarchyDim3"
},
{
"@type": "measureOutputColumn",
"header": "Contributors Count",
"name": "contributors.COUNT"
},
{
"@type": "measureOutputColumn",
"header": "My Custom Measure",
"name": "custom.MyMeasure"
}
]
}
}
Search Fact query DEE order
Retrieves the facts from the indexer.
The mdxQuery is defined by:
- pivotId, the name of the cube to execute the query on
- filter, powerful way to specify the criteria to select the facts to extract. More information about the syntax here.
- maxResults, limit the number of facts returned from the extraction. The default limit is 1,000.
JSON format
{
"name": "DEE Search Facts Query Test",
"queries": [
{
"@type": "SearchFactsQuery",
"pivotId": "MyCube",
"storeName": "",
"condition": {
"MyField1": "20110131",
"MyField2": "EOD",
"MyField3" : {
"$in" : ["Source1", "Source2"]
}
},
"maxResults": 50
}
],
"output": {
"@type": "fileOutput",
"csvSeparator": "|",
"columns": [
{
"@type": "fieldOutputColumn",
"header": "Field A",
"name": "MyField1"
},
{
"@type": "fieldOutputColumn",
"header": "Field B",
"name": "MyField3"
},
{
"@type": "fieldOutputColumn",
"header": "Field C",
"name": "MyField2"
}
]
}
}
LocalDate type handling
There is no direct support of the LocalDate type in the JSON definitions. That said, you are able to use the LocalDate field in the conditions by passing a String value having the format yyy-MM-dd. In this case, the value is automatically translated to a LocalDate object.
Multiple orders
The format supports to have multiple orders in the same file.
JSON format
[
{
"name": "order1",
"priority": "NORMAL",
"queries": [
{
...
}
],
"output": {
...
}
},
{
"name": "order2",
"priority": "NORMAL",
"queries": [
{
...
}
],
"output": {
...
}
},
...
]
Order Templates
When defining the Data Extraction Service, it is possible to pass a collection of DEE orders which can be used as templates.
Each template will be referenced by name. Compare to a normal DEE Order, a template can contain placeholders which will be replaced by the values provided for the execution.
The placeholders format is as follows: {{placeholder_key}}
.
Placeholders can be used for the following properties:
- the MDX query
- the filename root
- the tailored header
Data Extraction Service
For maintenance sake it is best to keep the definition of the templates into external files. The template file must contain an IDeeOrder
definition in JSON format.
If the property data.extraction.templates.base.dir.path
is provided, the Data Extraction Service will make sure to load all the listed JSON files.
A Map
is built with the template name as key and the IDeeOrder
as value.
If you want to provide a custom implementation, you will need to override the DataExtractionService.dataExtractionTemplates()
method.
Template execution
The execution request of the template is made by calling the:
submitTemplateTask(String, Map<String, String>)
method for WebServices and RemotingsubmitTemplateOrders(JsonTemplateOrder)
method for REST
Example for the REST service
client()
.target("/connectors/rest/dee/VERSION")
.path("/submitTemplateOrders")
.request()
.post(
Entity.json("{\"templateOrderName\": \"DeeOrder1\", \"templatePlaceholders\": {\"COB_DATE\": \"20200101\", \"SLICER\": \"10D\"} }"),
String.class
);
The services also expose the method to add/update and remove a template:
addTemplateTask(String, String)
andremoveTemplateTask(String)
methods for WebServices and RemotingaddTemplateOrder(String, String)
andremoveTemplateOrder(String)
methods for REST
Example for the REST service
client()
.target("/connectors/rest/dee/VERSION")
.path("/addTemplateOrder/yourTemplateName")
.request()
.put(
Entity.json("{\"name\": \"order1\", \"maxMdxNbRetries\": 2, \"priority\": \"NORMAL\", \"queries\": [{\"@type\": \"MdxQuery\", \"mdx\": \"select * from [cube]\"}], \"output\": {\"@type\": \"fileOutput\", \"tailoredColumnsHeader\": [\"COL1\", \"COL2\", \"COL3\"], \"hasColumnNamesHeader\": true, \"extractionProcedure\": {\"pluginKey\": \"InLineVectorValuesEP\", \"properties\": {}}, \"columns\": [{\"@type\": \"echoOutputColumn\", \"value\": \"test\"}, {\"@type\": \"fieldOutputColumn\", \"name\": \"machin\"}, {\"@type\": \"levelOutputColumn\", \"dimensionName\": \"dim1\", \"hierarchyName\": \"hier1\", \"levelName\": \"lvl1\"}, {\"@type\": \"lowestLevelOutputColumn\", \"dimensionName\": \"dim2\", \"hierarchyName\": \"hier2\"}, {\"@type\": \"measureOutputColumn\", \"name\": \"measure1\", \"format\": \"##.##\", \"valueIfNull\": \"N/A\"}, {\"@type\": \"extractProcValueOutputColumn\", \"name\": \"extractProcValue\", \"format\": \"##.##\", \"valueIfNull\": \"NONE\"}] } }")
);
client()
.target("/connectors/rest/dee/VERSION")
.path("/removeTemplateOrder/yourTemplateName")
.request()
.delete();
Enhanced Drillthrough REST Inputs
Execute Search Facts Query Input
This allows you to retrieve the data from a store. The query is executed directly against the Datastore. It is possible to pass conditions to filter the data.
When the pivotId
is specified, the query will be executed against the schema/base store. It is possible to follow the references to filter on some referenced stores.
When the storeName
is specified, the query is only executed against that specific store. Filters can only be specified on the store columns, no reference can be used.
This can be done through the executeSearchFacts
REST method.
The WebServices and Remote services take an instance of the ISearchFactsQuery
interface as parameters.
public List<Object[]> execute(final ISearchFactsQuery query);
The REST service rely on receiving a JSON object following the JsonSearchFactsQuery
definition.
Response execute(JsonSearchFactsQuery query);
{
"@type": "SearchFactsQuery",
"pivotId": "",
"storeName": "",
"condition": {
"ProductQtyMultiplier": {
"$lt" : 1000,
"$gte" : 10
},
"$or" : [
{ "Id" : { "$lte" : 500 } },
{ "Id" : { "$gt" : 100 } },
{ "Desk" : { "$like" : "skB" } }
],
"ProductId" : {
"$in" : [17, 36, 42, 55]
},
"Status" : "DONE",
"$not" : { "IsSimulated" : "LIVE" }
},
"attributes": [
"attr1",
"attr2"
],
"firstResult": 0,
"maxResults": 1000
}
Execute Enhanced Drillthrough Query Input
This allows you to generate the content of a drill-through query into a CSV file. It limits the data volume required to be transferred between ActivePivot and the UI. The drill-through query can either rely on a location or on an MDX query.
This can be done through the executeEnhancedDrillthrough
REST method.
The WebServices and Remote services take either an instance of the IDrillthroughQuery
or IMDXDrillthroughQuery
interfaces as parameters.
public void executeEnhancedDrillthrough(final IDrillthroughQuery query, final String filenameRoot);
public void executeEnhancedMdxDrillthrough(IMDXDrillthroughQuery query, String filenameRoot);
The REST service relies on receiving a JSON object following the JsonDrillthroughQuery
or JsonMdxDrillthroughQuery
definitions.
Response executeEnhancedDrillthrough(AJsonDrillthroughQuery query);
{
"@type": "DTQuery",
"pivotId": "",
"context": {
"key": "value"
},
"locations": [
"a|b|c|d",
"x|y|z"
],
"measures": [
"measure1",
"measure2"
],
"isFormatted": true,
"firstResult": 0,
"maxResults": 1000
}
{
"@type": "MDXDTQuery",
"mdx": "",
"context": {
"key": "value"
}
}
Retrieve Enhanced Drillthrough Input
This allows you to retrieve the data from a store. The query is executed directly against the Datastore. It is possible to pass conditions to filter the data.
When the pivotId
is specified, the query will be executed against the schema/base store. It is possible to follow the references to filter on some referenced stores.
When the storeName
is specified, the query is only executed against that specific store. Filters can only be specified on the store columns, no reference can be used.
This can be done through the retrieveEnhancedDrillthroughResult
REST method.
The WebServices and Remote services take an instance of the ISearchFactsQuery
interface as parameters.
public List<Object[]> execute(final ISearchFactsQuery query);
The REST service rely on receiving a JSON object following the JsonSearchFactsQuery
definition.
Response execute(JsonSearchFactsQuery query);
{
"@type": "SearchFactsQuery",
"pivotId": "",
"storeName": "",
"condition": {
"ProductQtyMultiplier": {
"$lt" : 1000,
"$gte" : 10
},
"$or" : [
{ "Id" : { "$lte" : 500 } },
{ "Id" : { "$gt" : 100 } },
{ "Desk" : { "$like" : "skB" } }
],
"ProductId" : {
"$in" : [17, 36, 42, 55]
},
"Status" : "DONE",
"$not" : { "IsSimulated" : "LIVE" }
},
"attributes": [
"attr1",
"attr2"
],
"firstResult": 0,
"maxResults": 1000
}
Cancel Enhanced Drillthrough
This allows to cancel the current drill-through execution. If there is one, it returns the status of the drill-through, null
otherwise.
This can be done through the cancelEnhancedDrillthrough
REST method.
The WebServices and Remote services return an instance of EnhancedDrillthroughTaskInfoDTO
.
public EnhancedDrillthroughTaskInfoDTO cancelEnhancedDrillthrough();
The REST service returns a JSON object following the EnhancedDrillthroughTaskInfoDTO
definition.
Response cancelEnhancedDrillthrough();
Get Enhanced Drillthrough Max Rows
This returns the maximum number of rows the enhanced drill-through is allowed to extract for a given cube. This setting can be fine tuned by the cube instance.
This can be done through the getEnhancedDrillthroughMaxRows/{pivotId}
REST method for a given ActivePivot Cube ID.
The WebServices and Remote services return directly the number.
public int getEnhancedDrillthroughMaxRows(final String pivotId);
Execute Enhanced Count
This retrieves the contributors.COUNT for a particular location, with some custom logic to ensure appropriate count calculation when dealing with analysis dimensions in the target cube.
This can be done through the executeEnhancedCount
REST method.
The WebServices and Remote services take an instance of IGetAggregatesQuery
interface as parameter.
public long executeEnhancedCount(final IGetAggregatesQuery getAggregateQuery);
The REST service returns a JSON object holding the number.
Response executeEnhancedCount(JsonGetAggregatesQuery query);
{
"@type": "GetAggregatesQuery",
"pivotId": "",
"context": {
"key": "value"
},
"locations": [
"a|b|c|d",
"x|y|z"
],
"measureSelections": [
"measure1",
"measure2"
]
}