Conditional FIX statements

A few years ago the very very talented Mike Henderson gave a Kscope presentation entitled Power Tools: Using VAR + LOOP for If-Then Procedure Logic and ARRAY for Speed (the presentation can be found here.) First and foremost the presentation has the easiest to understand and most detailed description of how arrays work in essbase – including a perfect use case and lashings of ginger beer code!

It also describes a nifty way of building conditional IF statements, allowing differences in treatment between two scenarios, without the performance and code cleanliness hit of having to do repeated IF(@ISMBR(“Sparse”)) intersections.

The only issue I had with Mike’s proposal is that…it’s slightly confusing.

SET RUNTIMESUBVARS { 
  runForecast=0 Enter 1 to skip Forecast scenario; 
  runBudget=0 Enter 1 to skip Budget scenario; 
};
 
VAR ForecastBreak = &runForecast;
LOOP(1,ForecastBreak)
  [Forecast code goes here]
ENDLOOP
 
VAR BudgetBreak = &runBudget;
LOOP(1,BudgetBreak)
  [Budget code goes here]
ENDLOOP

Basically it’s almost inverse of what you’re expecting. The variable ForecastBreak needs to be set to 1 if you want to SKIP the Forecast code. After I’d used the code in a few different places, I ended up practically shelving it because I was having to explain what the code was doing (sometimes to myself. I tried not to do this out loud.)

That said, it’s still an elegant solution to a fairly fundamental problem. Most other coding languages have the ability to do a conditional IF statement, and it definitely has use cases within Hyperion.

Fast forward a few years

A few years later I was doing some (all too brief!) work with the brilliant Danish Hotta and noticed in his code the following:

SET EMPTYMEMBERSETS ON;

FIX("Forecast" AND {Var_Scenario})
  [Forecast code goes here]
ENDFIX
FIX("Budget" AND {Var_Scenario})
   [Budget code goes here]
ENDFIX

Being an inquisitive fellow, and having never seen that code pattern before, I immediately asked (somewhat incredulously in hindsight) what on earth that was meant to do. He explained that it was to split the code up between Forecasts and Budgets.

I’d like to pretend that I believed him immediately and obviously didn’t have to have it proved to me…and given that this is my blog I shall take the liberty in stating that it only took around 10-15 minutes of patient explanation before I understood what it was doing.

So what IS it doing then?

Since this appears to be completely undocumented behaviour, let’s break it down to the component parts and work out what is happening.

SET EMPTYMEMBERSETS ON;

Emptymembersets is that function that you don’t need to understand, right up until it bites you and then you set it in every script.

The documentation is actually very clear as to what it’s doing:

If EMPTYMEMBERSETS is ON, and a FIX command evaluates to a (sic) empty member set, the calculation within the FIX command stops and the following information message is displayed: “FIX statement evaluates to an empty set. Please refer to SET EMPTYMEMBERSETS command.” The calculation resumes after the FIX command. If a calculation script contains nested FIX commands, the nested FIX commands are not evaluated.

However, the pertinent point is ‘what does evaluation to an empty member set’ actually mean. It is not a evaluation of data, instead, an empty member set is where the intersection of the fix statements resolves to something fundamentally invalid. Often times this will occur when using functions within FIX Statements ie:

SET EMPTYMEMBERSETS OFF; /* This is the default */
CLEARDATA "Actual";
FIX(@Children("BegBalance"))
    Actual = 100;
ENDFIX

Begbalance is a level zero member, ergo it does not have children, so the reference it is an invalid. The EMPTYMEMBERSETS flag then defines the calculation behaviour, with the default behaviour completely ignoring the Fix statement and loading data to all periods.

emptymbrsets_off_log
emptymbrsets_off

Setting EMPTYMEMBERSETS to ON then changes this behaviour – ignoring both the fix statement and all of the calculations within it

SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual"; 
FIX(@Children("BegBalance")
    Actual = 100;
ENDFIX

emptymbrsets_on_log
emptymbrsets_on

To further the point, the greatly venerated C.Lackpour wrote a very long blog post on this – going into detail about differences in behaviour of EMPTYMEMBERSETS with nested fix statements. I would strongly recommend reading it before continuing this post – I’ll wait.

Okay…you’re back

So now we know what EMPTYMEMBERSETS does – how does this help us with a conditional IF? This is where that Boolean AND function comes into play.

SET RUNTIMESUBVARS
{
Var_Scenario="Budget";
};

SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
CLEARDATA "Budget";
FIX(Actual AND &Var_Scenario)
	AUD = 100;
ENDFIX
FIX(Budget AND &Var_Scenario)
	AUD = 200;
ENDFIX

In FIX statement above, the scenarios are hardcoded, and then a scenario variable is passed through with a Boolean AND statement. In this case I’m using Essbase run time sub variables but could just as easily be done in a Planning application with calculation manager variable.

Tricka Technology

Triggering the calc above and looking first at the output in smartview, we can see that the Budget data has calculated, and the actual data has not changed.
budget_output
Reviewing the log we can see the that the resolutions of Actual AND budget has resolved to an empty set, therefore ignoring the FIX statement and all of the code therein.
budget_log

Tricka Technology the Remix

Changing the variable to Actual and rerunning?

SET RUNTIMESUBVARS
{
Var_Scenario="Actual";
};

SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
CLEARDATA "Budget";
FIX(Actual AND &Var_Scenario)
	AUD = 100;
ENDFIX
FIX(Budget AND &Var_Scenario)
	AUD = 200;
ENDFIX

Exactly what we’d expect to see:
actual_output

The most interesting aspect of this is to look at the log. The evaluation of the fix statements actually occurs as the first step of the code – and essbase is therefore aware that this fix statement can be ignored and doesn’t even try to resolve it later. This can act as a staggering optimisation in any code because it completely skips over even the resolution of anything within the fix statement.
actual_log

So there you have it

A clean and simple method to have conditional and variable driven FIX statements optimally calculating a component of a script. It works across both Dense AND sparse intersections and can be used both to standardise and simplify code – as well as providing the ability to selectively optimise any rules, particularly where you can pass through parameters from a planning dataform.

Cheers
Pete

Sub Variables, and a Glorious Dataform Hack

G’day All,

I’ve been a bit lax with regard to posts – apologies – but Cameron harassed me to post this up after he’d not heard of it. To be honest, this is one of those ‘been in my back-pocket for so long I just assumed everyone knew’ kinda features (is there an acronym for that?)

Basically I was asked if anyone knew a way of showing only certain periods on a dataform…without having to update the form every month. This is one of those things that feels like it should be possible (nay easy) but…well…isn’t. Hence this hack.

Where did this all come about…

One of the common problems with Planning dataforms is a fairly hefty limitation in how they can be structured. This has general led to a number of obscure and imaginative hacks to show the data that you want. Significant examples of limitations include:

  • No UDAs available to dynamically filter member selections
  • No range substitution variables
  • Limited member selection functionality

Oh for the want of a cloud…

For those of you already heavily using PBCS you’ll note that a number of these functions have been significantly improved. Including:

Member Exclusions
01-exclusions
Significantly More Member Selections
02-new-member-selections

This gives you the flexibility to build dataforms however you like. In our case, it allows you to selectively show Right or Left members of a member.

So setting a subvar to &Period_Plan = Nov and defining a column header as IRSIBLINGS(&Period_Plan) will show just November and December!
irsiblings

Terrestrial Based Development?

On-premises you’re more limited. The functionality to allow for member selections is basically only Level-0 references and children – difficult if you wish to selectively show certain periods based upon a sub variable.
20161113_dataform2

So what are our options? Interestingly what you would ‘expect’ to work doesn’t. Defining “Nov,Dec” as a sub variable will not allow it to be used – see both ActPeriods and Period_Act_Range for examples of what I tried (although they are obviously for the Actual periods!) Using these, saving the dataform and attempting to open it will just throw a default ‘dataform is invalid’ error message.

20161113_subvars

What’s this about Merging SubVars then?

One of the interesting default behaviours of Planning dataforms is to ‘merge’ the same member into a single column. If you have a column or a row selection with the same member listed sequentially (ie: Actual, Actual, Budget, Forecast) it will merge the Actual columns together rather than duplicating the column.

Therefore we need to work out a way that we can use and abuse that behaviour to suppress the columns that we don’t want to show. In order to do this, we create 12 sub variables.
subvar_definition

The variables are then updated with the ‘same’ value for the first month to be shown. In this example I’m showing just Nov and Dec, but with 12 variables it could obviously be extended to a full year. Obviously in a case like this you’re going to want to automate the setting of sub variables… but I’ll leave that as an exercise for the reader.

Building the Dataform

Then our dataform becomes easy – the period dimension is defined as all 12 variables.
20161113_dataform

The closed variables then merge together on the dataform and we are left with only the plan periods. Simple!

20161113_dataform_updatd

Obviously we’re all hoping for the next round of 11.1.2.4 patches to bring some of this PBCS functionality back to on-premise (my kingdom for on-premise smartpush!) – however for the moment it’s a very effective, if a touch inglorious, hack!

Cheers
Pete

PBCS and Fun New Features

KScope Wrap!

G’day All,

So Kscope16 is over, done and dusted, and I’m back home. A quick thankyou to everyone who I met – was amazing to be able to put a face and a voice to the names/blogs/twitter accounts I’ve followed over the years!

For everyone else – you’ll be glad to know the likes of Jason Jones, Cameron Lackpour, Tim German and Celvin Kattookaran (and others I’ve naught mentioned for brevity, but you know who you are) are as tragically nerdy amazingly bright, excited and enthused about this space as their blogs suggest. I was very endeared by their absolute state of gregarious comradeship – a spirit of learning and challenging yourself to become better – lead to both a humbling and inspiring couple of days.

A really quick story before I get started on the actual post. I was standing in the main foyer with a few people watching the boats go by when Celvin (a Chi-City native) commented that: ‘It’s amazing you know – tour guides on the boats volunteer all their time! They don’t get paid at all, they just do it because they love the city of Chicago’. Silence reigned for a few moments – until the irony of Celvin (of all people) commenting in shocked admiration that ‘people that volunteer their time, just for the love of it’ sunk in!

Moving on…

So this will be the first in a series – perhaps not as compleat as others – looking at some of the new features in PBCS that will hopefully wend their way back to On-Premises at some point!

This is a 16.07 PBCS Test environment – released early July.

First thing I’ll be looking at is the automated Smartlist generation.

Step 1 – Nice and Easy

Nice and simple, we create a few new members. You’ll see here I’ve created a list of members under a parent Super Rates.

Create_New_Members

The keen eyed amongst you might notice one of the other changes – duplicate aliases are now in place and supported completely. Aliases can have the same name as the member (and other members!) as long as the member names are unique.  To quickly reference the release notes:

“When referencing an alias that resolves to multiple members in free-form mode, an error is returned noting that the alias cannot be resolved to a single member. You can do this either by using the Member Selector to select the correct alias name or by hand-typing the qualified name. Using the parent member name as a qualifier should be sufficient in most cases, given that only duplicate alias names are supported, not duplicate member names.”

Step 2 – Nothing Doing

Next up, create a smartlist, same as normal.

Create_Smartlist

At the bottom you’ll see the “Create From Members” Checkbox with a member selection. I’m obviously picking the descendants of Super rates rather than hard coding the list (which becomes very cool shortly.

Step 3 – Too Easy

After saving the smartlist, returning to the entries page shows the members!

Review_Smartlist_Delete

You can see that the Name is set to the Member Name, while Label has been updated to the Alias. In an earlier test I used the Aliases as the member names (because smartlist names have some limitations) – the spaces were removed as well as the special characters (%) and replaced by underscores, but the label was still correct.

Step 4 – Mixing in the Awesome

You’ll now be wondering what happens if you create a new member – well let’s have a looksee.

Adding_New_Members

Created a new fake member under Super_Rates, and upon refreshing the database…

Step 5 – Bada Bing Bada Boom

Review_Smartlist_Add

Awesome.

But there’s no point if it hangs around, so let’s see what happens if we delete the member and refresh it

Step 6 – I’ve Overdone the Pringles Thing…

Huzzah!

Manage_Smartlist

Wrapping Up

So that’s awesome and should help massively in defining smartlists going forward! Stay tuned for some more new features (as I get a chance to find them and test them!)

Cheers

Pete

 

Adding and Removing Years, PBCS and the joy of UI

G’day All

So. Been a while. A nice easy one to, er, get back on the horse.

Years

There are two processes for changing the first year in an Hyperion application.

Option 1) The Delete and Recreate method (aka. the one that starts scary)
  • Take a full LCM backup of your planning application
  • Delete the planning application, and recreate, changing the first year when you define it.
  • Reimport all of the dimensionality from your backup, playing a fun game dealing with the scenarios and whatever else fails
  • Rejoice that you have added a year to the start, then realise you’ve now got to deal with all that additional data
Option 2) Hack the relational tables (aka. the one that seems easy to start with, but ends in tears)
  • Start googling for ‘how to add a year to Hyperion Planning’
  • Find the usual suspects (Ahem. The Usual Suspects)
  • Think to yourself, ‘oh this can’t be too hard’ – conveniently forgetting that you have the SQL skills of a rabbit
  • Convince IT that you definitely need the passwords and access to the SQL server
  • Spend 4-5 hours working out where all the references to years are in the HSP_Object, HSP_Calendar etc etc tables
  • Spend a further 4-5 hours writing your own custom SQL insert statement (with associated fear and trepidation and assistance from the Overflow …sorry, the Overflow)
  • Trigger the statement, restart planning and marvel at your wondrous SQL skills
  • Remember you then have to replicate the same thing across 3 environments and sigh

