Hybrid Essbase has arrived

A patch, but much more than just a patch

After much anticipation, Oracle have released the latest and greatest update to Essbase, the Oracle Essbase Release 11.1.2.3.000 Patch Set Update (PSU): 11.1.2.3.500.  As a patch, you must first have 11.1.2.3 installed.  You then get to experience the pleasures of opatch.exe as you apply the following patches to Essbase and its components (there are many platforms available, the below are Windows 64-bit only):

Component	Patch
Hyperion Essbase Server 11.1.2.3.500	Patch 17767302: Essbase Server
Hyperion Essbase Client 11.1.2.3.500	Patch 17767299: Essbase RTC
	Patch 17767307: Essbase Client MSI
Hyperion Essbase Studio Server 11.1.2.3.500	Patch 17767295: Essbase Studio Server
	Patch 17767296: Essbase Studio Console MSI
Hyperion Essbase Administration Services (EAS) 11.1.2.3.500	Patch 17767309: EAS Server
	Patch 17767316: EAS Console MSI
Hyperion Analytic Provider Services (APS) 11.1.2.3.500	Patch 17767293: APS Services

If you are not on Windows, you can search for the patches.  Yes, the below search is for Windows but go ahead and change it to your platform of choice.  Oracle is OS agnostic.

That Windows-specific search will give you this (I cut off the non .500 files):

Just be sure that you also search for Studio, EAS, and APS.

If you want the whole 11.1.2.3.500 EPM patch (and it is a monster 1.7 gigabyte patch -- I heard about it from John Goodwin’s blog) go to Patch 17529887: Patch Set Update: 11.1.2.3.500 for Oracle Enterprise Performance Management System.  Here’s the readme (you must be logged into Oracle Support to read this).

Why should you care?

I’ll give you my take on this release, but as the saying goes, Read The Whole Thing.

I’m going to briefly cover Hybrid, but I think the other big hits from this release are:

• MDX Aggregate and Sum are sped up
• FIXPARALLEL
• A change to the way Essbase stores BSO data

MDX Aggregate and Sum

A pretty common issue in ASO databases is the need to do a level zero-only calculation (say for a rate calc) and then aggregate up.  Except of course ASO doesn’t work that way – it’s dynamic, remember?  And that means that ASO wants to calculate data at all levels, including that rate calculation.  If the rates are only valid at level zero, this fully dynamic nature results in inaccurate results when those rates are summed and then calculated at upper levels.

Many (including me) have tried to get round that by creating member formulas that test for the level of the dimensions and use MDX to aggregate dimensions on the fly.  This often doesn’t work because MDX calculations are dynamic and hence do not take advantage of the bitmap.  As Dan Pressman would say, doing this is not letting ASO be ASO.  What I say is that this is S-L-O-W.  Too slow, usually, to actually work.

Do it in batch

Oracle have realized this and, in 11.1.2, came up with ASO calc scripts.  They are run via MaxL and the execute calculation command – it’s a little ugly looking but supports those level zero only calcs.  Run that and let ASO be ASO and do the aggregations it loves to do – perfect, right?

What about interactive applications?

While that’s fine for batch processes, it doesn’t work very well in interactive applications.  I’ve seen some stunningly kludgy ways of launching MaxL in the background to do the level zero calcs after a user inputs data.  It works, kind of, but is a pain.

A better way, hopefully

Oracle feels your pain, and have optimized Essbase so that the member formula MDX technique may allow you to do those dimension level tests and then do a Aggregate or Sum.

I note that this may be a solution for you because:

1. I haven’t tested it
2. I have to believe that it only scales so far

OTOH, per the “I haven’t tested it” statement I could be 100% wrong and maybe this approach will work for large dimensions.  Watch this space for a test in the near future.

What does the read me say?

Faster Queries for MDX Aggregate and Sum Functions

In aggregate storage databases, performance is improved for MDX queries containing the Aggregate or Sum functions. Essbase performs dependency analysis and uses a formula cache to execute these requests dynamically.

For the optimized performance of these functions on aggregate storage databases, include in your query the following elements:

• Any of the following functions, used within the named set and/or as an argument to this function: Intersect, CurrentMember, Distinct, CrossJoin, PeriodsToDate. The use of any other functions (such as Members) disables the optimization.
• The second parameter, accounts_member, must be included for optimal performance.

