Saturday 12 August 2017

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!

Saturday 5 August 2017

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.


And then there's the Waterfall Chart in PBCS

Ever found yourself looking to create a Waterfall chart in PBCS Dashboards only to be left disappointed?  Sure, you can have this done when ...