Navigation :

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 and EXCEPTIONAL. For standard extraction job it is recommended to use the NORMAL 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.
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 Remoting
submitTemplateOrders(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) and removeTemplateTask(String) methods for WebServices and Remoting
addTemplateOrder(String, String) and removeTemplateOrder(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);

Example of input:

{
  "@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);

Examples of input:

{
  "@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 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);

Example of input:

{
  "@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);

Example of input:

{
  "@type": "GetAggregatesQuery",
  "pivotId": "",
  "context": {
    "key": "value"
  },
  "locations": [
    "a|b|c|d",
    "x|y|z"
  ],
  "measureSelections": [
    "measure1",
    "measure2"
  ]
}