Note: Optimal query performance may require a larger formula cache size. If you get an error message like the following, adjust the Essbase.cfg setting MAXFORMULACACHESIZE accordingly:

Not enough memory for formula execution. Set MAXFORMULACACHESIZE configuration parameter to [1072]KB and try again.

Pretty cool, eh?

FIXPARALLEL

We all know, and mostly love, CALCPARALLEL.  It can be a little tricky to set up correctly, causes greater PAG file (we are back in BSO land, btw) fragmentation, and lots of functions revert Essbase to serial mode, but the performance boost is so great most developers just use as much of it as possible.

FIXPARALLEL is just what it says – parallel calculation for blocks of commands as defined by FIXPARALLEL…ENDFIXPARALLEL.  

A question arises:  If BSO parallel calculation already exists through CALCPARALLEL, why bother with a different method of parallelization?  It’s partly about choice, and partly because FIXPARALLEL doesn’t rely on task selection the way CALCPARALLEL does.  In other words, FIXPARALLEL doesn’t depend on “sparsity, outline order, dependencies, and member formulas” when generating a task list.

Also, FIXPARALLEL works with temporary variables (VAR functions), DATACOPY, and DATAEXPORT (flat files only).  

A change to the way Essbase stores data

This one caught me by surprise, mostly because it tells me that the way I thought Essbase stored data in blocks has been wrong.  

Per the ReadMe:

Prior to this release, each time a data block was updated, it was written to a new disk location. With this release, for Exalytics, Essbase enables in-place data writing.

In-place data writing means that when updates occur, the data block can be written to the same location, as long as the compressed size of the data block fits in its original location on the disk.

In-place data writing can help reduce data fragmentation and lower the need for frequent restructuring of database. It also reduces the need for frequent index updates, resulting in improved performance.

I could have sworn that Essbase always did that.  In fact, many people (although not Glenn Schwartzberg) have.  Even Oracle (at least previously) stated:

The Average Fragmentation Quotient ratio measures free space in a given database.  As you update and calculate data, empty spaces occur when a block can no longer fit in its original space and will either append at the end of the file or fit in another empty space that is large enough.  These empty spaces take up space in the .PAG files

I, and others, took this to mean that when Essbase can write a data value back to its original block, it did.

Reading this, and Glenn’s comments that this has always been the way Essbase works led me to try an experiment with Sample.Basic on 11.1.2.3.000:

1. I cleared the database and loaded calcdat.txt. That got me to 1 for the Average Clustering Ratio and 0 for the Average Fragmentation Quotient as displayed in Esscmd’s GETDBSTATS command (I am not exactly sure this is yet available in MaxL).
2. I went into Smart View, navigated to New York->Budget->Sales->Jan->100-10, changed 640 to 645, and did a submit.
3. Looking at GETDBSTATS again showed me that Sample.Basic now had an:
1. Average Clustering Ratio of 0.9999647
2. Average Fragmentation Quotient of 0.04882698

Glenn was right (yet again, I might add -- I wouldn’t bet my life on arguing technical functionality with Glenn as I am still to young to die), changing even a single value causes Essbase to fragment.  I can only conclude, just as the documentation states, Essbase, outside of Exalytics, this release, and the INPLACEDATAWRITE Essbase.cfg setting, that Essbase for most of us does not write back to the same location.

Hybrid is here, or ASO meets BSO

I’ve been waiting for this one ever since it was announced at Oracle OpenWorld 2013 – Hybrid, formally known as BSO/ASO Hybrid Aggregation Mode, is a fascinating piece of code.  What it essentially does is brings ASO dynamic aggregations to BSO databases.  This is beyond huge because it solves the data explosion issue that has bedeviled BSO Essbase since its inception.

Hyrid aggregation means that you get all of the BSO goodness (think all of those great BSO calc script functions) that you know and love but get away from the all (or most) of the bad things in BSO that stem from the aggregation process of stored members:

• Upper level blocks don’t have to exist
• Database sizes are much smaller
• Calc times are much shorter (or even nonexistent)

It’s all very exciting stuff that I and Dan Pressman will cover in coming blog posts and of course our ODTUG Kscope14 presentation Evolution or Revolution:  The New Hybrid Essbase.

