Everyday Essbase: Essbase MDX Exports

I delivered a presentation last week at the most excellent Hyperion Solutions 2020 on the topic of Essbase MDX. In it I walked the audience through the evolution of Essbase MDX exports. This is a summary of that section. Be sure to read until the very end as there is an update since the presentation.

Generation 1: The Ugly

In the first generation, if you wanted to export data during batch using MaxL the output was nearly unusable. I had to write a parser in Perl to get the output I needed.

Here's the code:

spool on to 'gen1.txt';

select {[Mar],[Apr]} on columns,
{[300],[400]} dimension properties
level_number, member_unique_name on rows,
crossjoin({[Actual],[Budget]},
{[Opening Inventory],[Ending Inventory]})
dimension properties level_number, member_unique_name on pages
from [Sample].[Basic];

spool off;

Below is my actual output file. I mean, seriously, what am I supposed to do with this?

MAXL> select {[Mar],[Apr]} on columns,

3> {[300],[400]} dimension properties

5> level_number, member_unique_name on rows,

7> crossjoin({[Actual],[Budget]},

9> {[Opening Inventory],[Ending Inventory]})

10>

11> dimension properties level_number, member_unique_name on pages

12>

13> from [Sample].[Basic];

Axis-2 Axis-2.properties Axis-1 Axis-1.properties (Mar) (Apr)

+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------

(Actual, Opening In (LEVEL_NUMBER = 0, (Cream Soda) (LEVEL_NUMBER = 1, 29095 30334

(Actual, Opening In (LEVEL_NUMBER = 0, (Fruit Soda) (LEVEL_NUMBER = 1, 26409 27588

(Actual, Ending Inv (LEVEL_NUMBER = 0, (Cream Soda) (LEVEL_NUMBER = 1, 30334 32266

(Actual, Ending Inv (LEVEL_NUMBER = 0, (Fruit Soda) (LEVEL_NUMBER = 1, 27588 29550

(Budget, Opening In (LEVEL_NUMBER = 0, (Cream Soda) (LEVEL_NUMBER = 1, 27380 28460

(Budget, Opening In (LEVEL_NUMBER = 0, (Fruit Soda) (LEVEL_NUMBER = 1, 27230 29030

(Budget, Ending Inv (LEVEL_NUMBER = 0, (Cream Soda) (LEVEL_NUMBER = 1, 28460 30190

(Budget, Ending Inv (LEVEL_NUMBER = 0, (Fruit Soda) (LEVEL_NUMBER = 1, 29030 31520

OK/INFO - 1241150 - MDX Query execution completed.

MAXL>

Generation 2: The Bad

With Essbase 11.1.2.4.010 I could specify an output delimiter which made things far better. The column_seperator command was supposed to solve all of my problems.

Here's the code:

set column_separator "|";
spool on to 'gen2.txt';

select {[Mar],[Apr]} on columns,
{[300],[400]} dimension properties
level_number, member_unique_name on rows,
crossjoin({[Actual],[Budget]},
{[Opening Inventory],[Ending Inventory]})
dimension properties level_number, member_unique_name on pages
from [Sample].[Basic];

spool off;

The output was better but not perfect. I still have to deal with the commands being written to the file.

MAXL> select {[Mar],[Apr]} on columns,

2> {[300],[400]} dimension properties

3> level_number, member_unique_name on rows,

4> crossjoin({[Actual],[Budget]},

5> {[Opening Inventory],[Ending Inventory]})

6> dimension properties level_number, member_unique_name on pages

7> from [Sample].[Basic];

Actual|Opening Inve|0|Actual|0|Opening |Cream Soda|1|300|29095|30334

Actual|Opening Inve|0|Actual|0|Opening |Fruit Soda|1|400|26409|27588

Actual|Ending Inven|0|Actual|0|Ending I|Cream Soda|1|300|30334|32266

Actual|Ending Inven|0|Actual|0|Ending I|Fruit Soda|1|400|27588|29550

Budget|Opening Inve|0|Budget|0|Opening |Cream Soda|1|300|27380|28460

Budget|Opening Inve|0|Budget|0|Opening |Fruit Soda|1|400|27230|29030

Budget|Ending Inven|0|Budget|0|Ending I|Cream Soda|1|300|28460|30190

Budget|Ending Inven|0|Budget|0|Ending I|Fruit Soda|1|400|29030|31520

OK/INFO - 1241150 - MDX Query execution completed.

MAXL> spool off;

Generation 3: The Good

The next iteration, only available in Essbase 19c or higher, is what should have been available in the first place.

Here's the new code. Notice the first line where I can now specify the export file name and the delimiter. Nirvana...

EXPORT INTO FILE "Gen3" OVERWRITE USING COLUMNDELIMITER "|"
select {[Mar],[Apr]} on columns,
{[300],[400]} dimension properties
level_number, member_unique_name on rows,
crossjoin({[Actual],[Budget]},
{[Opening Inventory],[Ending Inventory]})
dimension properties level_number, member_unique_name on pages
from [Sample].[Basic];

Here's the file output:

Actual|Opening Inventory|0|Actual|0|Opening Inventory|Cream Soda|1|300|29095|30334

Actual|Opening Inventory|0|Actual|0|Opening Inventory|Fruit Soda|1|400|26409|27588

Actual|Ending Inventory|0|Actual|0|Ending Inventory|Cream Soda|1|300|30334|32266

Actual|Ending Inventory|0|Actual|0|Ending Inventory|Fruit Soda|1|400|27588|29550

Budget|Opening Inventory|0|Budget|0|Opening Inventory|Cream Soda|1|300|27380|28460

Budget|Opening Inventory|0|Budget|0|Opening Inventory|Fruit Soda|1|400|27230|29030

Budget|Ending Inventory|0|Budget|0|Ending Inventory|Cream Soda|1|300|28460|30190

Budget|Ending Inventory|0|Budget|0|Ending Inventory|Fruit Soda|1|400|29030|31520

Generation 2 Part 2:

This is the plot twist and the part that I did not cover in my presentation. In reading through the release notes, I noticed there's a new command which became available in 11.1.2.4.018. It allows you to turn off the echo of statements in the spooled file. It does appear that you can get a clean MDX export in 11.1.2.4 (as long as you're on patch 18 or higher). The other thing I noticed is that I only got clean output in my export file if I was running the script in non-interactive mode. If I manually logged in using the MaxL command line interface and tried running this, I'd still get the unwanted statements in my output file.

Here's the code:

set message level warning;
set column_separator "|";
set echo_mode off;
spool on to 'Gen2p2.txt';

select {[Mar],[Apr]} on columns,
{[300],[400]} dimension properties
level_number, member_unique_name on rows,
crossjoin({[Actual],[Budget]},
{[Opening Inventory],[Ending Inventory]})
dimension properties level_number, member_unique_name on pages
from [Sample].[Basic];

spool off;

Here's the file output:

oduct|Product.LEVEL_NUMBER|Product.MEMBER_UNIQUE_NAME|Mar|Apr