Tuesday, August 18, 2015

Exporting Data using MDX

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.

4 comments:

Unknown said...

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

srx said...

Really nice paper and script but not sure that people will prefer MDX export!

TimF said...

@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.

Unknown said...

cool post, thanks