The future

I’m going to stop for now (mostly because I have to have a life and because I need to get rid of the beta patches I have and install the commercially released version) but will note that this this release has a lot of limitations as one might expect for an initial go round.  Think of 11.1.2.3.500 as a statement of direction, with limitations being taken away and new functionality being added over time.  

The next few blog posts will cover what I and others discovered during the beta process, some of the workarounds, and the implications of this exciting new extension to BSO Essbase.

You may know that I was part of a team that wrote a book all about advanced Essbase:  Developing Essbase Applications.  In it, author Dave Farnsworth wrote a chapter called, “BSO Will Never Die and Here is Why”.  Despite many people’s attempts to declare BSO dead, I think it’s safe to say that Oracle doesn’t agree.

Be seeing you.

An appeal for Mike Riley, cancer victim

Who is Mike Riley?

This will be a blog post like no other I have ever written.  I make it a very deliberate point to not make this blog about marketing, sales, or any kind of commercial appeal.  That isn’t why you, oh Gentle Reader, are here.

This blog post is an appeal, but it is not related to commerce of any sort.  It is an appeal for a man who is a friend of all EPM geeks – Mike Riley.

Have you ever wondered how EPM came to ODTUG in the first place?  Or why ODTUG has been such a comfortable home for EPM?  It is largely due to one man – the very same Mike Riley, former ODTUG board member, past president, and current conference co-chairman who is the subject of this post.  Success has many fathers, and Mike was not alone in his support of all things EPM at Kscope, but he was the primary guiding light.

When I reflect on what ODTUG has done for my professional education, network, and career it is a little hard to credit given its impact.  I am firmly convinced that my Oracle ACE Director award (not to mention my continued employment) would have occurred in, oh, about 6,000,000 years (I may be underestimating) if ODTUG had not opened its collective arms and welcomed me and all of you into the community of professionals that make up ODTUG.  Professionally, we owe Mike a debt of gratitude.

ODTUG is too big for Mike to have met and touched every one of you (although I have seen Mike work a room – it certainly isn’t for lack of trying), but for those of us who have interacted with Mike in depth, we personally owe Mike a debt of gratitude.  This obligation stems from the fact that Mike believes in the best in all of us.  I have watched Mike take flyers on people that he barely knew, given them the room to succeed (or fail and that in particular takes a special kind of courage), and unwaveringly support them as they became fully fledged volunteers in ODTUG.  Not many people have that insight, maturity, or altruistic concern for others.

If Mike is reading this, I know like all good modest Midwesterners (my family hails from Michigan so I get it) he is a bit uncomfortable with praise, especially publically delivered.  Mike, I would not write the above if every word wasn’t exactly how I feel.  Try not to blush.  :)

This post is not intended to be a hagiography (the above is the unvarnished truth) but instead that appeal I wrote about.  What do I mean by appeal?  Mike Riley has stage III rectal cancer.  As horrific as that sounds, I should note that this is not an obituary.  He is under treatment; Mike has undergone radiation, surgery, and now chemotherapy.  Getting and beating cancer is not for the timid.  Despite the almost unimaginable difficulties he has undergone his spirit remains and he is tough.

His family – wife Lisa, daughter Morgan, and son Cameron (What an excellent choice in male names.  Ahem.) are all tough too.  They need to be.  Mike and his family are always in my thoughts and prayers.  Cancer is a cruel bitch of a disease.  And that’s where you can materially help.  

Enter OracleNerd

Chet Justice, fellow Oracle ACE Director, Kscope14 database content chairman, OracleNerd, and super nice guy has initiated several fund drives for Mike.  The first was an informal one to get Mike to see his home town St. Louis Cardinals play the Boston Red Sox for the 2013 World Series.  Chet then really got serious and set up a GoFundMe charity drive for Mike and his family.  Its goal is $10,000 and is almost there – a testament to Mike’s friends and the ODTUG community (I am not too sure that both sets of people aren’t one and the same).  

Please ignore that fact that the fund has almost reached its goal because the medical monetary needs of Mike and his family continue – insurance does not cover all expenses and they add up rapidly.  Remember how I wrote cancer is a cruel bitch?  First it destroys your body, then it destroys your savings.  Cruel is an understatement.

