Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, March 19, 2015

A quick tip for Dataexport

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.


  DataExportDynamicCalc ON;
  DataExportLevel ALL;
  DataExportColHeader "Period";
  DataExportOverwriteFile ON; };


FIX ("1st Pass","Final","Budget", "Actual","FY15", @relative("YearTotal",0),

     "ALY","SAP CC 1000","760","U-ctID","NZU72200",@RELATIVE("Cost Category",0))



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.

Monday, March 2, 2015

We all need to thank Applied OLAP

I typically don’t single out a person or company in my blog, but am doing so today. Tim Tow, Oracle Ace Director, Owner of Applied OLAP, Essbase friend and evangelist, on his blog announce the release of newest version of the Next generation Outline extractor .

Why the big deal? Why am I praising him? First, Tim maintains the code for his love of Essbase, he makes no money from it. Second, it cost him money. Time taken from billable work to make changes is a cost, plus, he has his help desk support people assist anyone with a problem again at no cost.

That is all well and good, but the final thing is his responsiveness in improving the product. I emailed Tim on a Wednesday asking about missing features of the relational extract. Tim and I exchanged a few emails about what I would like to see and how I thought it should work.  By Sunday, I had a beta version of extractor with all I asked for and more.  I know from Tim’s questions that I was not getting work he had already planned, but that he had modified the product for me.  After my testing the changes (I found no bugs), he has released it to the Essbase world.

We all need to tank Tim and Applied OLAP for their continued support of the Hyperion community. I don’t work for Tim, but do think his products are awesome. It is nice that he puts as much care into the free products he supports as he does for his fantastic Dodeca product.