1. REST API for Oracle Essbase
  2. Tasks
  3. Execute MDX

Run MDX Query

post

/essbase/rest/v1/applications/{application}/databases/{database}/mdx

Runs an MDX query, returning the results in the selected format (JSON, HTML, Excel, or CSV).

Results are an MDX output set in the requested format (the default is JSON). The output set contains metadata (including page, column, and row tuples) followed by data (a tuple for each row).

Request

Path Parameters
Query Parameters

  • Result format.

    Default Value: JSON
    Allowed Values: [ "XLSX", "CSV", "HTML", "JSON" ]
Body ()

MDX query and preferences. Use this to execute an MDX query directly as a string, rather than running a saved MDX report (a named query saved in the cube context).

Root Schema : MDXInput
Type: object
Show Source
Nested Schema : NamedQueriesPreferences
Type: object
Show Source

  • When the memberIdentifierType: ALIAS and the aliasTable are set to a valid alias table name, the resulting mdx grid contains member names from the specified table. If the value of this property is not set then names are picked from the Default alias table.

  • Set to true to enable query result to return a set of metadata attributes besides the actual data value.

  • Set to true to omit data values from the output set. Default is false.

  • Set to true to return the formatted values for cells of type text or date, or cells associated with a format string. Default is true.

  • Set to true to return the formatted values for cells.

  • Set to true to hide the restricted cell data in the output.

  • Set to true to suppress the meaningless cell data in the output. For example, missing or structurally irrelevant cells.

  • Allowed Values: [ "NAME", "ALIAS", "UNIQUE_NAME" ]

    Specify whether metadata in the output should refer to member names, member aliases, or unique member names (in case of duplicate member enabled outlines).

  • Set to true to return a comma-separated list of all text values associated with the cell in the output.

  • Set to true to add URLs to each applicable cell so users can click through to the source data. This works only if the Essbase drill-through links are configured.

Back to Top

Response

Supported Media Types

200 Response

Mostly OK

As this is a streaming API, it can fail even with status 200. Check for an errorMessage tag in the response to identify any errors.

Body ()
Root Schema : InputStream
Type: object

400 Response

Bad Request

Failed to get the data in the required format.

500 Response

Internal Server Error.

Back to Top

Examples

The following example shows how to run an MDX query as a string.

Script with cURL Command

This example uses cURL to access the REST API from a Windows shell script. The calling user's ID and password are variables whose values are set in properties.bat. 

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/applications/Sample/databases/Basic/mdx?format=JSON" -H Accept:application/octet-stream -H Content-type:application/json --data-binary "@./input_mdx.json" -o output_mdx.json -u %User%:%Password%

Input JSON data

The script above executes, on Sample Basic, the MDX input query stored in input_mdx.json. The input file has the following contents: 

{
    "query" : "SELECT {([Year].generations(2).members)} ON COLUMNS",
    "preferences" : {
            "dataless": false,
            "hideRestrictedData": true,
            "cellAttributes": true,
            "formatString": true,
            "formatValues": true,
            "meaninglessCells": true,
            "textList": true,
            "urlDrillThrough": true,
            "memberIdentifierType": "NAME",
            "aliasTableName": "Default"
        }
}

Where:

  • query contains the input MDX string.

  • preferences contains options you can specify for the output set. For example,

    1. "dataless" : false means do not omit the data values from the output.
    2. "hideRestrictedData" : true means restricted cell data is hidden in the output.
    3. "cellAttributes" : true means the query result can return a set of metadata attributes besides the actual data value.
    4. "formatString" : true means return the formatted values for cells of type text or date, or cells associated with a format string. By default, this setting is on.
    5. "formatValues" : true means return the formatted values for cells of type currency sign or decimal places.
    6. "meaninglessCells" : true means meaningless cell data is suppressed in the output.
    7. "textList" : true means if outline is enabled for textual measures then only it will return a comma-separated list of all text values associated with the cell in the output.
    8. "urlDrillThrough" : true means if Essbase drill-through links are configured then URLs are added to each applicable cell to enable users to click through to the source data.
    9. "memberIdentifierType" : "NAME" means the output should contain member names rather than aliases.
    10. "aliasTableName" : "Default" means if the memberIdentifierType: ALIAS and the aliasTable are set to a valid alias table name, the resulting mdx grid contains member names from the specified table. If the value of this property is not set then names are picked from the Default alias table.

Example of Response Body

If successful, the API returns an MDX output set in the requested format (the default is JSON).

The output set has the following form (example in JSON):

{
	"metadata": {
		"page": [<tuple>],
		"column": [<tuple>],
		"row": [<tuple>]
	},
	"data": [["",<data_row_tuples>],
         ]
}

The output includes key value pairs, where keys are metadata and data, and values are a) another MDX set and b) a data tuple.

Table - JSON Response Set

Key Value description
metadata {Metadata Set}
data [data tuple [row tuples]]

The following output set is written to output_mdx.json for the cURL example used above. 

{ "metadata" : {"page" : ["Measures","Product","Market","Scenario"],"column" : ["Year"],"row" : []},"data" : [["Qtr1","Qtr2","Qtr3","Qtr4"]] }

Example using Execute As

If you are a Service Administrator, you can use Execute As to impersonate other users and check their data access. This can be useful for testing filters assigned to various users.

To do so, add a request header X-Essbase-LoginAs with a user name who is provisioned to the application.

For example, the following script writes to a file the results of an MDX report "as executed by" (using the permissions viewpoint of) user3.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/applications/Sample/databases/Basic/mdx?format=JSON" -H "Content-Type:application/json" -H 'X-Essbase-LoginAs: user3' --data-binary "@./input_mdx.json" -o output2_mdx.json -u %User%:%Password%
Back to Top