Use the DEE - Atoti Hub

Extracting data

A DEE order defines a query type and an output destination, submitted to one of the REST endpoints listed below.

Request endpoints

The services are exposed at the following paths for REST version v1:

Data Extraction Engine: /connectors/rest/dee/v1/
Enhanced Drillthrough: /connectors/rest/enhanceddt/v1

Order structure

The DEE Order defines what to extract and how to extract data. A DEE Order can extract data from Atoti Server by executing one of the following queries:

getAggregatesQuery, using the locations to find the data
mdxQuery, same as what can be done in Atoti UI
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 a 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 attributes

List of the DEE Order attributes and their default value.

Property	Description	Default or Required
`order.name`	Name of the Order	Required
`order.priority`	Priority of the DEE order	`NORMAL`
`order.output.locale`	Locale to use	`Locale.getDefault()`
`order.output.filenameRoot`	Name of file for Extraction to write to. See property explanation for more info.	`<taskId>`-`<order name with "_" instead of spaces>`
`order.output.hasHeader`	(DEPRECATED) Flag value for indicating whether or not the file has a header	`true`
`order.output.hasColumnNamesHeader`	Flag value for indicating whether or not the file has a header made of the column names	`true`
`order.output.tailoredColumnsHeader`	If provided, will create the custom header printed as first row in the file
`order.output.surroundWrittenDataWithQuotes`	Flag value for surrounding the data with quotes	`false`
`order.output.transformNAToEmpty`	Flag value to replace the N/A values with an empty string for level output columns	`false`
`order.output.measureValueIfNull`	Replacement value if the measure is NULL	Empty string
`order.output.csvSeparator`	CSV column separator	`,`
`order.output.maxRowsPerFile`	Max rows per file	`1000000`
`order.output.maxRawDataSizePerFile`	Max raw data size generated per file, use `-1` for unlimited	`1073741824` (1GB)
`order.output.compressionType`	Type of compression, possible values are: `NONE`, `ZIP` and `GZIP`	`ZIP`
`order.output.keepOutputFileIfFailure`	Flag value to keep the output file generated in case of failure	`true`
`order.output.format`	Format used by the measures	`##0.000`

order.output.filenameRoot

The filenameRoot property is handled differently depending on the value provided. For example, if a DEE order is executed with the name “File Extraction Example” which has a taskId of “123456_1”, the filename the extraction will write to will differ based on what is provided to the filenameRoot property: If the following is provided:

N/A: No value was provided, the default filename will be: <taskId>-<order_name> or 123456_1-File_Extraction_Example.csv.
output: Only the file name was provided; the file extension is added onto the file name, resulting in output.csv.
output.csv: File name and extension were provided, so nothing is added. The file will be output.csv.
directory/output.csv: An extra directory was provided; this file will be created inside a nested directory directory. The directory must exist prior to the extraction, the DEE will not create nested directories.
output_%1$TY-%1$Tm-%1$Td_%1$TH-%1$TM-%1$TS: A datetime formatter was provided. The current time is used to fill in the Year, Month, Day, Hour, Minute, and Second into the file’s name. This generates a file (just before the new year) with output_2020-12-31_11-59-59.csv. Read Java String Formatting for more information on formatting syntax.

For Enhanced Drillthrough orders, the time of the extraction will always be appended to the end of the file name. For example: output_20201231125959.csv

Output columns

Several types of output columns are provided by the DEE.

Type	Description
`echo`	Output a constant
`level`	Outputs the current member of the provided level
`lowestLevel`	Always outputs the lowest level member of the provided hierarchy
`measure`	Outputs the measure value
`fieldOutputColumn`	When doing a SearchFact query, it outputs the field value
`extractionProcedureValue`	When configuring an extraction procedure, it outputs a value from it

The output columns can have different parameters (see examples below). Nevertheless, all of them share the following parameter:

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 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",
  "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 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 facts query 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
maxResults, limit the number of facts returned from the extraction. The default limit is 1,000.

Some notes on null properties:

If no output columns are specified, then by default all Columns of the specified store will be exported.
If no filter is provided, then all Facts will be selected by default.

JSON format

{
  "name": "DEE Search Facts Query Test",
  "queries": [
    {
      "@type": "SearchFactsQuery",
      "pivotId": "MyCube",
      "storeName": "My Store To Export From",
      "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, the LocalDate field can be used in the conditions by passing a String value having the format yyyy-MM-dd. In this case, the value is automatically translated to a LocalDate object.

Multiple orders

The format supports having 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. Compared 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 location of an aggregates query
the filename root
the tailored header

More can be read on the DEE Template Orders page.

Data Extraction Service

For maintenance’s sake, it is best to keep the definition of the templates in 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. To provide a custom implementation, the DataExtractionService.dataExtractionTemplates() method must be overridden.

Enhanced Drillthrough REST inputs

Execute search facts query input

This retrieves 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 relies 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 generates the content of a drillthrough query into a CSV file. It limits the data volume required to be transferred between Atoti Server and the UI. The drillthrough 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 retrieves 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 relies 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 cancels the current drillthrough execution. If there is one, it returns the status of the drillthrough, 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 Drillthrough 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 Atoti Server 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"
  ]
}

​Extracting data

​Request endpoints

​Order structure

​DEE Order attributes

​order.output.filenameRoot

​Output columns

​Get aggregates query order

​JSON format

​MDX query order

​JSON format

​Search facts query order

​JSON format

​LocalDate type handling

​Multiple orders

​JSON format

​Order templates

​Data Extraction Service

​Enhanced Drillthrough REST inputs

​Execute search facts query input

​Execute Enhanced Drillthrough query input

​Retrieve Enhanced Drillthrough input

​Cancel Enhanced Drillthrough

​Get Enhanced Drillthrough max rows

​Execute Enhanced count

Extracting data

Request endpoints

Order structure

DEE Order attributes

order.output.filenameRoot

Output columns

Get aggregates query order

JSON format

MDX query order

JSON format

Search facts query order

JSON format

LocalDate type handling

Multiple orders

JSON format

Order templates

Data Extraction Service

Enhanced Drillthrough REST inputs

Execute search facts query input

Execute Enhanced Drillthrough query input

Retrieve Enhanced Drillthrough input

Cancel Enhanced Drillthrough

Get Enhanced Drillthrough max rows

Execute Enhanced count