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>>]
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.

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.

Example

The following query

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]);  

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

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

The output file, example.txt, is saved to the database 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