Dynamic cube rule

Hi there,

Was wondering if anyone had any advice on how to approach the follow in a cube rule.

I am needing to apply an additional charge to accounts based on a flag (stored in measure as 1 / 0). The measure has a parent hierarchy named 'Extra Charge' that will have additional elements added. Each child element has a $ value stored against it for account "-".

I need the cube rule to apply the $ value to each account when the flag is enabled (1 written to measure).

So far, I've approached this with the syntax:

[Measure:'Extra Charge] * [Measure:'Extra Charge, Account:'-']

This applies all the extra charge amounts to the account if the flag = 1 for any of the charges. Ideally, this would only apply the charge for the charges that are = 1.

Thanks,

Leanne

Find more posts tagged with

Infor EPM Discussions

Comments

martin-reischl

Hi Leanne,

>Ideally, this would only apply the charge for the charges that are = 1

This is possible with an if statement, taking your example:

if([Measure:'Extra Charge', Account:'-'] = 1, [Measure:'Extra Charge'] , #na)

However - the if will make the rule much slower than if you just use your original rule, multiplying with 0 where the flag is 0. So I recommend to stay with your rule.

(By the way, I hope the flag is #na, empty, instead of physical 0. Physical 0 will consume memory and performance same as any other value, while an empty cell will not).

Best Regards

Martin

Attachment_4356.zip

Nick Gladkov

I would also consider if that should be rule or can be offline process that is executed whenever the flag is enabled.

In general, what you typically do is the multiplication of a flag (0/1) by the charge value. So formula will be something like [Flag] * [Value] and as you don't define account in the coordinates, it will be applied to all accounts/charges.

P.S.: for performance reasons, you should put flag first and the amount second. This way rule will be proceeding with calculations only with areas with non-empty flag, so will significantly faster (rather than taking value which is always there and then applying flag which is sometimes there)

Mike Potashnick

Nick, That is very interesting. Is this an updated way of effectively managing sparcity. This suggests that this is how you replace an accelerator. Would this work if 0 values were #NA. Then the Rule could be the other way round, especially if another rule is dependent on there being a value or not?

David Swinburne

Hi Mike,

A few comments based on my understanding of the OLAP Server Prefetching Rules Engine when processing a [Flag]*[Value] formula.

The OLAP Server will parse the formula and it will identify two references [flag] and [value]; each reference will have associated with it the number of populated data points or tuples in the request context
The OLAP Server will then make a decision about which of the references are primary references and use the tuples retrieved (for the primary references) to drive the calculation by constraining the calculation area of the request context using these values; i.e. only calculate results where these values are present.
In the case of an [a]*[b] formula both values are normally primary references
In this case (two primary references for a [a]*[b] formula) the most efficient way to then structure this rule is put the reference with the least number of tuples first as this will constrain the tuples considered in the second; this is a rule design decision, the OLAP Server does not make this decision.
So, if the [flag] reference has less tuples than the [value] reference in the context then [flag]*[value] is more efficient.
The number of tuples in any reference will depend on the data distribution in the cube and the context of the request and so the ultimate arbitrator of operand order are the request use cases within the application and a review of the output in Rule Debug where the number of tuples is detailed for each reference.

Below is a Rule Debug output from a request on the Sales cube in the sample application for the following rule.

B:[CURRTYPE:'LC', PROFIT:'Sales Deduction']=[PROFIT:'Revenue']*[PROFIT:'Sales Deduction in percent']

(#488,0) Rule: SALES:17 Cells: 242 1769040 1 1477 Time: 47 0 ms
Primary required missing was active.
Rule flags: | RF_BaseCells | RF_Pull | RF_TRIEJOIN | RF_CONJUNCTIVE_SWEEP
Rule execution flags: | RP_OPTIMIZEDREFERENCES
Param vector count: 1235
B:[CURRTYPE:'LC', PROFIT:'Sales Deduction']=[PROFIT:'Revenue']*[PROFIT:'Sales Deduction in percent'];
Reference (0): 1210 I P 127 [PROFIT:'Revenue']
Reference (1): 267 I P 127 [PROFIT:'Sales Deduction in percent']
Query run #0 Subquery #0 References: {0,1}
Rule area #0 cells: 1769040 References: {0,1}

In this example it is more efficient to structure the formula as

[PROFIT:'Sales Deduction in percent'] *[PROFIT:'Revenue']

As [PROFIT:'Sales Deduction in percent'] has 267 tuples and [PROFIT:'Revenue'] 1210 tuples.

In reality this rule is really quick 0ms-15ms calculation time so it would probably not have a big impact.

The OLAP Server will consider 0 as a data point but not #NA

Regards

Dave

Copyright © 2025 Infor. All rights reserved.