MDX Export

How to export dynamically calculated values to a file

In my previous post I talked about MDX Insert which allows you to store dynamically calculated values in the cube, but what if you want to export it into a file? There are a lot of different reasons to do so, but the most common one would be moving this data into a different application. Back in 2016 Gary Crisci explained how it can be done using MAXL and MDX.

The solution proposed in the post did the job, but I wasn't too happy about it for two reasons.
First would be the query governor - by default Essbase kills queries that return too much data. It is done to avoid runaway queries that exhaust all server memory. Second - the file is created on the client machine which means we need to send the data over and do all the formatting work on the client. In addition if the file needs to be shared with multiple consumers, user would have to upload it to some shared location. So we implemented MDX Export.

I'll use the same Zigzag application from previous posts to illustrate how it works. Remember the "Closing Inventory" and "Opening Inventory"?

“Closing Inventory” = "Opening Inventory" - "Shipped" + "Received";

“Opening Inventory” = 

IF(("Inventory on hand" != #Missing) OR (@ISMBR(Jan)))

                        "Inventory on hand";

ELSE

                        @PRIOR("Closing Inventory");

ENDIF;

To query these two members we can use following MDX query:

SELECT
  {[Opening Inventory], Shipped, Received, [Closing Inventory]} ON COLUMNS,
  NON EMPTY CrossJoin(Time.Levels(0).members,
            CrossJoin(Dim1.Levels(0).members,
            CrossJoin(Dim2.Levels(0).members, Dim3.Levels(0).members))) ON ROWS
 FROM Zigzag.demo

To run the query open the Analyze screen for the Zigzag.demo cube. Click "Execute MDX" button and enter the query in to MDX dialog.

Click Execute button and see the query results

Now let's modify the query to save the results into a file. Simply add following on top of our original query

EXPORT INTO FILE "demo" OVERWRITE USING COLUMNDELIMITER ","

and click Execute again. This time the result grid will look like below

It means that the query was successfully executed. Navigate to the data base inspector and download the demo.txt file

The file should have the same content as the query result above

Time,Dim1,Dim2,Dim3,Opening Inventory,Shipped,Received,Closing Inventory
Jan,1-11,2-1,3-1,100,10,15,105
Feb,1-11,2-1,3-1,105,23,12,94
Mar,1-11,2-1,3-1,94,48,12,58
Apr,1-11,2-1,3-1,58,1,10,67
May,1-11,2-1,3-1,67,60,90,97
Jun,1-11,2-1,3-1,97,75,1,23
Jul,1-11,2-1,3-1,23,22,100,101
Aug,1-11,2-1,3-1,101,93,100,108
Sep,1-11,2-1,3-1,108,10,50,148
Oct,1-11,2-1,3-1,148,132,13,29
Nov,1-11,2-1,3-1,29,20,100,109
Dec,1-11,2-1,3-1,109,#Missing,#Missing,109

For more details about MDX export see Official documentation