Run MDX Query
/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
-
application: string
Application name.
-
database: string
Database name.
-
format(optional): string
Result format.
Default Value:JSONAllowed 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).
object-
cellAttributes(optional):
boolean
-
dataless(optional):
boolean
Set to true to omit data values from the output set. Default is false.
-
formatString(optional):
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(optional):
boolean
-
hideRestrictedData(optional):
boolean
-
meaninglessCells(optional):
boolean
-
memberIdentifierType(optional):
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(optional):
boolean
-
urlDrillThrough(optional):
boolean
Response
- 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.
object400 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 {Product.Members} on rows, {Year.Members} on columns",
"preferences" : {
"dataless" : false,
"formatValues" : true,
"memberIdentifierType": "NAME"
}
}Where:
-
querycontains the input MDX string. -
preferencescontains options you can specify for the output set. For example,"dataless" : falsemeans do not omit the data values from the output."formatValues" : truemeans return the formatted values for cells of type text or date, or cells associated with a format string. By default, this setting is on."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. In this documentation, the output has been formatted with whitespace for legibility.
{
"metadata": {
"page": ["Measures",
"Market",
"Scenario"],
"column": ["Year"],
"row": ["Product"]
},
"data": [["",
"Year",
"Qtr1",
"Jan",
"Feb",
"Mar",
"Qtr2",
"Apr",
"May",
"Jun",
"Qtr3",
"Jul",
"Aug",
"Sep",
"Qtr4",
"Oct",
"Nov",
"Dec"],
["Product",
"105522.0",
"24703.0",
"8024.0",
"8346.0",
"8333.0",
"27107.0",
"8644.0",
"8929.0",
"9534.0",
"27912.0",
"9878.0",
"9545.0",
"8489.0",
"25800.0",
"8653.0",
"8367.0",
"8780.0"],
["100",
"30468.0",
"7048.0",
"2355.0",
"2329.0",
"2364.0",
"7872.0",
"2442.0",
"2571.0",
"2859.0",
"8511.0",
"2988.0",
"2945.0",
"2578.0",
"7037.0",
"2317.0",
"2247.0",
"2473.0"],
["100-10",
"22777.0",
"5096.0",
"1710.0",
"1666.0",
"1720.0",
"5892.0",
"1793.0",
"1908.0",
"2191.0",
"6583.0",
"2299.0",
"2313.0",
"1971.0",
"5206.0",
"1706.0",
"1653.0",
"1847.0"],
["100-20",
"5708.0",
"1359.0",
"427.0",
"463.0",
"469.0",
"1534.0",
"497.0",
"509.0",
"528.0",
"1528.0",
"590.0",
"518.0",
"420.0",
"1287.0",
"432.0",
"402.0",
"453.0"],
["100-30",
"1983.0",
"593.0",
"218.0",
"200.0",
"175.0",
"446.0",
"152.0",
"154.0",
"140.0",
"400.0",
"99.0",
"114.0",
"187.0",
"544.0",
"179.0",
"192.0",
"173.0"],
["200",
"27954.0",
"6721.0",
"2141.0",
"2323.0",
"2257.0",
"7030.0",
"2283.0",
"2302.0",
"2445.0",
"7005.0",
"2365.0",
"2370.0",
"2270.0",
"7198.0",
"2505.0",
"2391.0",
"2302.0"],
["200-10",
"7201.0",
"1697.0",
"554.0",
"582.0",
"561.0",
"1734.0",
"551.0",
"583.0",
"600.0",
"1883.0",
"588.0",
"671.0",
"624.0",
"1887.0",
"667.0",
"626.0",
"594.0"],
["200-20",
"12025.0",
"2963.0",
"955.0",
"997.0",
"1011.0",
"3079.0",
"1006.0",
"1030.0",
"1043.0",
"3149.0",
"1078.0",
"1077.0",
"994.0",
"2834.0",
"972.0",
"920.0",
"942.0"],
["200-30",
"4636.0",
"1153.0",
"365.0",
"387.0",
"401.0",
"1231.0",
"406.0",
"421.0",
"404.0",
"1159.0",
"436.0",
"370.0",
"353.0",
"1093.0",
"351.0",
"359.0",
"383.0"],
["200-40",
"4092.0",
"908.0",
"267.0",
"357.0",
"284.0",
"986.0",
"320.0",
"268.0",
"398.0",
"814.0",
"263.0",
"252.0",
"299.0",
"1384.0",
"515.0",
"486.0",
"383.0"],
["300",
"25799.0",
"5929.0",
"1917.0",
"1997.0",
"2015.0",
"6769.0",
"2203.0",
"2242.0",
"2324.0",
"6698.0",
"2573.0",
"2255.0",
"1870.0",
"6403.0",
"2041.0",
"2077.0",
"2285.0"],
["300-10",
"12195.0",
"2544.0",
"800.0",
"864.0",
"880.0",
"3231.0",
"1029.0",
"1082.0",
"1120.0",
"3355.0",
"1337.0",
"1155.0",
"863.0",
"3065.0",
"898.0",
"979.0",
"1188.0"],
["300-20",
"2511.0",
"690.0",
"220.0",
"231.0",
"239.0",
"815.0",
"281.0",
"265.0",
"269.0",
"488.0",
"294.0",
"100.0",
"94.0",
"518.0",
"168.0",
"168.0",
"182.0"],
["300-30",
"11093.0",
"2695.0",
"897.0",
"902.0",
"896.0",
"2723.0",
"893.0",
"895.0",
"935.0",
"2855.0",
"942.0",
"1000.0",
"913.0",
"2820.0",
"975.0",
"930.0",
"915.0"],
["400",
"21301.0",
"5005.0",
"1611.0",
"1697.0",
"1697.0",
"5436.0",
"1716.0",
"1814.0",
"1906.0",
"5698.0",
"1952.0",
"1975.0",
"1771.0",
"5162.0",
"1790.0",
"1652.0",
"1720.0"],
["400-10",
"11844.0",
"2838.0",
"935.0",
"949.0",
"954.0",
"2998.0",
"944.0",
"995.0",
"1059.0",
"3201.0",
"1072.0",
"1123.0",
"1006.0",
"2807.0",
"993.0",
"887.0",
"927.0"],
["400-20",
"9851.0",
"2283.0",
"744.0",
"768.0",
"771.0",
"2522.0",
"799.0",
"844.0",
"879.0",
"2642.0",
"892.0",
"888.0",
"862.0",
"2404.0",
"801.0",
"756.0",
"847.0"],
["400-30",
"-394.0",
"-116.0",
"-68.0",
"-20.0",
"-28.0",
"-84.0",
"-27.0",
"-25.0",
"-32.0",
"-145.0",
"-12.0",
"-36.0",
"-97.0",
"-49.0",
"-4.0",
"9.0",
"-54.0"],
["Diet",
"28826.0",
"7017.0",
"2279.0",
"2362.0",
"2376.0",
"7336.0",
"2396.0",
"2434.0",
"2506.0",
"7532.0",
"2610.0",
"2595.0",
"2327.0",
"6941.0",
"2379.0",
"2252.0",
"2310.0"],
["100-20",
"5708.0",
"1359.0",
"427.0",
"463.0",
"469.0",
"1534.0",
"497.0",
"509.0",
"528.0",
"1528.0",
"590.0",
"518.0",
"420.0",
"1287.0",
"432.0",
"402.0",
"453.0"],
["200-20",
"12025.0",
"2963.0",
"955.0",
"997.0",
"1011.0",
"3079.0",
"1006.0",
"1030.0",
"1043.0",
"3149.0",
"1078.0",
"1077.0",
"994.0",
"2834.0",
"972.0",
"920.0",
"942.0"],
["300-30",
"11093.0",
"2695.0",
"897.0",
"902.0",
"896.0",
"2723.0",
"893.0",
"895.0",
"935.0",
"2855.0",
"942.0",
"1000.0",
"913.0",
"2820.0",
"975.0",
"930.0",
"915.0"]]
}