OLAP via EQL Endpoint Guide

Owned by Marius Navickas
Last updated: Jul 03, 2024 by Pamela Totas (Deactivated)
4 min read

Working with OLAP Reports and the EQL API Endpoint

The effective user for OLAP report requests submitted via the EQL endpoint needs to be configured in the EDS UI, in the Manage tab, Vendor Configuration editor, as the PACE vendor.

The EQL API endpoint for OLAP leverages the PACE SOAP interface ("FeedType": "paceolap"). We have tested the eBQueryPerfAnalsReport SOAP object. The first step is to identify all of the OLAP reports on your application server that are available through the eBQueryPerfAnalsReport SOAP object. The following parameter in the EQL POST Body will be static: "PACE_BusObjType": "eBQueryPerfAnalsReport"

Up to this point, we have tested the "Performance Analysis" Event Type. The SOAP interface and the EQL POST Body filter will be static: "PACE_REPORT_TYPE": "Performance Analysis".
In order to get a current list "Performance Analysis" reports go to Automation Center and select Events in the left navigation, then filter by the "Performance Analysis" Event Type.

In Automation Center the left column is labelled "Event Name". This is synonymous with the terms "OLAP Report", "Report" and "Profile". In the EQL POST Body you'll use: "PACE_PROFILENAME": "OLAP-PerformanceOverviewReport" and the value will be one of the "Event Name"s found through the filtered Automation Center view (see above).
The author of the report will be helpful in identifying the balance of the "filters" which will be needed in the EQL Body. These filters will control the scope of the OLAP calculation.

The next filter controls the account or Entity data used in the calculation. The values can be:

  • a single Entity

  • an Entity List

  • a passed list of Entities separated by a comma (e.g. "12345,22222,54682")

The list of entities will drive the calculation duration and the size of the result set. Please remember that there are default restrictions on these parameters in the API Manager layer. Be careful to limit scope accordingly. The entity filter parameter format is: "PACE_ENTITY_ID": "14268"

Now we get to date filters. There are two types of reports:

  • Reports requiring a single date where there is a fixed period. In this case a single date is passed and the format is: "PACE_EFFECTIVE_DATE": "12/31/2019" (i.e. mm/dd/yyyy)

  • Reports requiring a range of dates which drive the any-date-to-any-date calculation. In this case two filters are needed and the formats are:

    • for the start date of the range: "PACE_FROM_EFFECTIVE_DATE": "01/01/2019"

    • for the end date of the range: "PACE_TO_EFFECTIVE_DATE": "12/31/2019"

Putting it all together for the EQL POST Body you would see something like:
{
  "FeedType": "paceolap",
  "outputFormat": "csv",
  "filters": {
    "PACE_BusObjType": "eBQueryPerfAnalsReport",
    "PACE_REPORT_TYPE": "Performance Analysis",
    "PACE_PROFILENAME": "OLAP-PerformanceOverviewReport",
    "PACE_ENTITY_ID": "14268",
    "PACE_EFFECTIVE_DATE": "12/31/2019"
  }
}

For outputFormat you may choose csv, eagleml, or eaglejson (the default when none is specified).
With csv outputFormat, an additional optional parameter specifies the field delimiter, row delimiter, and if a header should be included, e.g. "outputformat": "csv", "outputFormatDialect": {"delimiter":"|","rowdelimiter":"\n","hasheader":"Y"}

There are three other filters (overrides). When included in the Body these overrides will supersede the setting within the report. Eagle users familiar with submitting reports with overrides will be familiar with these options. The three available overrides (these have been tested with v17R2.22 and have not been tested with v2015) are:

  • Currency

  • Benchmark

  • Grouping.

Starting with the following EQL POST Body Example:
{
  "FeedType": "paceolap",
  "filters": {
    "PACE_BusObjType": "eBQueryPerfAnalsReport",
    "PACE_REPORT_TYPE": "Performance Analysis",
    "PACE_PROFILENAME": "Daily Performance Check",
    "PACE_ENTITY_ID": "9410",
    "PACE_EFFECTIVE_DATE": "10/21/2020"
  } 
}
Currency Override
Overrides like currency override are set using the PACE_OVERRIDE_VALUES filter parameter.
An example for USD:
CURRENCY_CONVERSION!1,F_ALTERNATE_TO_CURRENCY!-1,F_ON_FLY_CURR_CONVERSION!1,F_TO_CURRENCY!608
An example for EUR:
CURRENCY_CONVERSION!1,F_ALTERNATE_TO_CURRENCY!-1,F_ON_FLY_CURR_CONVERSION!1,F_TO_CURRENCY!504
An example for GBR:
CURRENCY_CONVERSION!1,F_ALTERNATE_TO_CURRENCY!-1,F_ON_FLY_CURR_CONVERSION!1,F_TO_CURRENCY!507
The currency values come from instance numbers in the pace_masterdbo.code_values table:

