I love the dataexport function in calc scripts. I tend to use it a lot for both writing data to flat files and to relational databases. I’ve written multiple blog posts on it.
Today, I got an email from a fellow consultant who was having problems with it and needed help. It took me a few emails back and forth, but I was able to help them. I decided to post it so we all don’t run into the same issue.
The original email was
“There is a sparse dimension that is dynamically calculated in an app.
I want to export a parent which is dynamically calculated, but even when setting the data export with DataExportDynamicCalc ON; it still exports the level 0 for that dimension. “ If I change that dimension to Dense, then it exports what I want, the parent. I even fix on that parent member but it still exports level 0 of that parent.”
I first responded asking if the member name was explicitly in the Fix Statement and if SET DataExportLevel ALL; was set. I was assured it was. I was sent the whole calc and it looked good.
SET DATAEXPORTOPTIONS
{ DATAEXPORTCOLFORMAT ON;
DataExportDynamicCalc ON;
DataExportLevel ALL;
DataExportColHeader "Period";
DataExportOverwriteFile ON; };
/*EBIT*/
FIX ("1st Pass","Final","Budget", "Actual","FY15", @relative("YearTotal",0),
"EBIT",
"ALY","SAP CC 1000","760","U-ctID","NZU72200",@RELATIVE("Cost Category",0))
DATAEXPORT "File" "," "TESTENABLE.TXT";
ENDFIX;
I was about to write back that that I was stumped and remembered something they said that I skimmed over the first time. “If I change that dimension to Dense, then it exports what I want”
Hmmmm. I started to think about the difference between dense and spares dimensions and how Essbase works. It worked on a dense dimension. OK, So it pulls in the block and can calculate the dynamic members. OK, that is reasonable.
A sparse dimension. Wait a second, there is no block for a dynamically calculated member. In this case, the block is calculated upon retrieval. By default, the fix would bypass empty blocks. I looked at the set statement again and it hit me. There was no statement for emptyblocks. I remember it because I always include it and turn it off in my scripts so I know it is off for sure. I knew there was on and looked it up in the tech reference. SET DataExportNonExistingBlocks ON|OFF
The tech reference describes this function as:
Specifies whether to export data from all possible data blocks. For large outlines with a large number of members in sparse dimensions, the number of potential data blocks can be very high. Exporting Dynamic Calc members from all possible blocks can significantly impact performance.
again hmmmm. All possible blocks. I had the consultant add this to their extract set it to ON and try it. I did warn them this could be slow, but they told me it was a small outline.
Lo and behold it worked like a champ. IT is a valuable lesson. With us typically wanting to improve performance we turn things on and off without even thinking about it. Sometimes we have to go back and reevaluate our options.
Posts
