1. Calculation and Query Reference for Oracle Essbase
  2. MDX
  3. MDX Syntax and Grammar Rules
  4. MDX Export Specification

MDX Export Specification

The MDX export clause is a way to save query results to a file on Essbase. This is an alternative to viewing the query output on a client, and can be useful for large queries, or for exporting data to import later using a data load.

Syntax

[<with_section>]
EXPORT INTO FILE <file_name> [OVERWRITE] [USING COLUMNDELIMITER <delimiter_character>] [DATAEXPFORMAT]
SELECT [<axis_specification>
       [, <axis_specification>...]]
  <subselect> | FROM <cube_specification> 
[WHERE [<slicer_specification>]]

Table 4-14 MDX EXPORT Clause Elements

Item Description
file_name

The name of a text file in which to save the exported MDX query results. A file extension is not required.
OVERWRITE

Optional keyword specifying that if file_name already exists, overwrite it.

USING COLUMNDELIMITER delimiter_character

Optional argument specifying a character or word to use as a column separator. If omitted, the default MaxL column output is used, and the default column width is 20 characters.
DATAEXPFORMAT

Optional keyword, applicable for aggregate storage (ASO) cubes only. Exports the data (for stored members only) in native data export format. Native format means the same format that is produced by the DATAEXPORT calculation command or the Export Data job in the Essbase web interface.

Limitations of DATAEXPFORMAT directive:

  • You can export only level-0 (stored) data. See the examples for how to employ the Filter function to achieve this.
  • Using the directive with COLUMNDELIMITER keyword has no effect.
  • Calculated members are not supported for export.
  • Formula members are not supported for export.

See Example 2.

Notes

MDX Export is designed for large data exports. For optimal performance, Essbase treats the row axis as NON EMPTY, in a two-axis MDX Export query. This is the default behavior even if NON EMPTY is not specified. For more information about NON EMPTY, see MDX Axis Specifications.

When you use MDX Export to export data subsets, your export has optimal performance and no query limit of 232-cells, as long as you adhere to the following guidelines:

  • Export only level-0 (stored) data. See the examples for how to employ the Filter function to achieve this.
  • Use two axes in the SELECT section of the query.

MDX Export can export data from cubes that have duplicate member outlines.

Example 1 – Basic Usage

Assume the following query is entered into MaxL Shell: 

EXPORT INTO FILE "example" OVERWRITE USING COLUMNDELIMITER "#~"
SELECT  
   {[Mar],[Apr]} ON  COLUMNS,
   Crossjoin({[100],[200]} , crossjoin({[Actual],[Budget]},
   {[Opening Inventory],[Ending Inventory]})) ON ROWS
FROM [Sample].[Basic]
WHERE ([New York]);

The query returns only minimal information to the MaxL Shell (where status 1 indicates successful query execution): 

 Axis-1              (File)             
+-------------------+-------------------
 (Mdx Export)                          1

The output file, example.txt, is saved to the cube directory, and contains the actual query output: 

Product#~Scenario#~Measures#~Mar#~Apr
Colas#~Actual#~Opening Inventory#~2041#~2108
Colas#~Actual#~Ending Inventory#~2108#~2250
Colas#~Budget#~Opening Inventory#~1980#~2040
Colas#~Budget#~Ending Inventory#~2040#~2170
Root Beer#~Actual#~Opening Inventory#~2378#~2644
Root Beer#~Actual#~Ending Inventory#~2644#~2944
Root Beer#~Budget#~Opening Inventory#~2220#~2450
Root Beer#~Budget#~Ending Inventory#~2450#~2710

You can also use the Analyze view in the Essbase web interface to run MDX Exports, if you do not want to use MaxL Shell. For details, refer to Run MDX Scripts.

Example 2 – DATAEXPFORMAT for ASO

The following query exports stored (level 0) aggregate storage cube data in the same format that is produced by the DATAEXPORT calculation command or the Export Data job in the Essbase web interface. 

EXPORT INTO FILE "MDXExport_DataExport1.txt" USING DATAEXPFORMAT
SELECT Filter([Measures].members, [Measures].currentmember.STORED_FLAG)
ON COLUMNS, 
NON EMPTY(
 Crossjoin(
  Crossjoin(
   Crossjoin(
    Crossjoin(
     Crossjoin(
      Crossjoin(
       Crossjoin(
        Crossjoin(
         Crossjoin(
          Filter([Years].members, [Years].currentmember.STORED_FLAG),
         Filter([Time].members, [Time].currentmember.STORED_FLAG)),
        Filter([Transaction Type].members, [Transaction Type].currentmember.STORED_FLAG)),
       Filter([Payment Type].members, [Payment Type].currentmember.STORED_FLAG)),
      Filter([Promotions].members, [Promotions].currentmember.STORED_FLAG)),
     Filter([Age].members, [Age].currentmember.STORED_FLAG)),
    Filter([Income Level].members, [Income Level].currentmember.STORED_FLAG)),
   Filter([Products].members, [Products].currentmember.STORED_FLAG)),
  Filter([Stores].members, [Stores].currentmember.STORED_FLAG)),
 Filter([Geography].members, [Geography].currentmember.STORED_FLAG))
)
ON ROWS FROM ASOSamp.Basic;

The output file, MDXExport_DataExport1.txt, is saved to the cube directory. This example is truncated for length. 

"Original Price" "Price Paid" "Units" "Transactions" "Returns"
"Curr Year" "Jan" "Sale" "Cash" "No Promotion" "1 to 13 Years" "Under 20,000" "Camcorders" "017589" "14036" 656.5 656.5 1 2
"13681" 1443 1443 2 1
"Photo Printers" "14010" 232 232 1 2
"14027" 238 238 1 1
"13428" 214 214 1 2
"13681" 206 206 1 2 206
"Feb" "Digital Cameras" "13421" 436 436 1 2
"Camcorders" 682.5 682.5 1 1
"Photo Printers" "13835" 238 238 1 1
"Jan" "Memory" "13904" 220 220 2 2 220
"13664" 238 238 2 1
"13668" 202 202 2 1 202
"13421" 236 236 2 1
"Other Accessories" "14001" 357 357 6 5
"14027" 202 202 4 2
"13628" 380.5 380.5 7 3
"13636" 157.5 157.5 3 2
. . .