Data Phasing and Allocation

Today, I'd like to discuss about techniques I've used to implement rules for cost allocation and data phasing.  Sticking to the "no hard-coding" mantra, here are some of the methods that were used in my PBCS projects.

The examples in this blog rely on a PBCS application with the following dimensions and members:

• Entity
• Cost Centre
• Account
• Measure
• Period
• Scenario
• Version
• Cycle
• Cycle_Line

All the dimensions contain members you would usually find in any PBCS application.  The exceptions are in the Measure, Cycle, and Cycle_Line dimensions which I'll discuss in more detail in sections below.

Phasing of Plan Data

Introduction

More often than not, you'd find yourself getting requests to build planning models that is quick to prepare and the most requested function I found has been the phasing of the budget or forecast data in the outer years based on trends derived from Actuals.  In this instance, planners would have entered their annual planned numbers and will rely on the business rule to phase their plans across the months for the outer years based on historical trends that he/she has selected.  This is to ensure that the plans relfect the actual trend.  One such implementation of mine, the client has requested for a phasing model that allows them to assign one of the following phasing methods to each of the account that they wish to phase:

• Last Year Actual
• Last 2 Years Actual Average
• Last 3 Years Actual Average

Here's a sample of how the phasing works:

In the example above, the Annual Amount of $20,000 for "Acc001" in FY19 is phased based on "Last Year's Actual" while "Acc004" is using the "Last 2-Years Actual Average" as the phasing basis (third row of "Acc004").  So, with "Acc001" the calculation is simply taking the "Amount to be Phased" and then multiplying with the monthly last year actual number for "Acc001" before being divided by the total last year actual amount for "Acc001".  The formula is shown below:

While "Acc004" uses the Average of Last 2 Years' Actual to phase the annual data.

Here's what I did:

Tagging of Phasing Method

A webform was created to expose the relevant accounts that are subjected to the phasing logic with a measure (associated with a Phasing_Method smartlist) displayed in the column.  The Phasing_Method smartlist contains the 3 methods mentioned above.  This form provides the users the flexibility to vary the phasing method by account.

Another form was defined to capture the annual planned amount and this amount is captured against the "BegBalance" period member.

The Rule

The phasing of the data is done through a business rule where it will evaluate the phasing method associated to each of the accounts and then apply the necessary logic to spread the annual plan data across the periods using the user-selected basis.  Here's a snapshot of the business rule:

Inter & Intra-Company Cost Allocation

Introduction

Another use case for the above implementation is for cost allocation.  I realised that I have written similar post about cost allocation when discussing about reverse-mapping techniques.  You can read the post here.  In this blog, I have expanded the description to include some examples to further illustrate how the allocation model will work.  So, read on.

In building such a model for one of my clients, I was determined to build them one that they have full control over how the allocation is performed.  By this, I mean to develop one that allows the users to determine the source of costs to be allocated and the targets for the allocated costs.  The model will also accept the allocation ratio for the source/target combination.  The spreadsheet below illustrates the allocation of costs from Entity A Cost Centre 001 to Entity A Cost Centre 002, 003 & 004.

And here's a sample of the Inter-Company allocation where I will allocate part of the cost of Entity A to entities B, C, & D.  Note that 40% of the cost from Entity A is retained within the same entity.

Now, let's examine the building blocks for this model:

Dimensions & Members

As mentioned earlier, I'll focus on the Measure, Cycle, and Cycle_Line dimensions in this section.  T

Measure Dimension

The main function of this dimension is to enable us to further break an account down further to delineate the origin of a piece of data.  Consider the following hierarchy in the Measure dimension:

• Measure
• Total_PL
• Total_PL_Before_Alloc
• Loaded_Amount
• Direct_Amount
• Phased_Amount
• Workforce_Amount
• Project_Amount
• Allocation_Amount
• Alloc_In
• Alloc_Out
• Allocation_Drivers
• Phasing_Method
• Cycle_Description
• Source_Entity
• Source_Cost_Centre
• Source_Account
• Target_Entity
• Target_Cost_Centre
• Target_Account
• Alloc_Ratio

The "Loaded_Amount" member is used to store the data that is loaded from external data sources (e.g. Actuals from ERP).  "Direct_Amount" member is an input member capturing inputs from users during the planning process while the "Workforce_Amount" and "Project_Amount" members will store that data coming from the Workforce and Project models respectively.  The "Allocation_Amount" member which is further divided into "Alloc_In" and "Alloc_Out" will be used in the Allocation model.  All these members will roll-up to "Total_PL" to arrive at the total P&L number.