Please give generously

The ODTUG community, and Essbase hackers in particular, are generous, kind, and thoughtful people.  We are obligated to Mike for his pioneering work in giving so much to the EPM world.  He is also one hell of a nice guy and he needs our help.  Edward Roske has asked you to do this; I am asking again.

I wrote in the first paragraph of this post that this would be a blog post like no other I have ever written and in over 150 posts I have never written about a person or appealed for money.  I likely, God willing, never will again.  The exigencies of Mike’s medical situation demands that I do.  And they demand that you reach into your heart and help him.

Give generously, won’t you?

Calculation Manager everywhere

Four goals have I

This must be Calculation Manager week.  (Do you have themed technology weeks?  No?  You must have some semblance of a life.  How I envy you.)  Putting aside the sadness (or happiness) of my life, this week is Calculation Manager week because during this very same set of five days the following occurred:

1. On Tuesday, I gave an ODTUG webinar on The Most Awesome Planning Calculation Manager Hack the World Has Ever Seen
2. There was a question about how to convert graphical Calc Mgr Templates to Script over on Network54
3. A while back (okay, not within the same week – give me some artistic license) a thread also n Network54 about the reusability of graphical versus Script objects
4. I was asked if it really was true that Calc Mgr 11.1.2.3 Templates cannot accept parameters

Whew, all (mostly) in one five day period.  Sometimes you choose Calc Mgr, sometimes Calc Mgr chooses you.  

The Most Awesome Planning Calculation Manager Hack the World Has Ever Seeen

I’ve written, presented, and generally bored the ears off of any number of unfortunates who happen to be near me when I start talking about the benefits of Focused Aggregations.  Focused Aggregations is my term for using Planning and Hyperion Business Rules/Calculation Manager (actually, this is totally transferable to Dodeca and calc Scripts as well) to figure out what is in the Planning form POV and Page dimensions and then only aggregating the bits of the hierarchy that are impacted by the changes on the form.  This radically reduces the scope of the calculated blocks and hence improves performance, sometimes a lot.

What this approach, until this great hack was invented (not by yr. obdnt. srvnt. alas), did not do was interpret the dimensions on the rows and columns of the form.  This necessitated an AGG of the sparse dimensions which kind of negated the whole point of Focused Aggregations – an AGG just aggregates everything in the specified dimensions.  And there the collective EPM world was until Christian M. came up with the technique through trial, error, and sheer desperation.  I would throw in a bit of inspired genius as well.

I blogged about it here in some depth and in this webinar go even deeper into the concepts behind Focused Aggregations and of course show how to do everything along with benchmark results.  It is a hack, but it is a most glorious one.  And you will note that this is a blog with “hack” in its title.

Go listen to the presentation (I think my presentation does add something to the explanation) if you wish, or simply download a pdf of the presentation here for a quick review.  Your choice.

Converting graphical objects to Script

AP (I have no idea who AP is) was in a bit of a pickle – he had converted a Hyperion Business Rules (so 11.1.2.1 or before) Planning application, complete with HBR macros, to 11.1.2.2.  In doing so, the HBR macros went from being Script based (BSO calc Script language) to graphical.  The question was:  How does one convert those graphical Templates (Templates being the Calc Mgr replacement for HBR macros) to Script?

Here’s an example of a Workforce Planning Template.  You can see that there isn’t any conversion functionality.  And while some people are proponents of graphical Calc Mgr business rules, I am just too steeped in writing code to switch, so my interest was piqued by AP’s question.

It’s pretty easy

After spending five minutes trying every which way I could think of to convert the Template from graphical to Script, it occurred to me that I had reviewed graphical code as BSO calc Script language by clicking on the Begin node of a rule and then selecting the Script tab in the properties pane.

Looking at the rules that come with Workforce, I can see that there’s a rule called “Add TBH Hourly” that contains the very same Template. 

 

NB – I am doing this with a predefined rule, but you could easily create a your own rule and drag a Template into it to accomplish the same result.

If I open the rule up I see that graphical Template.

By clicking on the Script tab in the bottom properties pane, I get:

That code can be copied and pasted to a text editor.  Once there, simply create a new Template and you are off to the races.

