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:
1
2
3
4
5
6
7
8
9
10
11 | 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,
2>
3> {[300],[400]} dimension properties
4>
5> level_number, member_unique_name on rows,
6>
7> crossjoin({[Actual],[Budget]},
8>
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:
1
2
3
4
5
6
7
8
9
10
11
12 | 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];
Scenario|Measures|Scenario.LEVEL_NUMBE|Product|Product.LEVEL_NUMBER|Mar|Apr
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...
1
2
3
4
5
6
7
8 | 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:
Scenario|Measures|Scenario.LEVEL_NUMBER|Scenario.MEMBER_UNIQUE_NAME|Measures.LEVEL_NUMBER|Measures.MEMBER_UNIQUE_NAME|Product|Product.LEVEL_NUMBER|Product.MEMBER_UNIQUE_NAME|Mar|Apr
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | 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:
Scenario|Measures|Scenario.LEVEL_NUMBER|Scenario.MEMBER_UNIQUE_NAME|Measures.LEVEL_NUMBER|Measures.MEMBER_UNIQUE_NAME|Pr
oduct|Product.LEVEL_NUMBER|Product.MEMBER_UNIQUE_NAME|Mar|Apr
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