Extending on the example discussed in the introduction, I am adding the Measure dimension to the Intra-Company allocation table:

The measure "Total_PL_Before_Alloc" is a roll-up of amounts from "Loaded_Amount", "Direct_Amount", Workforce_Amount", and "Project_Amount".  The example above shows that the last 3 members ("Direct_Amount", Workforce_Amount", and "Project_Amount") contain values of $400, $300, and $300, and therefore,"Total_PL_Before_Alloc" being the parent will hold the sum of $1,000.  We will use this roll up as our source for allocation.  

As a result of the allocation process, the model will allocate to the target Cost Centres (002, 003, & 004) based upon the user-defined ratios and the allocated numbers are stored in the "Alloc_In" measure.  The whole amount from the source "Total_PL_Before_Alloc" will also be reversed and posted to the source Entity/Cost Centre/Account combination and the reversal (-$1,000 in this example) is stored in the "Alloc_Out" measure.  So, when reported at "Total_PL" level, the Overheads from the source Entity/Cost Centre" combination (Entity A, Cost Centre 001) will be fully allocated to the respective targets because the sum of "Total_PL_Before_Alloc" ($1,000) and "Allocation_Amount" (-$1,000) will result in zero.

Cycle & Cycle_Line Dimension

These dimensions allow users to define different allocation rules (in Cycle) and multiple lines per allocation rule (Cycle_Line).  The members in both the dimensions are just running sequential numbers.

• Cycle
• C001
• C002
• C003
• ...

• Cycle_Line
• CL001
• CL002
• CL003
• ...

Source/Target & Ratios Definition

As mentioned in the introduction, this model serves to allocate cost within the company as well as inter-company allocation.  The mapping table that I've defined via a webform capture not only the source and target accounts, but also the source/target Entity & Cost Centre combination as well.  Here's an illustration of the webform defined to capture the source/target combinations:

The top part of the diagram shows the form to define the Allocation Cycle while the bottom part of the diagram shows the detail allocation lines for each allocation cycle.  Notice that both forms are capturing the combinations against the NA members for Entity, Cost Centre, and Account.  You might be wondering how do I then capture the different combinations of source and target Entity, Cost Centre, and Account combinations?  The answer lies in Smartlist (see next section for details on smartlists).

Columns 3 through to 8 of the Allocation Line form are measures with smartlists tagged to each of the member.  This enables users to then pick the source/target entity, cost centre, and account of their choice from their associated smartlist.  Column 9 is an input column allowing users to enter the allocation ratio for each allocation line.  The above definition is  reflective of the Intra-Company Allocation example that I have mentioned in the introduction earlier.

Smartlists

The following heirarchy-driven smartlists are required to support the allocation model.  The smartlists will be assigned to Allocation_Driver members in the Measure dimension:

• Source Entity
• Source Cost Centre
• Source Account

If you have Target Entities, Cost Centres, or Accounts that are different from the source, you'd need to also define Target smartlists from them:

• Target Entity
• Target Cost Centre
• Target Account

A sample of a hierarchy-driven smartlist is shown below.  Please refer to my blog on Hierarchy-Driven Smartlists here for more in-depth description on the process to build such smartlist.

The Business Rule

To code the allocation logic, I have decided to use the @XWRITE function.  The cool thing about the model is that the rule is able to create the necessary target blocks based on what the users have selected for their source/target combinations via the webform created.  This helps minimise any chance of a block explosion where blocks are created unnecessarily which will in turn adversely affect the performance of the cube in general.  Keep a look out for my post on block creation which is still a "work in progress".  Also, the fact that all the source/target combinations are captured against Entity_NA, Cost_Centre_NA, and Account_NA means that the rule will rule faster as it does not need to cycle through all the different entity, cost centre, and account base members.  My rule is fixed to the NA members only and using @XWRITE to create the necessary blocks and perform the required allocation.  Here's the rule:

Notice that I've used the @XWRITE function to both create the blocks and perform the allocation.  The first part of the rule posts the reversal amount to the Source Entity, Cost Centre, and Account combination and to the "Alloc_Out" measure.  The value of "Total_PL_Before_Alloc" is multiplied by -1 before posting the results.

The second section of the rule deals with the allocation of the "Total_PL_Before_Alloc" amount from the Source Entity, Cost Centre, and Account combination to the Target Entity, Cost Centre, and Account combination and to the "Alloc_In" measure.  The value of "Total_PL_Before_Alloc" is multiplied by "Alloc_Ratio" before posting the results to the targets.

