Filter Native Essbase ASO Exports

Essbase 21.6 was released in August. One of the enhancements was improved performance of ASO MDX Exports. In order to use this capability, add the DATAFORMATEXPORT directive to your Export statement. 

It's worth noting that this only works for exporting stored data: only Level 0 members and no calculated or formula members. This enhancement removes the limitation of 2³² cells that was previously an issue for ASO MDX exports. If you previously had to break up MDX exports to get around this limit, this should fix that. Apparently it also adds support for duplicate member outlines. 

The example the documentation gives is super interesting because it includes a property, STORED_FLAG, that I don't recall ever seeing before. This property allows you to filter on stored members. The property isn't listed in the documented MDX Properties. I tried using it in some older Essbase versions and it did work so it's probably been available for a while.

Below is the example given in the documentation:

1:  EXPORT INTO FILE "MDXExport_DataExport1.txt" USING DATAEXPFORMAT  
2:  SELECT Filter([Measures].members, [Measures].currentmember.STORED_FLAG)  
3:  ON COLUMNS,   
4:  NON EMPTY(  
5:   Crossjoin(  
6:   Crossjoin(  
7:    Crossjoin(  
8:    Crossjoin(  
9:     Crossjoin(  
10:     Crossjoin(  
11:      Crossjoin(  
12:      Crossjoin(  
13:       Crossjoin(  
14:       Filter([Years].members, [Years].currentmember.STORED_FLAG),  
15:       Filter([Time].members, [Time].currentmember.STORED_FLAG)),  
16:      Filter([Transaction Type].members, [Transaction Type].currentmember.STORED_FLAG)),  
17:      Filter([Payment Type].members, [Payment Type].currentmember.STORED_FLAG)),  
18:     Filter([Promotions].members, [Promotions].currentmember.STORED_FLAG)),  
19:     Filter([Age].members, [Age].currentmember.STORED_FLAG)),  
20:    Filter([Income Level].members, [Income Level].currentmember.STORED_FLAG)),  
21:    Filter([Products].members, [Products].currentmember.STORED_FLAG)),  
22:   Filter([Stores].members, [Stores].currentmember.STORED_FLAG)),  
23:   Filter([Geography].members, [Geography].currentmember.STORED_FLAG))  
24:  )  
25:  ON ROWS FROM ASOSamp.Basic;  

Line 1: This begins the export command, specifies the output file and adds the new DATAEXPORTFORMAT directive.

Line 2: This begins the Select statement and specifies the column members which include all the stored Measures.

Line 4: Adds the NON EMPTY keyword. I believe this to be unnecessary. According to the documentation this will be assumed.

Lines 5-13: A disgusting list of nested Crossjoin functions. Oracle really needs to fix this. Microsoft MDX allows the joining of more than 2 sets in a single Crossjoin function call. This is an 11 dimension cube. One dimension will be in the columns and 10 on the rows. That means we need 9 Crossjoins. Gross. 

Lines 14-23: A filter statement on each line that returns only the stored members from the remaining dimensions.

Line 25: Ends the Export statement and specifies the cube from which we are exporting.

Here's a snippet of what you get back:

 "Original Price" "Price Paid" "Units" "Transactions" "Returns"  
 "Curr Year" "Jan" "Sale" "Cash" "No Promotion" "1 to 13 Years" "Under 20,000" "Camcorders" "017589" "14036" 656.5 656.5 1 2  
 "13681" 1443 1443 2 1  
 "Photo Printers" "14010" 232 232 1 2  
 "14027" 238 238 1 1  
 "13428" 214 214 1 2  
 "13681" 206 206 1 2 206  

If it looks oddly familiar, it is. This is the native Essbase export format. This new feature is the ability to filter native Essbase exports from ASO. If you want to only export a single year, or a handful of scenarios, you can do that.

It's great that we can do this now but what would really take this to the next level would be to export this ASO data in column format. I saw that on the roadmap at Kscope24 so hopefully we'll get that soon.