In most installations codes from 455 through 636 list the currencies. These come with a standard installation. Please be sure to confirm that they have not changed from one instance to another since this table can be edited post installation.
The EQL POST Body should be formatted as follows:
{
  "FeedType": "paceolap",
  "outputFormat": "CSV",
  "filters": {
    "PACE_BusObjType": "eBQueryPerfAnalsReport",
    "PACE_REPORT_TYPE": "Performance Analysis",
    "PACE_PROFILENAME": "PERFORMANCE_RETURNS_1",
    "PACE_ENTITY_ID": "0525PSP",
    "PACE_EFFECTIVE_DATE": "04/04/2020",
    "PACE_OVERRIDE_VALUES": "CURRENCY_CONVERSION!1,F_ALTERNATE_TO_CURRENCY!-1,F_ON_FLY_CURR_CONVERSION!1,F_TO_CURRENCY!504"
  }
}
Benchmark Override
To override a Benchmark for an OLAP report you will need to add PACE_OVERRIDE_VALUES parameter. The parameter will house 2 flags, BMRK_ORDER_OVRD and BMRK_VAL_OVRD. BMRK_ORDER_OVRD will be defaulted to "##DEFINDEX". BMRK_VAL_OVRD will be an entity where entity_type='INDX'.
An example EQL POST Body follows:
{
  "FeedType": "paceolap",
  "filters": {
    "PACE_BusObjType": "eBQueryPerfAnalsReport",
    "PACE_REPORT_TYPE": "Performance Analysis",
    "PACE_PROFILENAME": "PGI_IT_PERFORMANCE_RETURNS_1",
    "PACE_ENTITY_ID": "9410",
    "PACE_EFFECTIVE_DATE": "05/31/2020",
    "PACE_OVERRIDE_VALUES": "BMRK_ORDER_OVRD!##DEFINDEX,BMRK_VAL_OVRD!INDX105"
  } 
}
Grouping Rule Override
For the grouping rule override you will also utilize the PACE_OVERRIDE_VALUES parameter. There will be 2 flags that need to be added, ATTRIB_LEVEL and GRPRULE.
The GRPRULE flag will depend on what dictionaries are set up in rulesdbo.dictionaries table. The override will be the dictionary_id from this table.

The ATTRIB_LEVEL flag will depend on what levels are set up in the rulesdbo.dictionary_format table. Depending on which level you want to expose, you will need include this number. A sample query is below.

The resulting EQL POST Body
{
  "FeedType": "paceolap",
  "filters": {
    "PACE_BusObjType": "eBQueryPerfAnalsReport",
    "PACE_REPORT_TYPE": "Performance Analysis",
    "PACE_PROFILENAME": "Daily Performance Check",
    "PACE_ENTITY_ID": "9410",
    "PACE_EFFECTIVE_DATE": "10/21/2020",
    "PACE_OVERRIDE_VALUES": "ATTRIB_LEVEL!2,GRPRULE!PERF(9)"
  } 
}

For entity level dictionaries you may not see data when using dictionary overrides with just the attribution level. An extra section needs to be added to this override parameter to return the proper level of data.

It is an aggregation on the TOTAL level that looks as follows:

ATTRIB_LEVEL!1,GRPRULE!AGGR(10059)~PERF(9)

Currency and Benchmark overrides may be combined as follows:
{
  "FeedType": "paceolap",
  "outputFormat": "csv",
  "filters": {
    "PACE_BusObjType": "eBQueryPerfAnalsReport",
    "PACE_REPORT_TYPE": "Performance Analysis",
    "PACE_PROFILENAME": "Performance_Returns",
    "PACE_ENTITY_ID": "9410",
    "PACE_EFFECTIVE_DATE": "05/31/2020",
"PACE_OVERRIDE_VALUES": "CURRENCY_CONVERSION!1,F_ALTERNATE_TO_CURRENCY!-1,F_ON_FLY_CURR_CONVERSION!1,F_TO_CURRENCY!504,BMRK_ORDER_OVRD!##DEFINDEX,BMRK_VAL_OVRD!INDX105"
  }
}

The Eagle ACCESS API Manager sets a 2 minute default timeout. Reports that take over two minutes to run will fail.
You may need to redesign the report, or, in some cases, it may make sense to override the timeout.

Quick reports used in high volume may benefit from small batches. Concurrency capabilities directly align with the application server configuration. In general please engage professional services and technical delivery services to discuss optimizing OLAP API usage.