That's it folks for this blog.  Hope you've enjoyed reading this as much I have enjoyed writing it.  Do leave a comment and let me know what else you want me to cover.

Until then, sayonara for now.

To Hard-Code or Not To-Hard-Code - The Sequel

Keeping to the theme from my previous post, I thought of sharing more tips and tricks on how to avoid hard coding your business rules.  In this post, I'll share with you the following techniques that I have used in the various projects that I've worked with previously:

Reverse-mapping using Smartlist

This is essentially relying on dimension-based smartlists to resolve to the intended member.  This option gives the users the ability to maintain the mappings via a webform without having the underlying business rules hard-coding to any target members.  Use case for this method can be applied to any allocation-type business rules.  Details of this techniques can be found in this post and this one.

User Variables & Planning Expressions

The User Variables feature in Planning can be a useful tool to give users more control over what they wish to view in a webform.  Forms configured to use these variables will give users the ability to dynamically select a member at runtime (e.g. a parent member of a hierarchy) which could then be used to display the descendants of the selected parent.  To take it one step further, we can use the user variables in business rules as well.  This helps us to limit the calculation to only the selected member(s), thus, making the rule run faster.

Let's see how this can be achieved in the business rule.  The trick here is to use a Planning Expression to resolve to the selected member of the User Variable.  The Planing Expression in question here is the [[PlanningFunctions.getUserVarValue("User_Variable")]].


Consider this.  I have defined the following User Variables:

And I wish to limit my calculation scope to level 0 members of the selected Entity and Cost Centre parents.  Here's how I would code my rule:

The "PlanningFunction.getUserVarValue" Planning Expression will resolve to the base members of  entity and cost centre that the user has selected at runtime.  You could also use other Planning Expressions to further your quest in avoiding any hard-coding in your rules.  Here are some other examples of Planning Expressions that you could possible use:

Periods

The following is a list of Period-related Planning Expressions:

I have personally not used any of the above Planning Expressions except for [[Period("FIRST_PERIOD")]] & [[Period("LAST_PERIOD")]].  Here's an example of how I've used them in my rule to control how I get the prior period value for an account.

Scenarios

Here's a list of Planning Expressions linked to Scenarios:

The last I used the above expressions, they require the Scenario Name to be a string (i.e. hard-coded) but the latest reference guide from Oracle shows that the expressions now accepts variables such as Runtime Prompts (RTP).  Try it out and let me know we can now use variables.  Would be awesome if this is true.

Here's an example code I've lifted from the Oracle's guide.  Looks like it accepts RTPs.

That's all folks for this blogpost.  Let me know if you find this post useful and any other feedback is also welcomed.  Thank you and see you in my next post.

To hard-code or not to hard-code?

I'm not a fan of hard-coding my business rules as I find that more often than not, those hard-coded beasts will turn around and bite you.  You'd struggle to remember which hard-coded rules need updating when new hierarchies are added to your planning application.  Sure, hard-coded scripts tend to be easier to read but I'd still steer away from having hard-coding where possible.

Enough said, let's get down to business... This post, I'd like to share with you a technique I often used to resolve an assumption that is set globally for a group of common parent members (e.g. Entities).  Normally, one would create an "NA" member to hold the global assumptions for these members as shown below:

I could write a rule that hard-codes to ANZGROUP_NA to resolve the global assumption like this:

Or, I could create a rule that will dynamically resolve to ANZGROUP_NA. 

The code will resolve to ANZGROUP_NA by first resolving to the ANZGROUP node using the @ANCEST("Entity",2) function and followed by concatenating the resulting member with a suffix of "_NA".  The @MEMBER will then convert the concatenated string to a member before used in the crossdim with "Annual_Salary".  The series of functions will result in the following crossdim:

"Monthly Salary" = "Annual_Salary"->"ANZGROUP_NA" / 12;

This of course, is a very simple example with the entity dimension having just 3 generations (Entity, Group and Individual Level 0 entities) and you might ask what's the big deal with hard-coding the rule to point to ANZGROUP_NA?  You're right, this isn't a big deal to have it hard-coded in this instance as the chances of you adding another NA member in this hierarchy is quite minimal.  However, it will be a different conversation if we had an entity structure like the one shown below:

Each of the country entities (i.e. AU and NZ) having their respective NA members to capture their country-specific assumptions.  The hard-coded rule needs to be updated to evaluate if the current entity being calculated is part of the AU or the NZ hierarchy and then apply the correct country-specific assumption to the rule.

