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 [DATALESS] INTO FILE <file_name> [OVERWRITE] [USING COLUMNDELIMITER <delimiter_character>] [SKIPZERO] [DECIMAL <decimal_number>] [DATAEXPFORMAT]
SELECT [<axis_specification>
       [, <axis_specification>...]]
  <subselect> | FROM <cube_specification> 
[WHERE [<slicer_specification>]]

Table 4-14 MDX EXPORT Clause Elements

Item Description
DATALESS

Optional keyword to indicate a dataless (metadata-only) MDX export. Refer to Notes (and Example 4) for guidelines.
INTO FILE 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.
SKIPZERO

Optional argument to optimize large data queries by suppressing rows containing only zero values. Refer to Example 5.
DECIMAL decimal_number Optional argument specifying the number of decimal places to use in exported numeric data. The default is 16. This argument is applicable for both aggregate storage (ASO) and block storage (BSO) cubes. Refer to Example 3.
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 type.

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

To optimize performance for large data exports, you can specify the row axis as NON EMPTY. Starting with Release 21.8, Essbase no longer implicitly applies NON EMPTY over the row axis by default.

For optimized performance when using MDX Export to export data subsets, with no query limit of 232-cells, use the following guidelines:

  • Specify NON EMPTY on the row axis. For information about NON EMPTY, refer to MDX Axis Specifications.
  • 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.

To support dataless (metadata only) exports, use the following guidelines:

  1. In MaxL Shell, specify alter session set dml_output metadata_only on;
  2. Include the DATALESS directive in the MDX Export query syntax.
  3. Refer to Example 4.

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 type: 

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. The following 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
. . .

Example 3 – DECIMAL

The following query exports stored (level 0) aggregate storage cube data with 5 decimal places for the values:

EXPORT INTO FILE "MDXExport_DataExport2.txt" USING DECIMAL 5 
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_DataExport2.txt, is saved to the cube directory. For the data values, the number of places after the decimal is limited to 5. The following example is truncated for length: 

Years,Time,Transaction Type,Payment Type,Promotions,Age,Income Level,Products,Stores,Geography,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.50000,656.50000,1.00000,2.00000,#Mi
Curr Year,Jan,Sale,Cash,No Promotion,1 to 13 Years,Under 20,000,Camcorders,017589,13681,1443.00000,1443.00000,2.00000,1.00000,#Mi
Curr Year,Jan,Sale,Cash,No Promotion,1 to 13 Years,Under 20,000,Photo Printers,017589,14010,232.00000,232.00000,1.00000,2.00000,#Mi
Curr Year,Jan,Sale,Cash,No Promotion,1 to 13 Years,Under 20,000,Photo Printers,017589,14027,238.00000,238.00000,1.00000,1.00000,#Mi
. . .

Example 4 – DATALESS with NON EMPTY

The following query exports a large amount of metadata from the sample cube named ASOSamp.Basic (provided with Essbase). The export is optimized, because it is based on stored members, has two axes, and uses the NON EMPTY clause over the row axis.

EXPORT DATALESS INTO FILE "DATALESS.txt" OVERWRITE USING columndelimiter ","
SELECT {[Original Price]} 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, DATALESS.txt, is saved to the cube directory.

Example 5 – DATA query with SKIPZERO Directive

The following query exports a large amount of data from the sample cube named ASOSamp.Basic (provided with Essbase). The export is optimized because it uses the SKIPZERO directive to treat zero values as missing, and skips exporting them to the output.

EXPORT INTO FILE "DATA_SKIPZERO.txt" OVERWRITE USING columndelimiter "," SKIPZERO
SELECT {[Original Price]}
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, DATA_SKIPZERO.txt, is saved to the cube directory.