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.

Tuesday, November 2, 2010

Let it Flow, Let it Flow, Let it Flow!

I included in my MDX presentation for Kaleidoscope this last summer a few pages on the Flow property you can apply to the accounts dimension. This applies to ASO cubes only. There is some good documentation on it, but interestingly I don’t remember seeing it in any of the new features documents or presentations for 11. I guess they slipped in in on us without us knowing. There may be reasons for this and I’ll get to them later.
What the flow command is supposed to do is simplify and optimize calculations where a recursive formula might be needed. Until now, it was all academic as I didn’t have a need to try it. I found a good and typical use for it. I’ve built a ton of GL applications (if each application weights an average of 21.7 lbs and you can calculate how many) and in almost every one, I have to create a formula in the retained earnings section of the balance sheet to represent the year to date Income.
For my client I created a typical formula something like Case When ISLeaf([Periods].currentmember) thenSum (PeriodsToDate([Periods].Generation(1),[Periods].currentmember),[Income])End
and it worked just fine. Then I thought about my presentation and figured this would be a good place to try out Flow. I replaced my formula with [Income]
and changed the member type in the outline to Flow (I left it as TB last) and saved the outline. It worked like a charm giving me the desired results. It is a much simpler formula and since it is optimized, I’m sure it is faster than my manual statement.
This is great. So I went to incorporate it into my Studio process. There is no option to set flow. Next I went to the DBAG to see how to set it in a load rule, not there. So thinking swiftly, I decided to contact my friend Tim Tow to see if he encountered it when working on the Outline extractor. Tim did some research for me and found it in the Java API but not the VB api. Tim did say he was going to update the Java version of outline extractor to include this.
So it appears you can set it in the outline but not easily from anywhere else. I’ve got questions out to some people at Oracle, but until I hear more it appears you are limited to manually setting this property. It looks like flow is ahead of its time. Until you can Flow changes to the outline automatically, you are limited in using it.

Update I got a response back from Oracle and the ability to change this from Studio will be added to a future release. I do not have privy to what release that will be. So for now, from within the outline is the only way to set this

While there at it see if they can add a way to define the compression dimension via a load rule. Last I checked there was no way to do this in a load rule and causes an issue if you truly want to build an ASO outline form scratch via load rules.

EIS surprisingly can do this, not sure if Studio can or how EIS accomplishes it, from what I can tell it is doing something that is not exposed to the end user. Would be interesting to see how Studio handles it if it does.