Section 6.8.7 - Processing of the rules (how do I selectively modify consolidation facts?)

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

    • Section 6.8.7 - Processing of the rules (how do I selectively modify consolidation facts?)

      Hello everyone,

      I've created the following rule:
      ['e_element1','f_element1'] = C: IF(PALO.ECHILDCOUNT("db","dim_1",!'dim_1') == 0,
      PALO.DATA("db","cube_2",!'dim_1',"e_element1") * ['e_element2','f_element1'],

      This works fine for the nodes that I'm interested in. Consolidation facts that match ['e_element2', 'f_element1'] where !'dim_1' is a base element in the "dim_1" dimension.

      My problem is that I was expecting higher level consolidations in the "dim_1" dimension hierarchy to aggregate these values.
      The higher level consolidations are all zero after I enable this rule.

      (I've tried both CONTINUE() and STET(), but both produce the same result, 0's for all higher level consolidations.)

      If disable the rule, spraying works fine. The values are written all the way down to the lowest level consolidations.

      If I enable the rule, spraying work fine above the consolidations affected by the rule.

      I was thinking I could write a rule like this:

      ['e_element1','f_element1'] = C: IF(PALO.ECHILDCOUNT("db","dim_1",!'dim_1') == 0,
      PALO.DATA("db","cube_2",!'dim_1',"e_element1") * ['e_element2','f_element1'],

      But that just seems wasteful (not to mention, the rules processor doesn't appear to like PALO.DATA.SUM().)

      In the worst case, I will reprocess my data to get rid of the dependency on consolidation (I'll turn the consolidated fact into a base fact). While I explore that avenue, if anyone has any insight into why a consolidated rule that only applies to the lowest level consolidations would break higher level consolidations, that would be appreciated!
    • Hm. Easier question might be. Without using Excel, is there any way to see what rules are applied to any specific fact?

      If I could trace the rule firings for one of my consolidated facts, I might be able to figure out why the rule didn't fire, or why the default aggregation didn't occur.

      Assuming that the default consolidation for specific facts can be traced also...
    • Thanks Noel.

      The bad thing is if I make a rule like this:

      ['e_element1','f_element1'] = C: IF(PALO.ECHILDCOUNT("db","dim_1",!'dim_1') == 0,
      PALO.DATA("db","cube_2",!'dim_1',"e_element1") * ['e_element2','f_element1'],

      (Which basically looks like my original rule but with a continue when its not a level 0 element),

      The continue() appears to search for a rule to apply and Jedox won't default to the default aggregation rule (the rule that gets triggered when there is no rule...) and as a result, the value for all higher level elements is 0 :(

      I'm thinking that I can temporarily get over this hump by writing something like this:
      ['e_element1','f_element1'] = C: IF(PALO.ECHILDCOUNT("db","dim_1",!'dim_1') == 0,
      PALO.DATA("db","cube_2",!'dim_1',"e_element1") * ['e_element2','f_element1'],
      SUM( IF ( child 1 exists, child 1), IF (child2 exists child 2), ... If (childN exists childN)))

      Its *UGLY* but will bide me until I can find a way to do this better...
    • The continue() should yield default consolidation if not followed by any other rules related to calculation subset.

      It could be a mistake in your formula.

      I see your formula only applies to Consolidated elements, what about base elements ? if they are 0 then higher level default consolidation would turn to 0.

      To check your confidence on Continue function you can do a simple check on Seeded demo database.
      ['Units'] = IF(PALO.ELEVEL("Demo","Products",!'Products') == 1,0,CONTINUE())

      Check to see all level 1 values are all 0 but level 2 and base level product values have default consolidated data.

      If you can explain your case with an example , maybe we can help!

      Best regards,
    • I came upon that same conclusion yesterday. All of my base elements are 0. Hence, since the default consolidation rule only aggregates from base elements, the results will always be zero (even if some of the consolidations are set manually to values other than 0.)

      Here's the rationale behind the rule.

      If each base element represents a country and the value represents the after tax salaries for each employees in a specific department within a specific subsidary within that country, I would like to compute the average after tax salary contribution for each department for each subsidiary. The problem is that each after tax salary is a consolidated element (gross_salary - total_deductions) and each country has a country specific currency conversion rates. My rule addresses these two problems.
      1. Country specific currency conversion rates are applied at the country level.
      2. Consolidations aggregate the converted salaries. They do not *average* conversion rates and apply the average conversion to the summed salaries.

      The biggest problem I had with my previous solution was if I had the following conversion dimension
      Total Employee Count, Conversion Rate, Country
      10, 1.1, Algeria
      50, 1.2, Cameroon
      50000, 5.7, South Africa

      If I have a department with 1 person in Algeria and 1 person in Cameroon, I would like the department's Africa contribution to be (1/10 *1.1 + 1/50 *1.2)/60 instead of (1/10*1.1 + 1/50 * 1.2 + 0*50000/5.7)/50060. Since the distribution of employees isn't known ahead of time, I can't know ahead of time the consolidations I'd need to make in this dimension. Also, since I'm applying the conversion to consolidated elements, I can't make full use of the default rule.

      BTW, I made a quick hack to get my aggregations to work correctly, the rule looks something like this:

      ['contribution','actual'] = IF( PALO.ECHILDCOUNT("db","Place",!'Place') == 0,
      PALO.DATA("db","PlaceToDollars",!'Place',"foreign_currency") /
      PALO.DATA("db","PlaceToDollars",!'Place',"local_currency") *
      ['local_contribution','actual'] ,
      SUM(IF(PALO.ECHILDCOUNT("db","Place",!'Place) >= 1,
      IF(PALO.ECHILDCOUNT("db","Place",!'Place') >= 2,
      ... <repeated 10 times>

      So, as long as I don't have more than 10 children for every consolidated element, this works. Its a bit hacky, but it was the only way I could think to get a sum of individual children's contributions where each child is a consolidation.
    • Hi,
      solution described by byoshimi is only possibility with current palo.
      Currently rules do not support array operations sum([dimension:element.children]) there is also no built in meta-rules programming that would generate the same logic - e.g. for-each(palo.echild, sum+=child-value))
      You can use your own generator of rules for such cases. Of course performance can be problem if you generate such base rules on large target areas.

    • Yes. Thanks Jiri.

      I think I can make performance a little better by shortcircuiting on the first non-existent child.
      Something like:

      If (! exists(child1), default calculation,
      value (child1) +
      if (! exists (child2), 0,
      value (child2) +
      if (!exists (child3), 0,
      value (child3)

      Instead of using the sum() function.

      At least using the IF() statements I can control the level of evaluation and stop evaluating when I run out of children.
    • yes, this seems to be optimal general solution for current engine.
      If you have performance problems and if your dimension is not changing often and if it is not too big (hundreds of consolidated elements) you can write generator of rules and run it everytime rules changes
      [parent1] = [child1ofparent1] * [c1] + [child2ofparent2] * [c2] + ...
      [parent2] = [child1ofparent2] * [c1] + [child2ofparent1] * [c2] +