Head in the Clouds

So what does this look like in the cloud? You don’t have access to the relational tables, so are you stuck just dropping and recreating the application every time?

Well. No. Entertainingly the behaviour in the cloud is a little different.

Let’s start with a standard years dimension. You can see I’ve already been a bit cute and added the ‘All Years’ member, but it’s pretty standard.
1 - Years Pre Change

From here, same as always. Click on the Add Years button:

1 - add Years button

And type in the number of years to add:
2 - Click to Add Years

Once again, nothing too off-piste yet. Click OK and you are presented with the following:
3 - Confirmation

As a quick aside. This…somewhat poorly worded confirmation screen triggered this entire blog post. I remember thinking ‘No. Surely if they are allowing you to create years at the start it’s going to be clearer than a partial double negative‘.

Of course, being the good corporate citizen I am I immediately clicked No to see what would happen.
4 - Years added to the Start
Yep. Years added to the start. Err…Suprise!.

For those playing along at home…

So – we wait for everyone who has a cloud instance to go and immediately add years to the start while cackling madly. Now what?

Well, we’ve proved that you can add year(s) to a Hyperion Planning application in the cloud. What about deleting them?
5 - Remove Years
Right next to the add button is a remove button, clicking on it leads to a very similar screen…(so similar in fact, it comes with the same Tooltip! Err…I think that’s a bug)
6 - Bad tooltip

Selecting a number of years and clicking OK leads you to the similar, and once again ‘slightly convoluted‘ confirmation message.
7 - Remove confirmation

Clicking No…well, does exactly what you’d expect.

The Years are removed and we’re back where we started!
8 - Back to the Start

The world ain’t all sunshine and rainbows

So a couple of little quirks:

  • Same as deleting members, you need to make sure the years aren’t referenced on dataforms\within calculations etc etc.
  • Review the scenario dimension for references as well – you can’t delete a year if it’s set as a start or end year

9 - errors

So there you have it

Adding and Removing years in Hyperion Planning without angst, muss or fuss.

Be awfully nice to see this in on-premises….

Cheers, and looking forward to seeing an awful lot of you at Kscope!

Pete

MDXExport and Hybrid Allocations

G’day All,

So I’ll skip the shenanigans of Gaullic invasions and move straight to the good stuff – but this is basically a copy of a post I guest wrote for Cameron after being horrified that VBA was his go-to for dealing with MDX.

The agony and the ecstasy
Upon first reading Cameron’s blog post on allocations in Hybrid I was horrified, disgusted, appalled, gobsmacked bemused regarding the machinations required to achieve something so simple. I believe there may have also been the words, “VBA??? That is the sound when doves cry.”, although to be honest I haven’t directly heard that but imagine that would be the sound of doves in their cognitive programming agony if they could code in VBA. But I digress. Surely, there is a better option!

Here are the issues to consider:

  • All upper level members of a Hybrid cube are dynamic
  • If we calculate against those upper members, we will throw the cube out of Hybrid mode. and into traditional BSO sparse dynamic calculations…at which point we’ll probably beat it in calculation using an abacus. Or Roman numerals. Or counting matchsticks. You get the idea. This is a Bad Thing To Be Avoided.
  • Therefore we have to work out a method of getting the calculated upper level data to a stored level zero member.
  • The only way to pull the data out at the upper level of a Hybrid cube is MDX.
  • MDX is…not very elegant when run out using MAXL (hence Cameron’s everything-is-a-nail-if-VBA-is-your-hammer strategy). Cameron notes that this analogy is applicable to screwdrivers, sledgehammers, and ODI when used to build Planning dimensions.

Back to the Future I – A history lesson
Okay – so the underlying problem is pretty simple. Let us now jump back to an earlier time, an easier time, a simpler time. Hyperion version 11.1.2.2. Before all of this Hybrid nonsense. When BSO cubes ruled the world and performance was lacklustre but we liked it that way.

One of the brand new and world-breaking features in 11.1.2.2 was the advent of the @XWRITE statement. The counterpoint to the @XREF – this would allow us to write data from one cube to the other and had some amazing potential! Its benefits were easily recognisable – because you were only writing from active blocks, performance was significantly increased, and @XWRITE also had one key advantage in its ability to create the blocks on load! Gone were the days on needing to put CREATENONMISSINGBLK above your @XREF statements and waiting hours.

One of the items slipped in the documentation for this function is as follows: “For @XWRITE only, a reserved keyword @LOOPBACK can be used to write to the same database.” Curious – but actually had some utility in its ability to create blocks. The very talented David Ambler turned this into an elegant strategy to create blocks for a balance sheet in future years – pushing opening balances from the closing balances of the prior year.

Now I bet I know what you’re all thinking – that’s great Peter, so you’re just going to use @XWRITE from the parent to write to the stored level-0 member. Well done for reusing a four year old function. Except…that doesn’t work. @XWRITE is one of those top-down formulas that doesn’t work in Hybrid (and I did try). The cube drops out of Hybrid mode and we’re back to the abacus.

Still it’s a nice idea. Use a function to write back to the database directly, rather than export the data, map it and reload it. Now all we need is a function that natively uses MDX.

Back to the Future II – Where’s my hoverboard
Fortunately Oracle has provided one! The new (from 11.1.2.3.502 at least) CDFs of MDXDataCopy & MDXDataExport. Perhaps even more fortunately, Celvin Kattookaran has already written up how it all works! (whom despite never having met, I feel indebted to a significant number of drinks for all of the assistance he’s provided over the years) .

So, we’ve got a function that can export data using MDX thus keeping the database in Hybrid mode as well as the ability to map data from one member to another.

Where it’s at
Using good ol’ Sample.Basic, albeit a Hybridized/bastardized version of it, the code is as follows:

//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
"whynotzoidbergasAESkey==" /* key */
"pemO6ZElxHY7m570TfvhDB8H4WMcxP53wjWPlB26SwETEj/so3WuzB8ZBjnJBhUJ" /*user*/
"nqkqpUXwhhAywRPnn/ZFxOy5kKpb7iZoZWf6bA1NOJVR1hBzj15k2hpzjcgIwidC" /* password */
"Hybrid" /* from application */
"Hybrid" /* from database */
"Hybrid" /* to application (can be the same as the source) */
"Hybrid" /* to database (can be the same as the source)*/
"{[Scenario].[Actual]}" /* MDX that defines the column members */
"crossjoin(crossjoin(crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]})"  /* MDX that defines the row members */
"Sales,Market,Product" /* source member mappings, can be empty */
"Sales Driver,No Market,No Product" /* target member mappings, can be empty */
"" /* Target POV columns, members from dimensions that do not exist on the source*/
 "-1" /* rows per page */
