exportQueryResults
Runs a query defined in an application and exports results into a text file.
The query result file is stored in profitoutbox; you can download it using the downloadFile
command or by using Profitability and Cost Management File Explorer.
Applies to
Profitability and Cost ManagementRequired Roles
- Service Administrator
- Power User
- User
- Viewer
Usage
epmautomate exportQueryResults APPLICATION_NAME fileName=FILE_NAME [fileOutputOptions=ZIP_ONLY|ZIP_AND_TEXT|TEXT_ONLY] [queryName=QUERY_NAME] [exportOnlyLevel0Flg=true|false] [roundingPrecision=2] [dataFormat=NATIVE|COLUMNAR] [memberFilters=JSON_FILTER] [includeHeader=true|false] [delimiter="DELIMITER"] [keepDuplicateMemberFormat=true|false]
where:
APPLICATION_NAME
is the name of the Profitability and Cost Management application for which you want to run the query.fileName
is the name of the file that will store the query results. This parameter value is required ifqueryName
parameter value is not specified. It is optional ifqueryName
parameter value is specified, in which case, the query name is used as the name of the query results file.The data format you specify determines the format of the output file. If you use
dataFormat=NATIVE
(default) the export process creates a text file. If you usedataFormat=COLUMNAR
, the export process creates multiple sequentially numbered text files and compresses them into a Zip file.fileOutputOptions
, optionally, identifies the output format of the query results file. Default isZIP_ONLY
, which createsfileName
.ZIP orqueryName
.ZIP depending on whether a value for thefileName
parameter is specified. Other options areTEXT_ONLY
to create the output file as a text file andZIP_AND_TEXT
to generate both a text file and a zip file.queryName
is an optional parameter that identifies a query that is defined in the application. Query names that contain the space character must be enclosed in double quotation marks.Do not specify a query name if you want to export all Oracle Essbase data belonging to the application.
The following conditions may cause this command to create an empty data file:
- A badly formed query that retrieves no data
- A query that generates too much data. In this scenario, consider narrowing the scope of the query so that it retrieves less data or break the query into smaller queries
See Managing Oracle Profitability and Cost Management Cloud Queries in Administering Profitability and Cost Management.
exportOnlyLevel0Flg
, optionally, specifies whether the query should retrieve only level0 data. Specify this parameter value in all lower case.This parameter is ignored if you are exporting all application data by omitting the query name.
roundingPrecision
, optionally, specifies the number of decimal places (rounding precision) to use when exporting query results. Applicable only whenqueryName
is specified. Default is 2.dataFormat
, optionally, identifies the output format. Valid values are:NATIVE
, which maintains the query result as Essbase native format data. This is the default value.COLUMNAR
, which converts Essbase native format data and orders it in columns for easy interpretation and import into other applications.This option exports all Essbase data and ignores the
queryName
parameter value. You can filter the data by setting thememberFilters
parameter value.
Note:
The command considers the following optional parameters only ifdataFormat
is specified asCOLUMNAR
.memberFilters
, optionally, accepts a JSON formatted string to filter by dimension and level0 members. Example,"{\"Dim1\":[\"Mem1\"],\"Dim2\":[\"Mem21\",\"Mem22\"]}"
includeHeader
, optionally, adds dimension names as column headers. Set this value tofalse
to exclude column header. Default istrue
.delimiter
, optionally, identifies the delimiter that is to be used to separate dimension members in the query result file. Delimiter must be enclosed in double quotation marks. Default is space (" ").keepDuplicateMemberFormat
, optionally, specifies whether to print the member format in Essbase duplicate member format, for example,[Account]@[Accoun1]
. Set this value tofalse
to print only member name. Default istrue
.
Examples
- Export all application data:
epmautomate exportQueryResults BksML12 fileName="BksML12_MyQuery1.txt" fileOutputOptions=TEXT_ONLY
- Export results of a specific query:
epmautomate exportQueryResults BksML12 queryName="My Product Query" roundingPrecision=3
- Export Level0 data in NATIVE data format:
epmautomate exportQueryResults BksML30 fileName="BksML30_ExportLevel0-Data" fileOutputOptions=ZIP_AND_TEXT exportOnlyLevel0Flg=true
- Export Level0 data in COLUMNAR data format with a single dimension and single member filter:
epmautomate exportQueryResults BksML30 fileName="BksML30_Level0-Data" dataFormat="COLUMNAR" memberFilters="{\"Period\":[\"December\"]}" includeHeader="true" delimiter="," roundingPrecision="3"
- Export Level0 data in COLUMNAR data format with a single dimension and multiple member filters:
epmautomate exportQueryResults BksML30 fileName="BksML30_Level0-Data" dataFormat="COLUMNAR" memberFilters="{\"Period\":[\"November\",\"December\"]}" includeHeader="true" delimiter="," roundingPrecision="3"
- Export Level0 data in COLUMNAR data format with a multiple dimensions and multiple member filters:
epmautomate exportQueryResults BksML30 fileName="BksML30_Level0-Data" dataFormat="COLUMNAR" memberFilters="{\"Year\":[\"2016\"],\"Period\":[\"November\",\"December\"]}" includeHeader="true" delimiter="," roundingPrecision="3"