PEL Business Rule Re-use and Pre-defined What If Scenarios

sachatomey — Wed, 30 Apr 2008 21:17:11 GMT

You’ve built a rule set to satisfy an original set of business requirements for calculating budget, and all is good with the world. Then, a change order is raised and approved that requires, for want of a better description, a set of pre-defined what if* scenarios to apply to the calculated budget.

The pre-defined what-if’s are essentially the same business rule calculations with a slight factor or variance applied so it makes sense to want to re-use the existing rules. Not an unreasonable request.

What’s the best way to implement this change request without introducing the burden of duplicated rules and keeping your model clean and well structured?

Background

This is the rule set in question:

These rules utilise an assumption model that contains values such as salary levels, training days, tax assumptions etc and calculate various budget lines based on headcount information collected from a variety of regional cost centres.

The rules are pretty straightforward as the Salary rule outlined below shows:

scope(

[Scenario].[All Members].[Budget],
[Account].[Detail].[Salary Costs],
Descendants([Role].[Role].[All Roles], 0, after),
Descendants([Time].[Quarterly].[Year2008], 0, after),
Descendants([Region].[Region].[Global], 0, after)

) ;

this =

(
(Model([Drivers]),
[Account].[Detail].[Annual Salary],
[Time].[Quarterly].CurrentMember.Ancestor([Time].[Quarterly].[Year]),
[Role].[Role].CurrentMember,
[Region].[Region].CurrentMember,
[Scenario].[All Members].[Budget]) / 4
)
*
(
[Account].[Detail].[Headcount],
[Time].[Quarterly].CurrentMember,
[Role].[Role].CurrentMember,
[Region].[Region].CurrentMember,
[Scenario].[All Members].CurrentMember
);

end scope;

Essentially, for each quarter, for each role within each region we are multiplying the appropriate annual salary from the assumption model (divided by 4 for quarters) by the appropriate headcount to give us the budget salary figure.

New Requirement

Our change order requires a view of the budget as if we were to increase or decrease the headcount for each role within each region by 30%. Our new scenario dimension will look like this:

The last two members have been added to cope with this new requirement – you could argue that these members could exist in a separate ‘what-if’ style dimension that could then be applied to any of the scenarios independently. I actually prefer that approach, but for simplicity let’s just limit this explanation to the scenario dimension.

Okay, we have our new scenario members, we now need to wire up some rules to calculate these alternate budgets.

Design

Our first problem is how we can make the existing rules generic across budget scenarios. We have the budget scenario factors (a +30% and a -30%) to work in somewhere. We could hard-code these values into the rules but that would require a rule set per budget scenario thus duplicating all rules. Nasty.

We could store the factors in an assumption model against the appropriate budget scenario, this could then be referenced within the rule. That would potentially allow us to re-use the business rules. However, I’m not keen on this approach at all as I feel that the budget factors are fixed, scalar values and to store them in an assumption model is overkill and would potentially require much more implementation effort.

So, what option(s) are we left with? Not many, in this situation I would be tempted to create a member property on the Scenario dimension that held the factor against the appropriate member. The business rules could then simply (yeah right, see later!) reference the member property and apply the factor. So, in the case of the main Budget scenario we would have a factor of 1, for Budget +30% Headcount we would store a factor of 1.3 and 0.7 would be set against Budget -30% Headcount.

So, on the face of it you can then update each rule to multiply every reference of Headcount by the appropriate factor held in a member property.

However, there is a problem. That problem lies with the PEL function ‘Properties’. Unlike the MDX equivalent, the PEL version always returns a string, irrespective of the data type you assign to the member property itself. You cannot multiply by a string value and PEL does not contain any data type conversion functions to overcome this issue.

Implementation

The workaround: Use a Native MDX implementation. That sounds worse than it is; I’m not suggesting that you re-write all the business rules in MDX, although that is certainly an option.

Alternatively, you can add a single MDX business rule to the top of the rule set that re-calculates Headcount, using the value of the member property:

scope

(

{[Scenario].[All Members].[Scenario].&[402],[Scenario].[All Members].[Scenario].&[501]},
Descendants([Time].[Quarterly].[Year].&[2008], 0, AFTER),
[Account].[Detail].[Level 02].&[5001],
Descendants([Region].[Region].[Level 02].&[5201], 0, AFTER),
Descendants([Role].[Role].[Level 02].&[101], 0, AFTER)

);

([Measures].[Value])=

(
[Scenario].[All Members].[Budget],
[Time].[Quarterly].CurrentMember,
[Account].[Detail].[Headcount],
[Region].[Region].CurrentMember,
[Role].[Role].CurrentMember
)
*
[Scenario].[All Members].CurrentMember.Properties('OffsetPercent');

end scope;

Apart from the fact the MDX rule is very PEL like, notice the last line of the rule. Here the headcount for each role, region and quarter is multiplied by the Scenario member property OffsetPercent.

With this in place, the subsequent rules will calculate the budget scenarios based on the appropriate revised headcount value. (Due to the natural solve order of the rules in the rule set).

The final step is to update each of the rules scope to include each of the budget scenarios (leaving the main bulk of the rule in place, untouched).

Here’s the updated version of the Salary rule we looked at earlier (Modification in green)

scope

(
{
[Scenario].[All Members].[Budget],
[Scenario].[All Members].[BudgetPlus30PcntHCount],
[Scenario].[All Members].[BudgetMinus30PcntHCount]
},
[Account].[Detail].[Salary Costs],
Descendants([Role].[Role].[All Roles], 0, after),
Descendants([Time].[Quarterly].[Year2008], 0, after),
Descendants([Region].[Region].[Global], 0, after)
) ;

this =

(
(Model([Drivers]),
[Account].[Detail].[Annual Salary],
[Time].[Quarterly].CurrentMember.Ancestor([Time].[Quarterly].[Year]),
[Role].[Role].CurrentMember,
[Region].[Region].CurrentMember) / 4
)

*
(
[Account].[Detail].[Headcount],
[Time].[Quarterly].CurrentMember,
[Role].[Role].CurrentMember,
[Region].[Region].CurrentMember
);

end scope;

For completeness, our modified rules set will look like this:

Notice the new (MDX) rule ‘Headcount’, this will be calculated first, for each of the budget scenarios ensuring the subsequent rules use the appropriately factored headcount figure.

The support for Business Rule re-use is limited in PPS-P v1 but as this example hopefully illustrates, with a little thought and application, re-use is occasionally achievable. I hope there will be better support for re-use in future versions !

---------------------------------------

* Generally, what-if’s are used dynamically; you change a few numbers in a data entry worksheet, re-calculate the sheet and view the results, repeating the process as necessary. This is a great feature and really quite powerful, however, there are some situations where pre-defined what-if’s are required to easily and frequently show standard what-if scenarios.

Search results matching tags 'MDX', 'Business Intelligence', and 'PEL'

PEL Business Rule Re-use and Pre-defined What If Scenarios