"e:\\Ifthisworkscameronowespeterabeer.log"; /* log file , can be empty */

Breaking it Down

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy

Using the RUNJAVA command because we only want to run this command once and it’s all defined. There is also the function @CdfMDXDataCopy which could be used, but is not necessary here.
"whynotzoidbergasAESkey==" /* key */
"pemO6ZElxHY7m570TfvhDB8H4WMcxP53wjWPlB26SwETEj/so3WuzB8ZBjnJBhUJ" /*user*/
"nqkqpUXwhhAywRPnn/ZFxOy5kKpb7iZoZWf6bA1NOJVR1hBzj15k2hpzjcgIwidC" /* password */

This command requires a fully encrypted username and password and an associated AES key. In order to encrypt the key you need to use the calcmgrCmdLine.jar utility which can be found Essbase server. The command is as follows:

java -jar calcmgrCmdLine.jar -encrypt -key  
java -jar calcmgrCmdLine.jar -encrypt -key

I had a number of problems with this because the error handling of the AES string length isn’t…great. Basically you need a 24 character key with enough entropy in it – it doesn’t actually matter what it is (see my example) but you’ll need to test a few options (or just hammer the keyboard to get 24 chars).

"Hybrid" /* from application */
"Hybrid" /* from database */
"Hybrid" /* to application (can be the same as the source) */
"Hybrid" /* to database (can be the same as the source)*/

This one is pretty self-explanatory. The standard use of this function is to write to other databases – but as you’ll see, you can replicate our @LoopBack function from earlier and write back to the source.

"{[Scenario].[Actual]}" /* MDX that defines the column members */
"Crossjoin(Crossjoin(Crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]})"  /* MDX that defines the row members */

Okay: so this is one of the limitations of this process – the MDX query is simply a Column and Row definition. No Where clauses etc. Still, for what we’re trying to do here it’s reasonably functional.

"Sales,Market,Product" /* source member mappings, can be empty */
"Sales Driver,No Market,No Product" /* target member mappings, can be empty */

Here is where some of the fanciness lies. Really simple mapping (it’s actually just a basic string replace function) but allows us to write from a parent to a child to move the data to a stored member.

"" /* Target POV columns, members from dimensions that do not exist on the source*/

This is blank because we have no additional dimensions (the target IS the source). However, this is potentially useful when you’re mapping to an ASO reporting with additional dimensions.

"-1" /* rows per page */

As described in the documentation – this is a function to allow for a limiter of queries. If it’s a positive number only the number of rows listed will be extracted from the source and mapped. Useful only if you’re concerned about an out-of-control MDX query. Setting it to -1 will extract and load everything (and is obviously recommended

"e:\\Ifthisworkscameronowespeterabeer.log"; /* log file, can be empty */

A log file that will be generated on the essbase server. Incredibly useful for troubleshooting as it actually shows the processes and the outputs!

2015-08-28 10:09 >  Used Memory (Mb):8 Free Memory (Mb):237 Total Memory (Mb):245 Max Memory (Mb):245
Query:SELECT {[Scenario].[Actual]}on columns,crossjoin(crossjoin(crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]}) on rows FROM Hybrid.Hybrid
User:admin
Source:Hybrid/Hybrid
Target:Hybrid/Hybrid
Rows Per Page:-1
Signed in User:admin
Grid Size: Rows(13) Cols(5)
				Actual
Jan	Sales Driver	No Market	No Product	134590.0
Feb	Sales Driver	No Market	No Product	130233.0
Mar	Sales Driver	No Market	No Product	132922.0
Apr	Sales Driver	No Market	No Product	128346.0
May	Sales Driver	No Market	No Product	131673.0
Jun	Sales Driver	No Market	No Product	135427.0
Jul	Sales Driver	No Market	No Product	132977.0
Aug	Sales Driver	No Market	No Product	137744.0
Sep	Sales Driver	No Market	No Product	122903.0
Oct	Sales Driver	No Market	No Product	127760.0
Nov	Sales Driver	No Market	No Product	127837.0
Dec	Sales Driver	No Market	No Product	130086.0

Once you’ve got it working this should be set to missing for performance reasons.

So how’s it perform then?
On the topic of performance – how fast is it? On a fully loaded (admittedly a customised/hybridefied Sample.Basic) database of 9 dimensions with the biggest being 14k and 9k members and with the data logging turned on:
Total Calc Elapsed Time for [Test.csc] : [0.263] seconds
Without the data logging:
Total Calc Elapsed Time for [Test.csc] : [0.061] seconds

Given that you could put this step immediately after a data load (it’s Hybrid sonny – ain’t no aggregation time) that’s pretty ridiculous in an absolutely awesome way.

What have we learnt today boys and girls?
Four key takeaways from this:

    • MDX is an awesome language – as long as you can work out a method of parsing it, or just not needing to.
    • Hybrid is going to take some getting used to in order to come up new methods of calculating things
    • Sree and his team in the Oracle Calculation Manager group are coming up with some fantastic CDFs to drive a whole stack of functionality going forward. Definitely worth looking at some of them if you haven’t already.
    • Cameron really needs to step up his game ‘cos he ought to have figured this out on his own.

Bonus For Timf
So after Cameron posted this up, TimF in the comments section pleaded asked if anyone had tested it sourcing from an ASO cube.

So yes – for Tim – I tested it. And it’s awesome.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy 
"whynotzoidberglngthAES===" /* key */ 
"EmUPiLFGiSufeNHa1zQ43smFThCX4BL1HFfX/IjDJFVhNePVyAZucdLI93fH0ZpA" /*user*/ 
"FiYG8Gk4GuafZ3PJqCpcpfLsXqQyEASsjGORAYjhTW0hBez9jTksnPlZT65iwYPT" /* password */ 
"ASO1" /* from application */ 
"ASO1" /* from database */ 
"HybBSO" /* to application */ 
"Plan3" /* to database */ 
"Crossjoin(crossjoin({[Account].[Test_Acc]},{[Version].[Final]}),{[Scenario].[Rolling]})"/* MDX that defines the column members */ 
"crossjoin(crossjoin({[Years].Levels(0).Members},{[Entity].Levels(0).Members}),{[Period].Levels(0).Members})"  /* MDX that defines the row members */ 
"" /* source member mappings, can be empty */ 
"" /* target member mappings, can be empty */ 
"" /* Target POV columns, members from dimensions that do not exist on the source*/
 "-1" /* rows per page */ 
"c:\\ifthisworkstimowesmeabeer.log"; /* log file , can be empty */

