> ## Documentation Index > Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt > Use this file to discover all available pages before exploring further. # Use the DEE > How to extract data from an Atoti Server instance using DEE orders, covering the `getAggregatesQuery`, `mdxQuery`, and `searchFactsQuery` order types, the `order.output.*` configuration properties, and the Enhanced Drillthrough REST endpoints for direct store queries. ## 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][get-aggregates-query], using the locations to find the data * [mdxQuery][mdx-query], same as what can be done in Atoti UI * [searchFactsQuery][search-facts-query], 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](#orderoutputfilenameroot) for more info. | ``-`` | | `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: `-` 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](https://docs.oracle.com/javase/7/docs/api/java/util/Formatter.html#syntax) 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 ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "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 ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "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 ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "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 ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} [ { "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][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. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} public List execute(final ISearchFactsQuery query); ``` The REST service relies on receiving a JSON object following the `JsonSearchFactsQuery` definition. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} Response execute(JsonSearchFactsQuery query); ``` Example of input: ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "@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. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} 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. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} Response executeEnhancedDrillthrough(AJsonDrillthroughQuery query); ``` Examples of input: ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "@type": "DTQuery", "pivotId": "", "context": { "key": "value" }, "locations": [ "a|b|c|d", "x|y|z" ], "measures": [ "measure1", "measure2" ], "isFormatted": true, "firstResult": 0, "maxResults": 1000 } ``` ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "@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. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} public List execute(final ISearchFactsQuery query); ``` The REST service relies on receiving a JSON object following the `JsonSearchFactsQuery` definition. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} Response execute(JsonSearchFactsQuery query); ``` Example of input: ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "@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`. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} public EnhancedDrillthroughTaskInfoDTO cancelEnhancedDrillthrough(); ``` The REST service returns a JSON object following the `EnhancedDrillthroughTaskInfoDTO` definition. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} 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. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} 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. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} public long executeEnhancedCount(final IGetAggregatesQuery getAggregateQuery); ``` The REST service returns a JSON object holding the number. ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} Response executeEnhancedCount(JsonGetAggregatesQuery query); ``` Example of input: ```json theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} { "@type": "GetAggregatesQuery", "pivotId": "", "context": { "key": "value" }, "locations": [ "a|b|c|d", "x|y|z" ], "measureSelections": [ "measure1", "measure2" ] } ``` [get-aggregates-query]: /dee/user-guide/dee-orders#get-aggregates-query-order [mdx-query]: /dee/user-guide/dee-orders#mdx-query-order [search-facts-query]: /dee/user-guide/dee-orders#search-facts-query-order [dee-template-orders]: /dee/user-guide/dee-template-orders