Here it is in Notepad++:

It’s a bit of a hack, but hey, remember, you are on a blog all about hacks.  And it does get AP to where he wants to go.

Speaking of graphical versus Script

There was a thread a while back about a whole host of things about Calculation Manager over on Network54.  (Technically this didn’t happen this week but now is a good time to review the thread.)

In the course of that thread, it was stated:

The bit that sort of surprised me was this statement:

“Yes. In fact, if you don't use it, you are probably missing out on one of the most powerful features of Calc Manager: reusable code objects.”  

The “it” being Calc Mgr’s graphical interface and the implication being that you cannot reuse code unless it is graphical.  Hmm, really?  Perhaps I misunderstood.

And then that poster went on to write:

Okay, I didn’t misunderstand.  Again he asserted, “To CL's point, I believe that when a developer converted the "graphical" Scripts to the other mode, we lost the reusability features and had to manually search-and-replace in those objects. And so, I have avoided it.”

Hmm, really?  That bears testing out.

For the record, I am taking reusability features to mean – can a code based Script object be reused by reference and can a code based Template be reused by reference in Calc Mgr rules?  That, to me, seems to be the essence of reusability.  I think I know what specific reuse functions that commentator was referring to as “lost” and he isn’t the only one to think that.  I will address that additional functionality later in this post.  For the time being, let’s set the above baseline and prove or disprove it.

Test the first with Scripts

Here’s the simple code as a Script object:

Here’s a rule referring to a shared Script:

Here’s the deployed Script’s status after execution in Planning:

So that code-based Script works as a reusable object.

Test the second with Templates

The same simple code:

And now in a different rule:

Note the nice comment blocks that Calc Mgr gives us when we refer to a Template.

And here it is after successfully running in Planning:

Conclusion

Code based objects, whether they be Scripts or Templates can be reused.

Yet another assertion that ain’t necessarily so

A former client of mine was converting an 11.1.2.0 Planning implementation to 11.1.2.3 via a consulting company That Shall Not Be Named.  That company’s consultant got to one of my HBR macros with parameters and came up with a pretty interesting claim:  In 11.1.2.3, Templates cannot receive parameters.  

Huh?  Really?  That would be a huge reduction in functionality.  I think it’s pretty safe to say that Oracle’s approach with the EPM products is not to reduce functionality, but to expand it.  A lot.  So I was rather surprised by this statement.

And it got more interesting because the consultant then went on to claim that he had checked with his company (I would actually do the same if I were he and of course if I had a company to fall back on) and they had confirmed this.  Verrrrrrry interesting, if true.  But that reduction in functionality just didn’t make sense.

So, in my Doubting Thomas persona (which is actually pretty close to way I view much of the world and is quite a useful one when it comes to IT), I went off to either prove or disprove this assertion.  From such interactions are blog posts born.

A review of what Template parameters are and why you would care

Calc Mgr provides two different ways to share code as demonstrated above – through either Script or Template objects.

Given that the above example is functionally equivalent, it does sort of beg the question as to what Templates are for.  After all, if a Script and a Template are the same, and a Script is easier to work with, why bother inventing Templates?

Just like HBR macros, Templates can be snippets of code or completely contained code streams.  

Also, and here is what is crucial when you compare Scripts and Templates, Templates can be just simple code Scripts or huge combinations of Scripts, graphical objects, etc.  Really anything you can create in an overall rule you can put into a Template.  This is a huge jump in functionality over Scripts and of course (and this is one of the many things I like about Calc Mgr) it is completely up to you how you do this – have multiple discrete Templates and call them from a rule, have a huge Template that does all the logic, or have multiple discrete Templates that are managed by a calling Template that in turn is driven by a rule.  Pretty cool, eh?

Again, just like HBR macros, Calc Mgr Templates (no matter how they are organized) can have parameters passed to the code stream that are then dynamically substituted on execution. This is a valuable bit of functionality because it allows us to write code as an object and then, as required, pass values to that bit of code.  Please note that this is not the same as receiving the value of a Calc Mgr variable.  That is also available to a Calc Mgr Template but those values are driven from Planning to Calc Mgr.  What I am talking about is designer-specified values getting passed as required.

