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>
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;
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.
4 comments:
Great work, i tried the same using python but failed with member names that have commas in it. Does your script handle that case?
Greets
Kevin Kraft
Really nice paper and script but not sure that people will prefer MDX export!
@Kevin Kraft -- As a best practice I avoid using commas in member names (maybe try to put them in aliases if they're really necessary?). I understand it's not always possible to live by that rule. If you change the following line from:
@columns = split /\,/, $1;
to
@columns = split /\, /, $1;
then it will handle a comma in a member name so long as it isn't followed by a space. I'm afraid there's no way to distinguish between a member with a comma and a space and what the MDX output gives you. I looked to see if there was a way to put quotes around member names (I think you can do that in Report Scripts) but couldn't find any.
cool post, thanks
Post a Comment