Monday, July 24, 2017

Dimension vs Smart lists - Part 1 - MRC approach

It has been really a while that I posted anything. It was either work or arrival of a sweet angel into our lives, which kept me busy throughout this time. There have been many topics which I want to write about and probably my schedule will allow me to get back into motion.
Let me give you a brief background about of this post. We were implementing a workforce and OPEX solution. The workforce module was an employee based model calculating salary, benefits, etc. and OPEX module had all expenses rolled up to give us P&L. The solution seems to be a usual one but things turned complex while we were almost half way through our implementation when the client brought in a new set of requirements. OPEX cube uses Entity and Location dimensions to track expenses while Workforce cube didn’t use Location dimension. It was after a while that client came back asking for the ability to track workforce expenses by locations.
Being almost half way through the implementation, we had two options –

Either enable location dimension for workforce cube and rework on all the artifacts created so far
Use location dimension as smart list and give planners an ability to tag base location to each employee

With some further brainstorming with the client, we figured out that they just wanted to tag the locations to workforce expenses to carry out allocations down the line and wanted to have workforce expenses tracked by locations. Thus it didn’t make sense to add a whole new dimension, update all artifacts and re-engineer the calculation logic.

Comparing the two concepts – new dimension vs smart lists, a standard dimension would allow easy roll up, slice and dice whereas smart list can be used as data for reporting. Since in our case we wanted to just tag location to employees, it made more sense to use smart lists rather than standard dimension.

For this post, I have come up with a small application to demonstrate what can be achieved with the Smart list in lieu of standard dimension. Here is the dimensionality of the application

I created a smart list which is replica of location dimension and tagged it to account Employee Base

In order to move data between two cubes within an application, we can adopt either of the below approaches:

Map Reporting Cube (MRC)
  • This allows mapping a smart list from source cube to a dimension in the reporting cube
  • Ideal when Smart list entries can change over time. With changing Smart list entries, sync between the source entries and target dimension members is easy
  • Absence of dimension at source or target can be defined and mapping is managed internally by planning system
  • Does a member to member mapping and require source members to be available on target
  • Can be BSO to BSO or BSO to ASO
  • Source and target members should be same
Business rule with @XWRITE
  • Custom rule gives the flexibility to map any source member to any target member
  • Ability to perform calculations or transform data while moving between cubes
  • Can be BSO to BSO ONLY
  • Ideal when Smart list members are fixed and would not change over time. Changing Smart list entries increases overhead to maintain all options in business rule as well as smart list entries
  • Many to one or many to many mapping possible and needs to be defined in business logic

Considering that location dimension would change over time and smart list entries would need an update, it makes more sense to go with MRC option in this scenario. I will also take an example of the @XWRITE approach in future posts.

We designed a form on workforce cube to accept workforce related inputs. This form is to mimic the process where users plan for workforce expenses and tag base location to each employee using smart list. Considering a hypothetical situation, both the employees for Entity 2 are tagged to Location 1  while for Entity 1 each is tagged to a different location.


An MRC is defined to map source members from WKF cube to OPEX cube with following mapping and POV.



After saving data in the employee form, MRC is executed to move data from WKF to OPEX cube. Data gets copied over. Since Entity 2 had both the employee tagged to Location 1,  the sum of both the employees goes to Location 1 and other locations have nothing.


This form is pointed to OPEX cube which does not have employee dimension but has workforce accounts. This is required as MRC does a member to member mapping and requires source accounts to be available on target else the data movement would fail.

This is just an example how the cube size and potential blocks can be reduced by the use of Smart lists. The use of smart lists in lieu of dimensions requires a thorough evaluation of requirements and does not always be a correct choice.

Hope this helps !!!

Sunday, March 29, 2015

Executing .Bat files from Calc Manager

