palo 2.0

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • This is indeed a good question, from what I can tell the syntax is similar to that of TM1
    and takes the form of

    [source] = [destination]

    eg

    [] = 10 would set every value in a cube equal to 10

    You can use the following symbols (+,/,-,*)

    eg - The example below assumes you have created a new element in the measures dimension of the sales cube in the demo database called GrossProfitPercentage

    ['GrossProfitPercentage'] = ['Gross Profit']/['Cost of Sales']*100


    For maths operations you use the following symbols

    >= (greater than or equal to)
    > (greater than)
    <= (less than or equal to)
    < (less than)
    == equal to
    != not equal to
    <> not equal to
    TRUE - boolean true
    FALSE - foolean false


    You can use a comma to separate elements eg

    ['OpeningBalance','Jan2007'] = ['ClosingBalance','Dec2006']

    Please note that whilst you can write rules like the above you would probably be best performing an operation like this one with the copy function in splasher since that way you wouldn't be continually calculating figures which probably won't change any more.

    You can use functions within the rules eg

    This rule will give you a flag of 1 in the cell if the items make more than 40% gross profit margin
    ['HighMarginItem'] = if(['Gross Profit']/['Cost of Sales']*100>40,1,0)

    You can also restrict the area over which the rule fires by using C: and N: as part of the rule Whereby C refers to consolidated elements and N refers to leaf level (base level) members.

    eg

    [] = N: 50
    [] = C: 1000

    The rules above set every base element to 50 and all consolidations to 1000

    N: and C: can be embedded in rules

    eg

    ['Sales'] = N:['Units'] * ['Price']


    I'm not sure about external cube rules (eg pulling values into one cube from another cube or even if this is possible in the current version)
    Intercube rules are frequently used for this such as showing multiple currencies, whereby values may be lookedup in separate cubes. This is the syntax used in TM1

    eg ['Sales','April'] = ['Units','April'] * DB ('Price',!Region,!Product,'March')

    I also don't know how palo implements accelerators (alea term) or feeders (TM1 term) - these are basically server flags which control when the rule fires for increased performance

    eg If I had a rule such as

    ['Revenue'] = ['Price'] * ['Volume']

    then I would write an accelerator saying ['Volume'] => ['Revenue'] - This ensures that the rule will only fire when there is a volume (since anything times by nothing will be nothing it doesn't make sense to perform the calculation when there is no data)
    Basically accelerators or feeders are used to increase performance and handle cube sparsity.... Hopefully someone from Jedox can shed a little light on how we perform intercube rules and accelerators.

    The post was edited 1 time, last by Hugo ().

  • Thanks Hugo for the rules primer. I have found that if I have two data elements: Sales and Test consolidated to Total :
    Total
    .Sales
    .Test

    Then I save rules
    ['Test'] = ['Sales'] * 2
    ['Sales'] = 101

    In an Excel slice with PALO.DATAC formule, this shows
    Test as 0
    Sales as 101
    Total as 101

    When I enter 20 into Sales cell and re-calc sheet I get:
    Test as 20
    Sales as 101
    Total as 141

    Interesting.. I will do more experiments.

    It seems that there are 2 values held at intersection Sales and that the rule and hard entered values are both stored.

    I look forwared to a lot of interesting developments - Very well done to the Palo team for coming up with this version.It must have been a lot of hard work. THANK YOU
    OLAPmodels
    Multidimensional Model Builder

  • Hi

    Indeed this is interesting, I must admit I didn't try to perform a compound type rule (ie, the value of one rule is based upon the result value of another)...
    I assume the fact that both test and sales were equal to the same value is because the rules engine is naive and doesn't take the order the rules were created into consideration, this can only be consider a bug.
    In TM1 and alea, the order of rules matters, and the first rule present in a list will be calculated followed by subsequent rules.

    All I can tell you at this point is that the rules use a parser based on yacc/bison, and even this may not be true, I've looked at some of source code checked into subversion on sourceforge, however this hasn't been updated for around 5 months.

    In summary then I guess we as a community would like to know about the following from the developers.

    1) How is the ordering of rules performed ?
    2) Is there anything equivalent to stet? i.e. I can we write a rule which works over most of a cube but write a stet rule, so that the rule is ignored for a given element / area.
    3) Are intercube rules (external rule) possible ?
    4) Do you have a concept of accelerators / feeders ? If not how is sparsity dealt with, or do you plan to implement such a concept into the rules engine?

  • Hi,

    I think you (OLAPmodels) made a mistake in the example you wrote here. I tried to recreate it and when I entered 20 into Sales cell and re-calc sheet I get:
    Test as 40
    Sales as 101
    Total as 141

    But otherwise the observations seems to be correct. And it doesn't seem to be possible to get "Test" to be equal 202 no matter the ordering of the two rules. "['Test'] = ['Sales'] * 2" always takes the "base value" of Sales instead of the rule-based value "['Sales'] = 101".