Export Query Results
Exports query results for a given query or exports all Oracle Essbase data into a file in the Outbox.
When exporting all Essbase data, there is an option for writing the output in columnar format, and columnar-formatted data can be filtered by level-0 dimension members. This API triggers a job that can be monitored in the Job Library.
Required Roles
Service Administrator, Power User, User, Viewer
REST Resource
POST
/epm/rest/{api_version}/applications/{application}/jobs/exportQueryResultsJob
Note:
Before using the REST resources, you must understand how to access the REST resources and other important concepts. See Implementation Best Practices for EPM Cloud REST APIs. Using this REST API requires prerequisites. See Prerequisites.
Request
Supported Media Types: application/json
The following table summarizes the client request.
Table 24-17 Parameters
Name | Description | Type | Required | Default |
---|---|---|---|---|
api_version |
Version of the API you are developing with, such as v1 |
Path | Yes | None |
application |
Name of the application | Path | Yes | None |
exportOnlyLevel0Flg |
Whether to export only Level0 data; values are true or false |
Payload | No | false |
fileName |
Name of the query output file to be exported into the Outbox folder | Payload | Yes | None |
fileOutputOptions |
File output options. Available options are:
|
Payload | No | ZIP_ONLY |
queryName |
Query name from the Profitability and Cost Management application
When When |
Payload | No | None |
roundingPrecision |
The rounding precision (decimal places) for exported data. (Note: Applies only if queryName is also used.)
|
Payload | No | 2 |
dataFormat |
Select the output format as native Essbase format, or as columnar format. Values are NATIVE or COLUMNAR . With the COLUMNAR option, all Essbase data is exported, so the queryName parameter is ignored. Data can be filtered using the memberFilters parameter.
|
Payload | No | NATIVE |
The following parameters are only considered when |
||||
memberFilters |
Accepts a JSON formatted string for dimension and respective level-0 member format. For example:
|
Payload | No | None |
includeHeader |
Adds dimension names as column headers. Values are true or false .
|
Payload | No | true |
delimiter |
Character used to separate dimension members in the results file; must be enclosed in double quotes. | Payload | No | Space |
keepDuplicateMemberFormat |
When this parameter is set to |
Payload | No | true |
Example URL and Payload
https://<BASE-URL>/epm/rest/v1/applications/Ex3F3/jobs/exportQueryResultsJob
{"queryName":"Profitability - Product","fileName":"ProfitabilityProduct_03232016.txt","exportOnlyLevel0Flg":"true","roundingPrecision":"3"}
Response
Supported Media Types: application/json
Table 24-18 Parameters
Name | Description |
---|---|
details |
Task ID, such as BksML12_BksML12_ExportQueryResults_D20160323T024820_f73_1 |
status |
See Migration Status Codes |
statusMessage |
Message about the status, such as In Progress |
type |
Profitability |
data |
Parameters as key value pairs |
links |
Detailed information about the link |
href |
Links to API call |
action |
The HTTP call type |
rel |
Relationship type |
data |
Parameters as key value pairs passed in the request |
Example of Response Body
The following shows an example of the response body in JSON format.
{
"type":"Profitability",
"status":-1,
"statusMessage":"In Progress",
"details":"BksML30_ExportQueryResults_D20220511T114843_935",
"links":[
{
"href":"http://<BASE-URL>/epm/rest/v1/applications/jobs/ChecktaskStatusJob/BksML30_ExportQueryResults_D20220511T114843_935",
"action":"GET",
"rel":"Job Status"
}
]
}
Java Sample – ExportQueryResult.java for Profitability and Cost Management
Prerequisites: json.jar
Common Functions: See Profitability and Cost Management Common Helper Functions for Java
public void exportTemplate() throws Exception {
String fileName = applicationName + "_Template_Export_File";
JSONObject json = new JSONObject();
json.put("fileName", fileName);
String urlString = "%s/epm/rest/%s/applications/%s/jobs/templateExportJob";
executeJob(urlString, "POST", json.toString());
}
cURL Sample – ExportQueryResult.sh for Profitability and Cost Management
Common Functions: See Profitability and Cost Management Common Helper Functions for cURL.
funcExportQueryResult() {
queryName="Profitability - Product";
fileName=$APP_NAME+"_"+$queryName+"_Query_Result"
param="{\"queryName\":\"$queryName\",\"fileName\":\"$fileName\",\"exportOnlyLevel0Flg\":\"false\"}"
url=$SERVER_URL/epm/rest/$API_VERSION/applications/$APP_NAME/jobs/exportQueryResultsJob
funcExecuteRequest "POST" $url "$param" "application/json"
output=`cat response.txt`
status=`echo $output | jq '.status'`
if [ $status == -1 ]; then
echo "Started Exporting successfully"
funcGetStatus "GET"
else
error=`echo $output | jq '.details'`
echo "Error occurred. " $error
fi
funcRemoveTempFiles "respHeader.txt" "response.txt"
}
Groovy Sample – ExportQueryResult.groovy for Profitability and Cost Management
Prerequisites: json.jar
Common Functions: See Appendix C: Common Helper Functions for Groovy.
def exportQueryResult() {
String queryName = "Profitability - Product";
String fileName = appName +"_"+ queryName + "_Query_Result";
JSONObject json = new JSONObject();
json.put("queryName", queryName);
json.put("fileName", fileName);
json.put("exportOnlyLevel0Flg", false);
String urlString = serverUrl + "/epm/rest/"+ apiVersion + "/applications/" + appName + "/jobs/exportQueryResultsJob";
def url;
try {
url = new URL(urlString)
} catch (MalformedURLException e) {
println "Malformed URL. Please pass valid URL"
System.exit(0);
}
executeJob(url, "POST", json.toString());
}