REST API for Oracle Essbase

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

application(required): string

Application name.
database(required): string

Database name.

Query Parameters

format: string

Result format.

Default Value: JSON

Allowed Values: [ "XLSX", "CSV", "HTML", "JSON" ]

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

preferences: object NamedQueriesPreferences
query: string

{
    "type":"object",
    "properties":{
        "query":{
            "type":"string"
        },
        "preferences":{
            "$ref":"#/definitions/NamedQueriesPreferences"
        }
    }
}

Nested Schema : NamedQueriesPreferences

Type: object

Show Source

cellAttributes: boolean
dataless: boolean

Set to true to omit data values from the output set. Default is false.
formatString: boolean

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.
formatValues: boolean
hideRestrictedData: boolean
meaninglessCells: boolean
memberIdentifierType: string

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).
textList: boolean
urlDrillThrough: boolean

{
    "type":"object",
    "properties":{
        "dataless":{
            "type":"boolean",
            "description":"<p>Set to <b>true</b> to omit data values from the output set. Default is <b>false</b>.</p>"
        },
        "hideRestrictedData":{
            "type":"boolean"
        },
        "cellAttributes":{
            "type":"boolean"
        },
        "formatString":{
            "type":"boolean",
            "description":"<p>Set to <b>true</b> to return the formatted values for cells of type text or date, or cells associated with a format string. Default is <b>true</b>.</p>"
        },
        "formatValues":{
            "type":"boolean"
        },
        "meaninglessCells":{
            "type":"boolean"
        },
        "textList":{
            "type":"boolean"
        },
        "urlDrillThrough":{
            "type":"boolean"
        },
        "memberIdentifierType":{
            "type":"string",
            "description":"<p>Specify whether metadata in the output should refer to member names, member aliases, or unique member names (in case of duplicate member enabled outlines).</p>",
            "enum":[
                "NAME",
                "ALIAS",
                "UNIQUE_NAME"
            ]
        }
    }
}

Response

Supported Media Types

application/octet-stream
text/html

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.

Root Schema : InputStream

Type: object

400 Response

Bad Request

Failed to get the data in the required format.

500 Response

Internal Server Error.

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,
        "formatValues" : true,
        "memberIdentifierType": "NAME"
        }
}

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. "formatValues" : 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.
3. "memberIdentifierType" : "NAME" means the output should contain member names rather than aliases.

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%