Finally we’ve got an “xref” function for ASO to BSO data movements. And it’s lightning quick, creates the blocks on the way in, and could be variable driven (see Celvin’s recent posts)

But what if you’ve got more dimensions in ASO

//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy 
"whynotzoidberglngthAES===" /* key */ 
"EmUPiLFGiSufeNHa1zQ43smFThCX4BL1HFfX/IjDJFVhNePVyAZucdLI93fH0ZpA" /*user*/ 
"FiYG8Gk4GuafZ3PJqCpcpfLsXqQyEASsjGORAYjhTW0hBez9jTksnPlZT65iwYPT" /* password */ 
"ASO1" /* from application */ 
"ASO1" /* from database */ 
"RMITPOC" /* to application */ 
"Plan3" /* to database */ 
"Crossjoin(crossjoin(crossjoin({[Account].[Test_Acc]},{[Version].[Final]}),{[Scenario].[Rolling]}),{[New_ASO_Dim].[Test_New_ASO_Dim_Member]})"/* MDX that defines the column members */ 
"crossjoin(crossjoin({[Years].Levels(0).Members},{[Entity].Levels(0).Members}),{[Period].Levels(0).Members})"  /* MDX that defines the row members */ 
"Test_New_ASO_Dim_Member" /* source member mappings, can be empty */ 
"" /* target member mappings, can be empty, */ 
"" /* Target POV columns, members from dimensions that do not exist on the source*/
 "-1" /* rows per page */ 
"c:\\ifthisworkstimowesmeabeer.log"; /* log file , can be empty */

Catch you all later!

Cheers
Pete

HSP Date Functions in 11.1.2.4

G’day All,

As promised – my final post on time\date factors\arbitrary rants about date systems.

This time, we delve under the covers to have a look at some of the new HSP functions – which massively simplify any work that you need to do with dates in Hyperion!

Quick Recap

H’okay. If you’ve read back through my previous posts about time in Planning and Essbase (specifically this one and that one) or alternatively if you’ve ever beaten your head against the desk repeatedly about the seemingly cumbersome way in which essbase and planning use dates then you’ll be aware of the following:

    • Planning and essbase have different date systems
    • What works in Planning doesn’t work in Essbase
    • What works in essbase doesn’t work…basically full stop
    • It’s really difficult to efficiently use dates
    • The ‘simplest’ method is often to convert dates into effective strings and do ‘math

But, with the new HSP functions – that’s pretty much all changed.

Surprise Documentation!

Since these functions have been added there have been screams (well…modest complaints) about the staggering lack of documentation. Fortunately some amazing members in the EPM community have taken it upon themselves to change this, and through a combination of blog posts over the years have added many notes:

Alp Burak Beder – HSP Custom Functions 1
Alp Burak Beder – HSP Custom Functions 2
Cameron Lackpour – Calc Manager BSO and PLanning
Celvin Kattookaran – Calc Manager Execute MAXL and MDX
Robert Gideon – Calc Manager HSP Update 11.1.2.3
Robert Gideon – 11.1.2.4 New Functions

And now it seems Oracle has come to the party – adding the following to their Calc Manager \ Business rule Documentation.
Oracle Working with Custom Defined Functions

So let’s have a look then!
Okay, so perhaps most importantly with these functions – they are designed to work directly on Planning input dates. As such, they are expecting an Essbase date in the format (“YYYYMMDD”) and a Planning Date in the selected date format in the Planning apps.
DateSettingsinPlanning

The nice thing about this (for you crazy Americans) is that you can use discrete date options per user, but still use the same underlying functions. See date entry in mm-dd-yyyy in Planning, while the Smartview retrieve is showing dd-mm-yyyy.
Crazy_American_Dates

Or. You know. Your could standardise.
Standards

The Juicy Bit
Where was I….

Below is a listing of almost every date function in 11.1.2.4 – I’ve put in the function, the usage and the outputs (in both essbase and planning).

All Date HSP Functions

Some interesting functions in that list: CalcMgrRollDate – This is a cool function! Only for Month and Day

@CalcMgrRollDate:
[Adds or subtracts (up or down) a single unit of time on the given date field without changing larger fields.
For example,
@CalcMgrRollDate(19960131,"month",@_true) will result in the date of 19960229.
@CalcMgrRollDate(19960131,"day",@_true) will result in the date of 19960101.]
This is very useful. Rolling forward the ‘last day’ in the month without needing to deal with differing month lengths. It’s also setup for leap years, so massively reduces coding.

Another useful function is @CalcMgrGetCurrentDate. At first I couldn’t get it working, but after a quick google landed me on Robert Gideon’s blog and I was on my way. Could be invaluable if you wanted to script a data export to pass a date to the filename.

I did have issues with @CalcMgrGetDatePart and @CalcMgrAddDatePart returning slightly odd values or no values hence their exclusion above. However the main component of their functions have been replaced by more discrete functions, so they have limited utility anyways.

Anyways – I’ll hopefully be back soon with another blog post on the REST of the HSP functions. Any questions \ comments, hit me up in the comments.

Cheers
Pete

IPMT and PPMT in Essbase

G’day All,

The subtitle for this post could be “How to make sure the guy after you doesn’t hate you – making code legible”

As the newest Ace Associate and java-junky-Jason-Jones points out – code is read significantly more often than it is written

He then also used the term “behooves” correctly – so perhaps he just felt in the mood to wax lyrical.

Anyways.

The underlying logic for the functions PMT\IPMT\PPMT is not that difficult. Basically: for a fixed term loan you are to work out the full repayment, the interest component and the principle component. Obviously as you go through the loan period the ratio of interest to principle decreases, you pay more interest at the front when your principle amount is high and more principle at the end when the amount owing has diminished.

For a thorough and absolutely fantastic walk through of how to convert the excel function into code (including examples of Java & SQL!) you should definitely go and read this article by Kevin Cross . Actually – you should go and read it anyway – it’s an incredibly clear yet detailed discovery document, stepping through all of the issues, giving exceptionally clear examples (and counter-examples) and then finishes with a multitude of questions and answers for specifics. If the rest of world’s software documentation was half as good as this then there would be significantly fewer stressed developers (and a hell of a lot less random posting to stack overflow)!

And in Essbase?
So. To convert what is there to essbase functions. It should be noted that I’m cheating a little – this is really the IPMT and PMT functions in their default state, with a simple loan type and no value remaining at the end.

/* 
Tn is the length of the loan.
n is the period in question.
pv is the present value
rt is the Interest Rate
Lease Payments PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
Interest On Lease IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
Principal on Lease (PPMT) = PMT - IMPT */

No worries right? Too easy.

Where it all becomes a bit…messy

