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 Management

Required 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 if queryName parameter value is not specified. It is optional if queryName 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 use dataFormat=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 is ZIP_ONLY, which creates fileName.ZIP or queryName.ZIP depending on whether a value for the fileName parameter is specified. Other options are TEXT_ONLY to create the output file as a text file and ZIP_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 when queryName 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 the memberFilters parameter value.

  Note:

  The command considers the following optional parameters only if dataFormat is specified as COLUMNAR.
• 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 to false to exclude column header. Default is true.
• 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 to false to print only member name. Default is true.

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"