Export Data (Aggregate Storage)
The MaxL export data statement for ASO mode helps you export data from an Essbase aggregate storage database.
Click here for non-aggregate storage version
Use this statement to export level-0 data, which does not include calculated values, from an aggregate storage cube. Export data files are written to the cube directory, unless an alternate path is specified using FILEGOVPATH configuration. To use Report Writer, export the data using a report file.
Minimum permission required: Database Access.
Syntax
Description of the illustration export_data_as.gif
On aggregate storage databases, use export data to export in the following ways:
Keywords
- export database <dbs-name> level0 data...
-
Export level-0 input data to a text file. You cannot export aggregates, upper level data, or data from dynamically calculated members.
Note:
Exporting data does not clear the data from the database.
- export database <dbs-name> input data...
-
This statement performs the same action as export database <dbs-name> level0 data....
- export database <dbs-name> ... data anonymous
-
Export data in anonymized format. Anonymization removes the risk of sensitive data disclosure, and can be used in case sample data needs to be provided for technical support. Essbase replaces real data values with
1
, for each value in the block. - export database <dbs-name> ...using...report_file...
-
Run a stored report script, exporting a subset of the database.
Notes
-
This statement requires the database to be started.
-
Exports on aggregate storage databases are limited as follows:
-
You can export level-0 data only (level-0 data is the same as input data in aggregate storage databases).
-
You cannot perform upper-level data export on an aggregate storage database.
-
You cannot perform columnar export on an aggregate storage database.
-
To export data in parallel, specify a comma-separated list of export files, from 1 to 8 file names. This number should generally be equal to the number of processors on the machine that you wish to commit to doing parallel export. The number of threads Essbase uses typically depends on the number of file names you specify. However, on a very small aggregate storage database with a small number of data blocks, it is possible that only a single file will be created (in effect, performing serial export), even though parallel export to multiple files is requested. In this case, the export file name will be the first file name given as input.
-
During a data export, the export process allows users to connect and perform read-only operations.
-
If the data for a thread exceeds 2 GB, Essbase may divide the export data into multiple files with numbers appended to the file names.
The naming convention for additional export files is as follows:
_1
,_2
, etc. are appended to the additional file names. If the specified output file name contains a period, the numbers are appended before the period. Otherwise, they are appended at the end of the file name.For example, if the given file name is
exportfile.txt
, the next additional file isexportfile_1.txt
.
-
Example
Example 1
The following example exports all level 0 data from ASOsamp.Basic to an export file.
export database ASOsamp.Basic data to server data_file 'myfilesamp.txt';
The export location is the cube directory (
ASOSamp/Basic/myfilesamp.txt
), or whichever Essbase Server directory the administrator has specified for FILEGOVPATH.
Example 2
The following example uses a report script, Bottom.rep
, to export a subset of sorted data from ASOsamp.Basic to an output file, Bottom.rpt
.
export database ASOsamp.Basic using report_file 'Bottom.rep' to data_file 'Bottom.rpt';
Sample Report Script and Output
For example 2, assume that Bottom.rep
is the following report script file based on ASOsamp.Basic:
//Bottom.rep
<Sym
<Column (Measures, Years)
<Row (Geography, Products)
<ICHILDREN Geography
<ICHILDREN Products
<Bottom (3, @DataColumn(1))
!
The report script produces the following report (Bottom.rpt
):
Measures Years Time Transaction Type Payment Type Promotions Age Income Level Stores
North East All Merchandise 43,250,241
Products 43,250,241
High End Mercha~ 11,379,402
South All Merchandise 32,790,838
Products 32,790,838
High End Mercha~ 8,436,598
Geography All Merchandise 76,041,079
Products 76,041,079
High End Mercha~ 19,816,000