I got to actually writing this piece of the code however, and I realised I was going to have a bit of a problem. Tn (length of the loan) was a calculated field based upon a lookup off a smartlist with an override. N was a dynamic calc member. pv was a piece of data entry against a completely different intersection and rt was another lookup, with another potential of an override! Given that the IPMT code was already of a significant length, how could I make this code actually readable?

To give you an example of what this code looked like with all the cross-dimensionals (and note, this doesn’t include the overrides!)

Interest_On_Lease_IPMT = ("Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12 + 1)) - @POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Loan_Period"))) / (("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1) * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12) - 1));

Yeah. Not wonderful. And to be honest, once I add in the 3 other alternatives with the overrides, quite ridiculous.

The calculation for IPMT isn’t ever going to change – however, it’s possible (although unlikely) that the data locations might. Therefore, if down the track one of the data locations needs to be updated, somebody will need to go through this function piece by piece ensuring that they only change the right bit.

Clean the yard
So, how to make it easier? How about we make all the data locations variables instead?

var tn = 0;
var n = 0;
var pv = 0;
var rt = 0;

/* Open Calc Block */
Lease_Payments_PMT(
	/* If there is a lease override use it, otherwise use the interest rate */
	IF("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
		rt = "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
	ELSE
		rt = "Interest_Rate"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
	ENDIF
	/* If there is a asset life override use it, otherwise use the default asset life */
	IF("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
		tn = "Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12;
	ELSE
		tn = "Asset_Life_Global"-> "Unallocated Organisation"-> "Unallocated Activity"-> "Unallocated Line"-> "BegBalance"-> &Year_Assump * 12;
	ENDIF
	/* Set Present Value and Period */
	pv = "Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump;
	n = "Loan_Period";
			
	Lease_Payments_PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
	Interest_On_Lease_IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
        Principal_on_Lease_PPMT = Lease_Payments_PMT - Interest_On_Lease_IPMT;	
);

Much easier. The code matches up exactly with the actual function (thus making it significantly easier should somebody ever need to additional interest functions) and it’s also much easier to review (and change!) the data locations should they ever need to be updated.

And trust me. The guy after you will thank you for it.

Cheers
Pete

A History of Dates, Essbase and Why Unix time sucks

Summary
A multi-page rant about time, leap seconds, the amount of acid the Unix developers took in the 70’s and Essbase vs planning time.

Note: As per the previous blog post – to my American audience – in this post I will not be using the arbitrary retarded roller coaster  of US date formats, instead I’ll generally be using the significantly more ‘standardised’ dd\mm\yyyy format or yyyymmdd as needed. In interests of fairness, I’ll probably flag the ones that are potentially confusing, but rest assured, I’m not creating new months.

Where it all begins
Since the beginning of computing – the interaction of dates within a binary computer system has caused issues. Unix time in particular has an interesting history – in summary, the original timing mechanism was designed to calculate 1/60th of a second – with the epoch time being 01/01/1971. However they quickly realised that utilising a 32-bit integer only gives you around 3 years of ‘time’ to play in – so the timing was changed to every second and the epoch date changed to 01/01/1970 . Entertainingly this means we face another Y2k bug in 2038 when we hit the 32-bit overflow tracking in seconds.

Essbase dates are a variant on Unix Time – aka the number of seconds since 01/01/1970. That is to say, Essbase dates are rendered as a sum of all the seconds since 1970, with the exception of leap seconds, of which there have been 25 since 1970, with another due in June 2015.

Confused yet? To be honest, it’s getting a little bit arbitrary…still, it could be worse

In a sense this is similar to the date method inherit to Excel whereby dates aren’t really dates, but are sequential numbers counting the number of days since the 1st of January 1900.

As an aside I always wondered why they picked that date. As it turns out, they did that in excel v1.0 (released in 1987) to match up with Lotus 1-2-3 (the incumbent at the time). However, not only did they pick that date to match up with Lotus – they also deliberately included the same error: both Lotus and Excel include 29/02/1900 – despite the fact that it doesn’t exist!

To make this story slightly more entertaining – when VBA was released in 1993 it was decided to ‘fix’ the incorrectly added 29/02/1900 – however in order to keep the date function consistent between VBA and Excel, the date value in VBA actually starts on the 31/12/1899!

Back to the point: one of the other key differences between Excel and Essbase is the ability of Excel to actually use those dates in a sensible way. Excel has the concepts of Day(), Month(),Year(), EDate() – all easily available and all exactly as you’d expect functionally. Essbase…doesn’t.

Lets examine @DATEROLL for instance

Functionally very similar to EDate() – it’s designed to add a number of x (where x can be defined as period type) to a date. This example adds 10 months to the date.

So in Excel:

EDate(MyDate1,10)

In Essbase:

MyDate2=@DateRoll(MyDate1, DP_MONTH, 10);

That’s still not ‘too bad’ right? Lets try something a bit trickier. Find the first day of the next month after a defined date.

In excel:
Roll the month, take off the number of days in the entered month and add 1

MyDate2=EDATE(MyDate1,1)-DAY(MyDate1)+1

Alternatively – find the end of the current month and add 1!

MyDate2=EOMONTH(MyDate1,0)+1

Okay, now in Essbase:

Var DateNextMonth
DateNextMonth = @DateRoll(MyDate1,DP_DAY,1);
MyDate2=@DateRoll(DateNextMonth,DP_DAY,((@DATEPART(MyDate1,DP_DAY) * -1)+1));

Getting needlessly messy but it’s not too bad. So what’s the problem you ask?

Bringing Planning into the Mix

Some/most of you will be aware of the ‘date’ data type in Planning. Gives you the awesome ability to load dates to a planning dataform – exceptionally useful for depreciation models, employee costing / payroll models, birthday reminder applications (seriously – consider it – automated financial reporting emails with birthdays on them, it’d be awesome. I wouldn’t forget my mother’s birthday ever again!).

The issue is – for reasons that are unknown and are honestly baffling – the formatting of dates from Planning do not match a valid Essbase date format.

These are the available Essbase Date formats.
Valid Essbase Date Formas

The one missing? Yep – the format that Planning uses when it pushes data to Essbase: “YYYYMMDD”

So what does all that mean?
Basically when you’ve got dates coming from a Planning application you need to deal with the fact that the incoming format isn’t the same as what Essbase expects. And as such, you’re going to need to massage it.

Let’s look back up at our example above, finding the first day of the next month after a defined date.

Option 1 – Converting Planning Dates to Essbase Numbers

VAR NewDate1inSecs = 0;
VAR NewDate1inSecsRolled = 0;
Var NewDate2inSecs

/* Convert Newdate1 to secs */
NewDate1inSecs = @DATEROLL(0, DP_MONTH, (((@INT("NewDate1"/10000) - 1970) * 12) + 
@INT(@MOD("NewDate1",10000)/100) -1)) + 
(86400 * (@MOD("NewDate1",100) -1));
/* Roll NewDate1 to the next month */
NewDate1inSecsRolled = @DateRoll(NewDate1inSecs,DP_DAY,1);
/* Net back the days to the first day */
NewDate2inSecs=@DateRoll(NewDate1inSecsRolled,DP_DAY,((@DATEPART(NewDate1inSecs,DP_DAY) * -1)+1));

/* Convert my date 2 back to a planning date format */
Var NewDate2Format
Var NewDate2Yr
Var NewDate2mth
Var NewDate2day

NewDate2Format = @FormatDate(NewDate2inSecs,"yymmdd");
NewDate2Yr = @INT(NewDate2Format/10000);
NewDate2mth = @INT((NewDate2Format - NewDate2Yr*10000)/100);
NewDate2day = @MOD(NewDate2Format,100);

NewDate2= 20000000 + (NewDate2Yr * 10000) + (NewDate2mth * 100) + NewDate2day;

Okay, that’s getting silly. Needing to convert in and out of dates every time is simply going to complexify your code. At this point, you might as well just start splitting the Planning date into strings and not bothering with the inbuilt essbase date functions.

Option 2 – Using Math against Planning Date strings

VAR yr = 0;
VAR mth = 0;

/* Calc within a block */
NewDate2(
/* Find the Year */
yr = @INT(NewDate1/10000);
/* Find the month */
mth = @INT((NewDate1 - Yr*10000)/100);
/* If December */
IF(mth == 12)
	NewDate2 = ((yr+1)*10000) + (1*100) + 1;
/* Else roll the month */
ELSE
	NewDate2 = (yr*10000) + ((mth+1)*100) + 1;
ENDIF

Definitely simpler – but manipulating strings is a bit inelegant!

Let’s get exotic

Okay, so what if you really want to/have to use the Essbase date functionality. Let’s have a look at the other options.

In 11.1.2.1 you’re very limited as the only HSP function you’ve got available is HSPDateToString – however what that does is very useful. Basically that will take a Planning Date string (YYYYMMDD) and format it as a DD/MM/YYYY. That in itself is fairly useless, but in combination with TODATEEX you can convert a planning date directly into an Essbase “number of seconds” value. Which can then in turn be used in the Essbase functions!

NewDate1inSecs=@TODATEEX(“dd/mm/yyyy”,@HspDateToString(“NewDate1”));

This still has the underlying problem that you end up with an Essbase Date as the answer rather than a Planning one, but is useful for functions like @DATEDIFF

From 11.1.2.2
From 11.1.2.2 onwards, you can use start to use some of the ill-documented Custom Defined Functions:

[CDATA[ @HspDateRoll(startDate, noOfPeriods, periodType)]];
[CDATA[ Returns the next date, resulting from adding noOfPeriods to StartDate.]]

Okay – similar format to DateRoll – but you don’t have to modify the date on the way in. Instead it uses the Planning dates and RETURNS a Planning date. So returning to our original problem – finding the first day of the next month after a defined date.

Var DateNextMonth
DateNextMonth = @HSPDateRoll(MyDate1,DP_DAY,1);
MyDate2=@HSPDateRoll(DateNextMonth,DP_DAY,((@DATEPART(MyDate1,DP_DAY) * -1)+1));

Much easier!

We’ve also got the Planning equivalent of @DateDiff in @HSPDateDiff. This gives us a the difference between two planning dates – using the same functional format as DateDiff but using Planning Dates
NumofWeeksDiff = @HSPDateDiff(MyDate1, MyDate2, DP_WEEK);

Conclusion
As you can see – the default behavior of Essbase and Planning dates interaction is fiddly at best. However Oracle have definitely added some new functionality in recent versions that gets over most of the underlying issues.

In the next post we’ll look at some of the 11.1.2.4 & Cloud Functions that provide ‘real’ date functionality, and basically allow for pretty much any work with dates.

Finally – in researching this post I’m heavily indebted to the following authors whom have written excellent articles about dates (and a variety of other things!) Check them out.
Jason Biard
Alp Burak Beder

Cheers,
Pete

Time and Dynamic Date Factors

Intro
As it is with most things, you start writing a blog post about something, and two weeks, 4 environments and 16 test cases later it’s morphed into 3 blog posts.

So as follows:

  1. In this post, a useful tip about combining variables, dynamic calcs and scripts to deal with start and end Planning dates in Essbase
  2. Coming up: a multi-page rant about time, leap seconds, the amount of acid the unix developers took in the 70’s and essbase vs planning time. Also included is the different options you have to model dates.
  3. Finally: a short investigation of the ‘new world’ of custom HSP functions for dealing with dates

Hang about, it’s going to get fun.

A quick side note: To my American audience – in this post I will not be using the arbitrary retarded roller coaster of US date formats, instead I’ll generally be using the significantly more ‘standardised’ dd\mm\yyyy format or yyyymmdd as needed. In interests of fairness, I’ll probably flag the ones that are potentially confusing, but rest assured, I’m not creating new months.

Time
The concept of time in multi dimensional databases sucks. As soon as you’ve split time across two different dimensions (Years & then Months in Periods is a standard) you run into cross dimensional problems – now you need to reference multiple dimensions every time you write a single query, but how do you come up with a method to model data across multiple dimensions that is easy and consistent?

Clients don’t help either! Whether it’s an employee costing app, a capital depreciation model, or even a simple balance sheet model – users are going to want to enter and use dates across a spectrum of data.

So, you’ve got a problem. How to model the impact of ‘something’ across multiple dimensions in Essbase while meeting the three golden rules:

  1. Performance
  2. Ease of use
  3. Simplicity of maintenance

Requirements

So, we’ve got our requirements, lets build out a specific example. FTE – modelling Full Time Equivalent across periods to drive Salary Cost.

A really basic example is shown here.

StartPoint

  • FTE value sourced from Payroll
  • Start Date sourced from Payroll (formatted as YYYYMMDD – straight from Planning)
  • Contract End date sourced from Payroll (you’ll note it’s the middle of the month)

So, what we want to do is take the FTE data loaded in BegBalance and push the FTE value into the correct periods.

Starting from the beginning then, we’re going to want to create some outline members to provide reference points. I’ve created these as dynamic calcs – primarily to make them appear at all levels and hence make them easier to use.

 

And for my first trick…

FirstdayinMonth
FirstDayInMonth

This is a dynamic calc member that will populate the first date of every month in every month in our application. It’s pseudo dynamic in that you have to set the first year (in our case FY13) – but it will continue to work as we add years. @COUNT on a @CURRMBR is not very efficient, but it’s more than fast enough for our purposes as a dynamic calc.

 

And for my second trick…
DaysinMonth
DaysinMonth
Exactly what it says it is. A count of the number of days in the month.

Also – do you see my fancy formula in there to deal with Feb 29, except for century leap years every 100 years? Yeah – that’s me – future proofing code for the year 2100…

 

Nothing up my sleeves…

Okay – so now we’ve got these members, let us put them together in a script:

Taking it from the top:
Script_1
Standard fix statements to get us to level 0. Nothing particularly fancy in there with the exception of VAR commands. You can see I’m setting up three variables: StartDate, EndDate and Datefactor for use in this script.

Script_2
Starting to define the variables. Startdate and EndDate are set to the member values from earlier on while EndDate is defaulted to the upper end of the range if it’s missing. You’ll note it’s not actually a real date. This is for many reasons – some of which will be clarified in the next post – instead we’re going to use the Planning dates as numeric ranges for this calculation.

Script_3
Defining out some case statements for the ‘DateFactor’ variable. You’ll see how the code uses a combination of the FirstDayinMonth dynamic calc member with the DaysinMonth calc member to define a range of days in the month. One further nice feature of this is making DateFactor=#missing for the periods that shouldn’t have data. This way, when you calculate the value (see next section) you end up with #missing rather than creating a zero value block.

Script_4
Final step – just simple multiplication. Datefactor has calculated a ratio in every period (a combination of Month and Year) and you can then multiply it by the value to generate monthly values.

 

Multiply it out and Put a Bow on it
FinishingPoint

So you can see the output of the calculation – we’ve now got FTE’s in each period and 0.5 FTE in the final period as per the date ranges.

Lets come back to our original requirements and see how we did.

  1. Performance – it’s lightening quick. No stored members and everything calculated on demand in the script makes this about as fast as it’s possible to be
  2. Ease of use – The same concept of ‘datefactor’ can be reused everywhere you require dates to impact period calculations
  3. Simplicity of maintenance – This code piece is basically drop-in and forget. You only need to setup the variables to your start and end dates and then calculate the output at the end. Only potential issue is block creation in outer years as I’m using a dense member, but you could look at reworking the function to make it a sparse member assignment (using scenarios is an oft overlooked option)

As always, full code available at the bottom. Note you’ll have to update all of the dimensionality to your own application!

Hope that helps some of you – check back soon for more fun with dates.

Cheers

Pete

SET UPDATECALC OFF;
SET AGGMISSG ON;
/* Fix on future years, level zero periods and the Scenario variable */
FIX(@IRSIBLINGS(&Year_Curr),@RELATIVE(“Yeartotal”,0),Rolling,Final)
/* Fix on Level 0 Sparse */
FIX(@Relative(Organisation,0),@RELATIVE(Activity,0),@RELATIVE(“Position Employee”,0),@LEVMBRS(“Employee Type”,0))
/* Define a set of variables to handle dates */
VAR startdate = 0;
VAR enddate = 0;
VAR datefactor = 0;
/* Open a calc block – this allows interdependent formulas as well as the variables */
“FTE_Payroll”(
IF (&Assump_Year->”BegBalance”->”Start_Date”!= #MISSING)
startdate = &Assump_Year->”BegBalance”->”Start_Date”;
enddate=&Assump_Year->”BegBalance”->”Contract_End_Date_Payroll”;
IF(enddate == #missing)
enddate = 99999999;
ENDIF
ENDIF
/* 5 case statements exist for datefactor
1: start and end dates are fully inclusive of current month
2: start and end dates are fully exclusive of current month
3: start date is within current month, end date is inclusive of current month
4: start date is inclusive of current month, end date is within current month
5: both start and end dates are within current month
*/
/* 1: start and end dates are fully inclusive of current month */
IF(startdate=”FirstdayinMonth”+1 AND enddate=”FirstdayinMonth”+”DaysinMonth”)
datefactor=1;
/*2: start and end dates are fully exclusive of current month */
ELSEIF(startdate “FirstdayinMonth”+”DaysinMonth” OR enddate”FirstdayinMonth”)
datefactor=#missing;
/*3: start date is within current month, end date is inclusive of current month */
ELSEIF(startdate “FirstdayinMonth”+1 AND enddate=”FirstdayinMonth”+”DaysinMonth”)
datefactor=(“DaysinMonth”-(startdate – @INT(startdate/100)*100) + 1)/”DaysinMonth”;
/* 4: start date is inclusive of current month, end date is within current month */
ELSEIF(startdate”FirstdayinMonth” AND enddate”FirstdayinMonth”+”DaysinMonth”)
datefactor=(enddate – @INT(enddate/100)*100)/”DaysinMonth”;
/*5: both start and end dates are within current month*/
ELSEIF(startdate “FirstdayinMonth” AND enddate”FirstdayinMonth”+”DaysinMonth”)
datefactor=((enddate – @INT(enddate/100)*100)-(startdate – @INT(startdate/100)*100) + 1)/”DaysinMonth”;
ENDIF;
/* Calculate FTE using the datefactor */
FTE_Payroll = “FTE_Payroll”->&Assump_Year->”BegBalance” * datefactor;
);
ENDFIX
ENDFIX

EAS copy of a BSO Planning Cube post 11.1.2.2

G’day All,

A very quick and very essbase hacky one today (since we’re all essbase hackers at heart!)

From 11.1.2.2 onwards you can no longer take an copy of a Planning BSO Application through EAS. You can still copy ASO apps and Essbase only BSO apps, but can no longer take a quick snapshot of a Planning app to do some modelling in. The function is greyed out when you right click on the App.
V11.1.2.1
11111 - Copy

V11.1.2.2
11122 Onwards - No Copy

Because that is still quite useful to do, particularly when:

  • testing data changes
  • reviewing historical recalculations so you can see a before and after
  • making sure my code actually works and doesn’t set fire to everything

Below is some Maxl code to get around it. This will copy the outlines, rules, scripts and data across. Note the 8 character limit for both apps and databases.

Create Application ‘TargetAppName’;
Create Database ‘TargetAppName’.’SourceDB1′ as ‘SourceAppName’.’SourceDB1′;
Create Database ‘TargetAppName’.’SourceDB2′ as ‘SourceAppName’.’SourceDB2′;
Create Database ‘TargetAppName’.’SourceDB3′ as ‘SourceAppName’.’SourceDB3′;

Best run from the MAXL editor in EAS.
MAXL

This is a staggeringly inglorious hack, so treat it with caution. It has obviously been removed for a reason – though this may simply be to remove the confusion of planning/essbase apps.

You also need to be aware of your licensing agreement to make sure you’ve got coverage for Essbase only applications.

Finally, and obviously, I strongly recommend running this only in your dev/test environments. But as a great poet once said “there’s no test like production”

Cheers
Pete

ps: I’m kidding. Don’t run this in Production. Seriously. They are Planning apps remember?? That’s why you’ve got LCM. Read the previous post. Sheesh.