OneStream Tip: How to Make People Planning Calculate Faster (Part 1)
OneStream’s People Planning XF MarketPlace solution is a great example of how to combine a relational table to store and calculate data and a cube to analyze and report the results. If you ever used a cube before, you realize the limitations and difficulties in trying to perform basic salary-related calculations in an OLAP database. OneStream’s approach of using a relational table makes it easy to define, process, and track these calculations. However, these calculations can sometimes take a long time, which can frustrate end users.
At a recent implementation, the MorganFranklin team reduced our client’s People Planning calculation time from three hours to just minutes. The client had around 2,000 employees in 14 separate PLP registers. Normal calculation times for each PLP register ranged between 10 and 30 minutes, which meant running the full calc on the master register took around three hours. After applying the following methods, we were able to reduce calculation times for the individual registers to 3 to 20 seconds and the full register to two minutes and 15 seconds.
How did we do it? We went through each component of People Planning and found the best way for it to run. In this three-part blog series, we’ll be focusing on:
Allocation methods let us define calculations that transform input data from the register into calculated results in the Plan data table. For example, if register data may provide a hourly rate and hours per month, an allocation method will calculate the monthly salary by multiplying the hourly rate figure by the hours-per-month figure. A slightly more complex example: Hours are provided on a weekly basis, and the number of working days per month are stored in a cube. In this example, the allocation method will have to perform the following calculations:
Base Salary = Hourly Rate x (Hours per Week / 5) x Days per Month
For this calculation, we will have to write a Dashboard XFBR String rule. We can pass the Hourly Rate and Hours per Week values from the register to the rule, but we will have to get the Days per Month from the cube using a BRApi GetDataCellUsingMemberScript formula. To make things more complicated, maybe we have a different field where we store hourly rates for exempt versus nonexempt employees. In such a case, we will have to add an if statement to evaluate the employee exempt status.
Here’s an example of the XFBR code that does such a calculation:
Let us also consider the possibility that regular exempt employees have their base salary recorded in account 605001, while regular temporary exempt employees have their salaries recorded in a different account (605014), regular nonexempt employees go to 605051, and temporary nonexempt salaries to 605064. To make this happen, we need to separate allocation methods. One for regular exempt employees and one for temporary exempt employees.
Not-so-fast allocation method:
The screenshot above shows one way to do this. We define an IIF statement that calls the PLP_ParamHelper->GetRegValue containing the employee type (in this case Code2). Then, if the employee type is Regular, we call the custom SLM_PLP_ParamHelper->XFBR GetBaseSalary. This method works, but it is slow because it is calling two XFBR rules.
Faster allocation method:
A better approach is to evaluate the employee type directly in the allocation method without calling the PLP_ParamHelper->GetRegValue rule. This saves one pass through the business rule and will speed up the calculation by a few fractions of a second. This may not sound like a lot, but remember that each calculation is performed 12 times for each employee, and since we have four possible employee types (regular exempt, temp exempt, regular nonexempt, and temp nonexempt), we must run this calculation 96,000 times (2,000 employees x 12 months x 12 employee types).
Fastest allocation method:
What if there were a way to cut down on the number of times we run the business rule altogether? This would surely result in further reduction of time. One way to do this is to get rid of the IIF statement entirely. Instead of evaluating each employee, we can create four allocation methods (Base Salary Reg Exempt, Base Salary Reg NonExempt, Base Salary Temp Exempt, and Base Salary Temp NonExempt). Each allocation method calls the same rule but is assigned to a different account. The evaluation of the employee status is not done by a business rule. Instead, it is done by a filter in the Calculation Plan.