One use case might be custom currency conversion with multiple rates.  Depending on how you write the code, you might want to pass different rates (actual, forecast, estimate, favorable, unfavorable, etc.) to the same base chunk of code.  It’s the same code, just different rates (a specific member name) substituted in the code.

Of course you could rewrite the code multiple times and just repeat one code block after the other or you could create multiple Calc Mgr Scripts and use them as required but both techniques end up with way more code.  In turn that larger code base will need to be maintained.  Ouch.

If you could take that base bit of code and just substitute in the right rate you’d have a single point of maintenance.  Less code that performs the same logic is A Good Thing.  Unless you like maintaining redundant code in which case please stop reading this post.

Parameters, parameters, parameters

Assuming that you are lazy programmer, and hence a good one, let’s look at how Template parameters work.

But first a Template without a parameter.

And its calling rule.  Yes, this is silly.  I am only showing this to set up the functionality.

So what happens when I drag and drop the Template into the Script?  I am going to show this in screenshots and also take a movie of it so you can see exactly what happens.

Here’s the drag:

And the drop:

Huh?  Where’s the Template?  I dragged and dropped the Template into the Script and nothing showed up.  

I think that is why both consultants said there was an issue with Script-based Templates.  But I also think they are conflating a bug with a feature.

If this is unclear, go check out this movie.  And yes, I’ve confirmed that this is a known defect to be fixed in 11.1.2.3.500.

Code reference

Did you know that Templates are referenced via code?  It makes sense of course – our problem seems to be that a drag and drop does not result in that code.  But if we knew the syntax, might we simply enter it?  Referencing a Template code (without parameters) looks like this:

%Template(name:="Templatename",application:="appname",plantype="ptname",dtps:=())

Although you can actually get away with:

%Template(name:="Templatename")

Here it is in Calc Mgr just after validating:

So that’s one way around the issue of the drag and drop issue.

A Template with a parameter

So how do you reference, in code, a Template with a parameter?

It’s just an extension of the above example.  Here’s the code for referencing a Template with a single parameter.  

%Template(name:="Templatename",dtps:=("parametername":=[[parametervalue]]))

NB – Multiple parameters are comma delimited.

Defining a bad parameter

I am going to show you the wrong (or at least incomplete) way to do this so we can both experience the fairly awesome error message.  

First I am going to insert a new row in the Design Time Prompt (DTP) tab of the Template.

I’m going to give it a name of paramYear and select it as type member.  Note the interesting other selections you can make for the type:

 

I am then going to replace the hardcoded “FY12” with paramYear.  Just click on the insert variable toolbar button and then select the DTP you want:

Here it is in substituted into the code.  Note the square brackets [] around the parameter.  This is what normal variables looked like in HBR.  Calc Mgr does this to differentiate between DTPs and Calc Mgr variables which have curly bracket {} symbols around the variable names.

Now let’s go to our calling code, type in by hand the DTP settings, and see what happens:

What do we get when we try to do a validate:

Unpossible!  How did that not work?  It is in fact syntactically correct, but we didn’t set the DTP correctly.  This is going to be a little unintuitive, but bear with me.

Defining a parameter correctly

Let’s go back to the DTP definition.  

First we need to make this promptable.  Yes, it is weird that we have to set up a DTP with a prompt that in fact will never get prompted (it is set at design time, so what’s to prompt?) but again, just bear with me.

NB – Optionally, you could select the Choose Dimension Limits to limt the scope of the RTP that shouldn’t ever need to be selected.  

Then click on the Template wizard.  Within this wizard, you must create a step for the DTP to be executed.  This isn’t totally intuitive, at least to me, but here’s my explanation of why this is important:

1. The concept of a step only makes sense within the context of a promptable DTP.  A promptable DTP itself only makes sense if you are passing a RTP variable into the Template. This is correct. The value could be a RTP variable from the rule or any text, member name or function.
2. A step is analogous to a step in a wizard.  When there is a prompt, the prompt has to cycle through steps to receive a value.  If the calling code is passing a Calc Mgr variable (which is a RTP on its own) to the Templates DTP, that step is executed. Correct. Any promptable DTP that is part of a step that was skipped due to some condition will be set to blank at the end of the wizard completion.