Any subsequent addition to the entity dimension (i.e. adding of new countries), will require the rule to be amended to also consider the newly added countries.  Now, compare it with the non-hard coded rule.  You would update the code to use Generation 3 that will resolve to either "AU" or "NZ" before concatenating with the "_NA" suffix.

With just 3 lines of code, the rule will resolve to AU_NA for all entities under the AU hierarchy and to NZ_NA for all NZ entities.

With the above code, any subsequent addition of siblings to the AU or NZ nodes will NOT require you to amend your rules as it will work perfectly as long as you maintain the same hierarchy structure as we have for AU or NZ.

There! A short and sweet post.  Hope you found it useful and keep a look out for more tips and tricks on how to avoid hard-coding in your rules.  Till then, adios!

Hierarchy-Driven SmartLists: And then there were two....

Apa khabar (that's "how are you" in Malay in case you're wondering)?  Hope you've enjoyed reading my maiden blogpost (Reverse Mapping using Hierarchy-Driven SmartLists).  Keeping to the theme, thought I'd share another use of a Hierarchy-Driver Smartlist; this time in an allocation process.

More often than not, you'll find yourself building some sort of an allocation logic for your planning application and as you know, there's more than one way to skin a cat.  My latest go-to method is using @XWRITE in conjunction with a Hierarchy-Driven Smartlist.  Not only does it help in addressing the ever-annoying block creation issue (we had our fair share of rants on this matter don't we, Pete?), using a SmartList provides the ability to avoid any kind of hard-coding in the business rules.

Interested?  Read on...

Background

A client of mine has this 4-tiered allocation process that they want implemented in their PBCS application.  Our solution uses a mapping table established to define the relationships between the following sources and targets:

• Entity
• Profit Centre
• Function Code
• Account
• Allocation %

The UV_Assessment_Cycle (in the POV) allows the client to maintain multiple cycles (or tiers) of allocation while the rows provides the ability to have multiple lines of allocation per cycle.  For instance, L001 defines a 100% allocation of cost

• From
• Entity: 4650
• Profit Centre: RAM RNY
• Function Code: 590 - COMPUTER SYSTEMS
• Account: OH2
• To
• Entity: 4550
• Profit Centre: MGR RAM
• Function Code: 590 - COMPUTER SYSTEMS
• Account: Overheads Pooling

Having this mapping table frees us from having to hard-code our allocation business rules.

The SmartLists
Define a hierarchy-driven smartlist for each of the following:

• Sender/Receiver Entity

• Sender/Receiver Profit Centre

• Sender/Receiver Function Code

• Sender Account

• Receiver Account

Accounts / Measures
Create the following accounts or measures and attach the above smartlists:

• Sender_Entity - SL_Ent
• Sender_Account - Cycle_Sender_Acc
• Sender_Profit_Centre - SL_PC
• Sender_Function_Code - SL_FC
• Receiver_Entity - SL_Ent
• Receiver_Account - Cycle_Receiver_Acc
• Receiver_Profit_Centre - SL_PC
• Receiver_Function_Code - SL_FC

Business Rule

The business rule will cycle through all the Assessment Cycles and Assessment Cycle Lines to the post a negative entry to the sender Entity/Profit Centre/Function Code/Account combinations and posts a positive entry to the receiver combinations.  A sample of the results is illustrated in the table below:

This is the full allocation code:

Say what?  Okay, okay.  Let's examine the section where we used the Hierarchy-Driven Smartists to first create the required block and then post the allocated numbers.  We'll start with the posting to the source combinations:

Notice that the rule uses the XWRITE function to post the value from the Sender Entity, Profit Centre, Function Code, and SenderAccount combination (section 1) to the Sender Entity, Profit Centre, Function Code, and Receiver Account combination (section 2).  What's cool about this is that the XWRITE will first create the block for the Sender Entity, Profit Centre, Function Code, and Receiver Account combination if it does not exist before the posting happens.

Also note that we have used the method of concatenating "HSP_ID_" with the value from the Hierarchy-Driven SmartList to resolve to the intended member. 

Here's the code snippet for the receiving entry and with similar method used when pushing the Sender Entry:

The last part of the rule applies the allocation ratio to both the sender and receiver entries and flips the sign for the sender entry:

Recap
This solution allows users to define their allocation mapping without having to rely on a hard-coded business rule (all thanks to the Hierarchy-Driven Smartlists).  The rule will cycle through 2 dimensions (i.e. the Assessment Cycle and the Assessment Cycle lines) when allocating costs between sender and receiver Entity, Profit Centre, Function Code, and Account combinations.  An example of the allocation result is illustrated in the table below:

Hope you have enjoyed this blog and stay tuned for my next blogpost.  Till then, terima kasih!

Reverse Mapping using Hierarchy-Driven Smartlists

This is my maiden post as a blogger and here goes!

Credit goes to Peter Nitschke and Celvin Kattookaran for inspiring me to writing this post.  I've been involved in the EPM world since 2004 primarily in the areas of Essbase and Planning and I must say, the technology has evolved so much as to what I was used to back in the days.

Today, I would like to share with you one such feature that I have used in a project with my client.  Have you ever need to push data between cubes in PBCS where the source and target cubes maintain different account hierarchy levels?  If so, read on.

There are many ways to pushing data between cubes at differing levels and would require some sort of mapping to be in place.  My go-to method has always been using partitions (with pretty much a hard-coded mapping) when using on-premise version to using Data Maps with Smartlist mapping in PBCS.  My problem is that the account mapping is needed for a just handful of accounts where the source cube is maintained at 1 level higher than the ones in the target cube.  The solution before this was to have a form listing all the source accounts in the rows and a measure linked to a target account smartlist in the column.  Users will then have to manually establish the mapping for all the source accounts (a nominated lower level account for some and the rest will be mapped to itself).  This is not a very good solution as it requires maintenance to this mapping table when new accounts are added to the application.

So, having read this blogpost by Celvin Kattookaran about hierarchy-driven smartlists, it gave us an idea to apply this method to reduce the need to maintain the mapping table.  The post reveals that there is a hidden Alias Table (called SLAliases) automatically created in Essbase to store all the HSP_IDs for every member created in the Essbase app.  This alias table is used as the Entry ID in a hierarchy-driven Smartlist.

The member attached to this smartlist will store the HSP_ID of the selected entry.  For instance, if account A302600 is selected by the user, the member value stored will be 59422.  So, using a function to concatenate "HSP_ID_" with the member value will return us the account member.  In the example below, the "Sender_Account" measure is attached to the above smartlist and will store the HSP_ID of the selected account (e.g. 59422).  This example will return a value of "HSP_ID_59422" and will in turn resolve to the account member "A302600".

Taking Pete's bright idea to reverse the process, I am now able to automatically populate an account mapping table using a hierarchy-driven smartlist.  Here's what I've done to allow the client to manually maintain account mappings that are one level deeper in the target cube for a handful of accounts while having PBCS automatically populate the one-to-one account mapping for the rest of the accounts.

The application was created with the following dimensions.

1. Measure
2. Period
3. Account
4. Entity
5. Scenario
6. Version
7. Year

Create a hierarchy in the Account dimension
This hierarchy should contain all the accounts required to map to the target cube:

Create a Hierarchy-driven Smartlist
Create a smartlist based on an account hierarchy that you have defined in the dimension:

Create a measure to store the account mapping
The first measure is opened for input while the latter is a dynamic-calc member that derives its value from the input measure so that the input value persists in any possible combination.  Both measures are linked to the above smartlist:

Create a form
This form is used to capture the manual account mappings and display the auto-populated mappings for the remaining accounts.  The form is configured to have all the Source Accounts listed in rows while the column contains the 2 measures.  Note the column on the right contains the dynamically-derived account mapping based on the inputs in the first column.  This is to ensure that the values entered persist across all possible combinations, thus, facilitating in the mapping process using a Data Map.

Create a business rule
This rule will populate the HSP_IDs of the accounts to the "COA_Mapping_PL_FinRPT_Entry" measure.

The rule excludes all the accounts where their mappings are manually-maintained.  The remaining accounts are populated with the HSP_ID derived from their aliases in the SLAliases alias table.  As mentioned earlier, the SLAliases table contains the HSP_ID for each and every member that are created in the app.  The table below illustrates what the SLAliases table may contain:

The rule retrieves the Alias for each account from the SLAliases table and strips off the "HSP_ID_" prefix leaving just the numerical values of the alias and will be assigned to the "COA_Mapping_PL_FinRPT_Entry" measure.  Since this measure is linked to the smartlist we have created earlier, the numerical value assigned to this measure will resolve to the relevant entries in the linked smartlist.  The table below illustrates the process:

End Result
The one-to-one account mappings (bottom part of the form - in read only mode) are automatically populated once the above business rule is run.

Hope this post is useful and leave a comment if you like it.  Thanks.