Essbase has had the ability to export data using MDX for over 10 years. Some people still don't like it. They have good reason. You still can't get a clean MDX extract from Essbase after all of these years. Go ahead and export Sample.Basic level 0 data and what do you get?
MDX Script (this will export all level 0 data suppressing missing records, shared members and formula members):
set column_width 80;
alter session set dml_output alias off;
spool on to "out.txt";
SELECT {[Jan]:[Dec]} on columns,
NON EMPTY
Crossjoin(Filter(Descendants([Measures], Measures.Levels(0)), [Measures].CurrentMember.Member_Type <> 2 AND NOT Measures.CurrentMember.Shared_Flag),
Crossjoin(Filter(Descendants([Product], Product.Levels(0)), [Product].CurrentMember.Member_Type <> 2 AND NOT Product.CurrentMember.Shared_Flag),
Crossjoin(Filter(Descendants([Market], Market.Levels(0)), [Market].CurrentMember.Member_Type <> 2 AND NOT Market.CurrentMember.Shared_Flag),
Filter(Descendants([Scenario], Scenario.Levels(0)), [Scenario].CurrentMember.Member_Type <> 2 AND NOT Scenario.CurrentMember.Shared_Flag))))
on rows
from [Sample.Basic];
spool off;
exit;
So what's the output to my out.txt file look like?
MAXL> SELECT {[Jan]:[Dec]} on columns,
2>
3> NON EMPTY
4>
5> Crossjoin(Filter(Descendants([Measures], Measures.Levels(0)), [Measures].CurrentMember.Member_Type <> 2 AND NOT Measures.CurrentMember.Shared_Flag),
6>
and so on...
Hey, that looks familiar. I'll just add the setting that removes the MaxL statement from the output file. Oh, wait, there's no such thing. Nice...
And then the output is lovely. Aghh!!!
Axis-1 (Jan) (Feb) (Mar) (Apr) (May) (Jun) (Jul) (Aug) (Sep) (Oct) (Nov) (Dec)
+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------
(Sales, 100-10, New York, Actual) 678 645 675 712 756 890 912 910 790 650 623 699
The
Swiss Army Chainsaw, Perl, to the rescue! If you're on Unix or Linux then you're in luck because they all come with Perl and this script is so simple that it should run with no modifications. If you're on Windows, you'd have to install Perl. There are two output options -- regular and classic. Classic makes the output look like an old fashioned Essbase extract. Supply the input file name and the output is sent to STDOUT. Add the word "classic" after the input filename and the file will look just like an Essbase extract.
formatMaxl.pl:
#!/usr/bin/perl
if (scalar (@ARGV) < 1){
die "usage: formatMaxl.pl input [classic]";
}
# if there is a second argument, the file will be output in the classic Essbase export format: no commas, quotes around member names
if (exists($ARGV[1]) && $ARGV[1] eq "classic"){
$classic = 1;
}else{
$classic = 0;
}
my $tabfile = shift @ARGV;
open INFILE, $tabfile;
my $counter = 0; # initialize the counter to zero
my @prevColumns; # initialize the prevColumns array
my @printColumns; # initialize the printColumns array
my @columns; # initialize the columns array
# read through each line of the file
while (<INFILE>){
###################
# handle header row
###################
if ($_ =~ /Axis-1/){
while(/\((.+?)\)/g ) { # read all of the column member names in parenthesis
if($counter == 0){ # check if it's the first member name
if ($classic == 0){ # if it is a regular conversion
print $1; # just print the member name
}else{ # otherwise
print "\"$1\""; # put quotes around the member
}
$counter++; # increment the counter so we know we need commas in front of the rest of the members
}else{
if ($classic == 0){
print ",$1"; # same as above except need the member preceeded by a comma
}else{
print " \"$1\"";
}
}
}
print "\n"; # new line after the header
# now handle the data rows
} elsif($_ =~ /^ \(/) { # when we come to the beginning of a line followed by a parenths it's time to get to work
# the members should be in a format like so: (Sales, 100-10, New York, PY Actual)
# split the items in parentheses by commas and the rest by space
$_ =~ s/#Missing/#Mi/g; # change #Missing to #Mi
$_ =~ /\(([^\)]+)\)(\s+)([^\)]+)/; # this regexp puts everything within parethsesis into $1, the data all goes into $3
@columns = split /\,/, $1; # split the members in parentheses ($1) by commas
if ($classic == 0){
push ( @columns, split /\s+/, $3); # split the rest of it, the data, ($3) by space and then push it onto the end of the columns array
print join(", ", @columns), "\n"; # now print each row with a comma seperator
}else{
undef @data; # start with a clean array
push ( @data, split /\s+/, $3); # split the data by space
s{^\s+|\s+$}{}g foreach @columns; # this takes the spaces off the beginning and ends of the member names
#########################################
# handle repeated members in this section
#########################################
undef @printColumns; # start with a clean array
$memberCount = 0; # reset the member count
foreach $member (@columns){
if($columns[$memberCount] ne $prevColumns[$memberCount]){ # if the members in the arrays don't match up, the put the member into the printColumns array
push @printColumns, $member; # so it will get printed
}
$memberCount++;
}
@prevColumns = @columns; # take a copy of the member names to use on the next row
###############################
# end handling repeated members
###############################
$members = join ' ', map { qq/"$_"/ } @printColumns; # this command puts quotes around each member name and puts a space between them then puts them into the $members scalar
print $members, " "; # print the list of member names followed by a space
print join(" ", @data), "\n"; # print the data array with a space separator followed by a new line
}
}
}
close INFILE;
An example of regular output:
Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
Sales, 100-10, New York, Actual, 678, 645, 675, 712, 756, 890, 912, 910, 790, 650, 623, 699
Sales, 100-10, New York, Budget, 640, 610, 640, 670, 710, 840, 860, 860, 750, 540, 560, 620
Sales, 100-10, Massachusetts, Actual, 494, 470, 492, 519, 551, 649, 665, 664, 576, 474, 454, 510
Sales, 100-10, Massachusetts, Budget, 460, 440, 460, 490, 520, 610, 630, 630, 540, 400, 400, 450
An example of classic output:
"Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec"
"Sales" "100-10" "New York" "Actual" 678 645 675 712 756 890 912 910 790 650 623 699
"Budget" 640 610 640 670 710 840 860 860 750 540 560 620
"Massachusetts" "Actual" 494 470 492 519 551 649 665 664 576 474 454 510
"Budget" 460 440 460 490 520 610 630 630 540 400 400 450
This script gives you the flexibility to output using MDX with a clean format that you can use to load into another cube or send off to another system. Using MDX also allows you to change the output order of the dimensions which is not easily done using the standard Essbase export function.