Few days back, I had an opportunity to discuss on capability of Calc Manager to trigger .bat files. A fellow colleague of mine happen to have this requirement and had written a CDF to call a batch script. I was not convinced to write a CDF to perform such activity and believed to have some option using which we can avoid writing CDFs.
Most of us have used Maxl and must be aware of the command ‘Shell’. This command triggers underlying operating system commands from within Maxl shell session. This is our easy way to do what we want. With the introduction of Calc Manager CDFs @CalcMgrExecuteEncryptMaxLFile and @CalcMgrExecuteMaxLEnScript, we now have ability to trigger Maxls from within Calc Manager. All we need to make sure is all such requests are sent to Essbase Server and bin folder under Essbase path is our starting point.
Lets use Oracle sample application Vision and Calc Manager to test out.
  • Create a batch file Test.bat. This file accepts a run time parameter and simply writes it to a log file
  • Create a RTP to accept Version member from a webform
  • Create a business rule which uses RunJava version of the Calc Manager CDF to execute Maxls. We intend to pass the RTP member name as parameter to the batch file which should be written to log file. CDF function requires use of exit keyword if we intend to pass parameters. In this case, RTP member is passed as parameter. We need to enclose it within double quotes to make it act as parameter
  • Lets associate this rule as a menu item to a form from Vision app and select the version to trigger this
  • Since we unchecked the ‘Hide Prompts’ option, it displays the selected member during execution of rule

  • This triggers the Test.bat file and a log file gets generated with the version name written into it
I noticed a peculiar way application was handling the RTPs, it tend to cache the RTP value that was used during first execution. On executing this rule with different version members, it returned the same member name. Though this would get fixed if we restart out Planning services once. Seems to be a potential bug where RTP values are not updated during subsequent executions.
Hope this helps !!!

Thursday, February 19, 2015

Calc Manager - Buggy ? Already ?

While exploring different features under EPM release, I happen to stumble upon an interesting setback in Calc Manager which I believe is a potential bug. 

The issue occurs when we try to write business rules for Essbase applications and deploy them. It all started with me trying to explore how business rules with RTPs can be executed from Essbase. Interestingly, it the script editor which is buggy while the designer mode works perfectly. I used Sample.Basic, which comes with installation to test out my findings.

  • Created a runtime variable PeriodApp of type member which belong to dimension Year from Sample.Basic
  • Created a new business rule 'LNCHTST'. Select the 'Script' mode and type in a simple command to copy data from one period to another. And target is a runtime prompt.

  • When I try to validate this rule, I get a popup asking for RTP value. Bang !!! Script validation throws an error. I could not determine as to what is the issue. I double checked my rule to make sure I did not make any stupid mistake.

I did try a lot of workarounds to see if I am doing anything wrong, but nothing seem to work out. Then I tried creating the same rule using the designer mode of Calc Manager. Rule name LNCH2 using the same variable and script. To my surprise, it validated without any errors. Definitely, there is something fishy with script editor.

I went on deploying both the rules to Essbase to check how they get translated to Essbase scripts.
As per Calc Manager documentation - "When you deploy Essbase business rules with runtime prompts to Essbase, you can use Administration Services, MaxL, or any component that can launch a calc script to launch the business rules. When you deploy these business rules, they are converted to Essbase calc scripts, and the runtime prompt variables are converted to RUNTIMESUBVARS in the Essbase script"

The rules after deployment get converted to RUNTIMESUBVARS, but there is a catch between the two rules. The rule created in script editor did not deploy correctly in Essbase. We can find {PeriodApp} variable written as it is in the script. This would also fail when we try to execute this calc at Essbase level. While the one created using designer mode translates well to replace the RTP with RUNTIMESUBVAR. 

At the same time I wanted to check how the rules look like when they are exported. I opened them in XML Notepad and compared side by side

I just named it into different sections to compare. To my surprise where is Section 3 in the rule which was written using script mode ?? That's the issue probably .

Let me brain storm more into this and see how it can be fixed. At the same time, check if RTPs work fine on Planning. I hope they do !!!