I should mention that the text in black is my explanation, and the red text is from Sree Menon, the Oracle Calc Mgr product manager who very graciously responded to my questions.  More about Sree down in the conclusion of the this (long) post.

In any case, you need to create a step, and then assign the paramter to that step.  Think of it as a wizard that must be executed for the Template to run with parameters.

Click on the + symbol next to the Step dropdown:

Then give the step a name.

Then move the completed Step over to the Selected DTPs listbox:

Save the Template, and go back to your calling rule and validate:

Success!  Boil in bag!

Now try dragging and dropping that Template into the rule (we’re going to calculate FY11 as well as FY12).

You’ll get a prompt for the value.  Select type member:

Select FY11 and then click on Finish in the Step1 member selection dialog box:

You now have a rule with a drag and drop defined code step for the Template tmplAGGDims.

Save, validate, and deploy the rule, and then go test it out in Planning:

Ta da!  You now have two parameter driven selections in a 11.1.2.3 Template!  It is possible.  

And what have we learnt?

A few interesting things:

1. Calc Mgr can do all kinds of amazing things wrt Planning, including now finally squaring the circle on Planning rows and columns.  This is only thanks to the generosity of the Oracle EPM community (Christian M and his coworkers).  We own them a huge thanks because the inability to do this has hurt many Planning applications.  In my opinion, this improved Focused Aggregation should be standard practice in all BSO Planning implementations.
2. You can, if you wish, use Script or graphical objects in Calc Mgr and reuse them, or not, to your heart’s content.  There is no restriction on reuse based on graphical versus Script status.  None.  To state otherwise is belied by what I demonstrated above.
3. You can pass parameters to Templates in 11.1.2.3.  If you don’t correctly define the DTP for the parameter, Calc Mgr doesn’t throw an error the way it should (I would rather have a “Yo!  You forgot to set a step.  Go back and sin no more.” kind of message than just blank lines.  It appears that Oracle agrees given that this issue will be fixed.) but it does work.  A little experimentation proves that.
4. And that leads to the observation that there is opinion, and then there is fact.  Sometimes the two match up; sometimes they do not.  No matter what, opinion and fact are not the same thing.  This blog most definitely has a voice, a point of view, and a large dose of my opinion.  But it is also backed up with fact – I try to put myself in the shoes of a Doubting Thomas and then prove that what I say works.  Sometimes I realize that I have been dead wrong.  You never get to see those blog posts because they are memory holed.  And when I am wrong and I don’t test enough and then publish it (I am thinking of that post where Jason Jones rather kindly corrected a pretty egregious mistake on my part) I dig right into that delicious dish of crow.  I deserve it and while my ego is wounded my commitment to technical correctness doesn’t allow me any other path.  Would it that all consultants thought the same way.  :)
5. Speaking of opinions, you shouldn’t believe everything you read on the internet.  But you knew that, right?  And if I were you, I wouldn’t believe anything yr. obdnt. srvnt. claims without fully testing it out on your side.  Trust but verify is not a bad internet-as-a-source-of-wisdom approach.

One other observation:  Sree Menon, the Calc Mgr product manager, suggested to me that I stick his Oracle email into this post and let world+dog beat a path to his Calc MGr door.  I am quite familiar with the spam, phishing, and generally criminal nonsense that comes to this blog via its comments (and that you don’t see because I memory hole the lot), so I am not totally comfortable doing that.

What I propose instead is that if you have some burning question you want to ask Sree, please post it in the comments of this post, I will review it, contact you, and then if you pass the test (the test mostly being are you a human being asking for help or a spambot looking to make his life miserable) I will introduce the two of you.  I am not Sree’s keeper but believe me when I tell you that this blog attracts weirdos (other than the weirdo writing all of this stuff) like iron filings to a magnet.  In any case, the offer from Sree is geniune per our email conversation (again, me in black, Sree in red) below:

This definitely is blog material.  Is it okay if I mention you?  Not your email address, but just your name?  Or would you prefer to remain anonymous? I have no issues in either giving my name or my oracle email. If people want to contact me directly using my email id, on one hand, I would love that. If I am able to help people, then I feel blessed.

Now that kind of response makes this particular geek get all misty eyed.  Thanks again, Sree, for all of your input (and the input of your fellow product managers and developers).  Thanks again!

Be seeing you.