Interacting With the DEE

Extracting Data

This document will help guide 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 allows you to define what to extract and how to extract data. A DEE Order can extract data from AP by executing one of the following Queries:

• 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 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

DEE Order Values

List of the default values used by the DEE Order if none are specified: The following table outlines the different properties of the DEE Orders and if they are required. If a property is not required than there is a default value. Parameters with default values do not need to be specified unless the default value is not desired.

order.output.filenameRoot

The filenameRoot property is handled differently depending on the value provided.

For example, if we execute a DEE order with the name “File Extraction Example” who has a taskId of “123456_1”, then the filename our extaction will write to will differ based on what we provide to the filenameRoot property:

If we provide:

• N/A: No value was provided, the default filename will be: <taskId>-<order_name> or 123456_1-File_Extraction_Example.csv.
• output: Only file name was provide, we will add file extension onto file name. So we will get output.csv.
• output.csv: File name and extension was provided so we will do nothing. Our file will be output.csv.
• directory/output.csv: Extra directory was provided, this file will be created inside of a nested directory directory. The directory must exist prior to the extraction, the DEE wil not create nested directories.
• output_%1$TY-%1$Tm-%1$Td_%1$TH-%1$TM-%1$TS: A datetime formatter was provided. We will use the current time to fill in the Year, Month, Day, Hour, Minue, Second into the file’s name. We will generate a file (just before new years) 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

The output columns can have different parameters (c.f. examples below). Nevertheless, all of them are sharing the following one

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.

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 location of a aggregates 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

// Execute a client().post()
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 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);

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"
  ]
}