One of the key reasons that people use Jedox is for its planning capability. And any planning system worth its salt has a rules engine built in. A rules engine is a real time calculation algorithm; think of it like excel formulas inside a multi-dimensional environment. This is critical for planning as it allows planners to tweak high level drivers and change result sets down at the base level.

Common uses for rules in a Jedox model include:

• Payroll on-cost calculations
• Internal charge allocations
• FX Rates (especially around multiple eg spot/avg rates occurring at different parts of the model simultaneously)
• Project Costing labour from GL Cost Centres down to a Projects Model.

It also facilitates what–if scenarios: for example, what happens to our SKU (Stock Keeping Unit) level margins  if the product-labour mix on a specific product group changes 5% next quarter? This type of analysis is difficult to achieve in excel with decent sized datasets; and clunky in a relational environment (and certainly not a job for your garden-variety business analyst). With rules there is no pre-processing: the results are instantly available when the user changes the inputs to the formulas.

Rules in Jedox are in-memory and depending on the type of the formulas you create, calculation intensity can be high. So it is always important that you start with a small area and expand out from there. This will help you debug your rules effectively and monitor memory and cpu usage on your calcs.

A general maxim for rules: if the data is volatile (ie the input data to the rules can be changed anytime) then rule calculation is the way to go. If the data is static (ie changed at predefined points) then you should use ETL to calculate your formulas. The bigger the dataset, the more ‘fine tuning’ you need to do to your rules to ensure performance.

An additional note on real-time formula calculation: Jedox gives you options. As well as rules, you have php, available through web templates (php Web Macros) or via Supervision Server. More on that in a later post.

To help illustrate the concept of a rules based model, we need to tweak the Bikers database.

Download the files here.

• Add a text attribute called “Allocation” to the Products dimension.
• Add an element in the Measures dimension called “Allocated Expenses”
• Open up the Rules Editor on the Orders2 cube
• Click on the New button
• Copy the rule and paste into the top box, labelled “Rule”.

['Measures':'Allocated Expenses'] = 

IF((!'Products') == "All Products", STET(), 

       IF(PALO.EPARENT("Biker", "Products", !'Products', 1) == "All Products", STET(), 

       [['Measures':'Units']] 
        / 
      PALO.DATA("Biker", "Orders2", !'Years', !'Months', PALO.EPARENT("Biker", "Products", PALO.EPARENT("Biker",        "Products", !'Products', 1), 1), "All Customers", "All Channels", !'Datatypes', "Units") 
* 
PALO.DATA("Biker", "P_L", !'Datatypes', !'Years', !'Months', PALO.DATA("Biker", "#_Products", "Allocation", PALO.EPARENT("Biker", "Products", PALO.EPARENT("Biker", "Products", !'Products', 1), 1)), "Other operating expenses")
)
)

• Click the parse button (the little tick on the right side of the dialog). This will ensure that the rule is syntactically correct. Click OK then Close (to close the Rule Editor).
• Open up the spreadsheet. Type into the Allocation Attribute the values as you can see in the screen shot below. The easiest way is to double click on the cell to bring up the String Editor. Make sure your spelling is exact. This step is matching Cost Centres (or Clients) to Product Groups so the allocation can take place.

Once you have done this, you should see allocated values being populated against the individual product SKU’s. The basis for allocation in this case is the SKU’s unit proportion of total units for that product group. You can, however,  base your allocation on anything you like: revenue split, headcount, fte, floorspace %, etc.

By playing with changing the GL Allocation value by client, or the mapping on the attribute cube, you can see the allocation instantly changing at the SKU level.