Feeder behaviour in multi-level hierarchy

[bruno.zanelli]

Guys,

I'm getting used to SKIPCHECK and FEEDERS, but there's still a point concerning my rule. According to the script and images below, I can't figure out how to place FEEDERS correctly in order to get the same values as without FEEDERS.

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

SKIPCHECK;

#------------------------------------------------------------ AUX

['qtd_metric'] = N: 1; C: STET;

#------------------------------------------------------------ ok, nota%

['nota_%_rm'] =
if (ELCOMPN('estrutura_imc', !estrutura_imc) >=1,
0,
(((['qtde_visitas_feitas'] / ['qtde_meta_visitas']) + (['qtde_contatos_feitos'] / ['qtde_meta_contatos']))/2)
);
#------------------------------------------------------------ ok, nota atual

['aux_nota_imc'] = if (ELLEV('estrutura_imc', !estrutura_imc) = 0,
If(['nota_%_rm'] >= 1,1,If(1 > ['nota_%_rm'] & ['nota_%_rm'] >= 0.9,2,
If(0.9 > ['nota_%_rm'] & ['nota_%_rm'] >= 0.8,3,
If(0.8 > ['nota_%_rm'] & ['nota_%_rm'] >= 0.6,4,If(['nota_%_rm'] < 0.6,5,0))
)
)
),STET
);


['nota_imc'] = if (ELLEV('estrutura_imc', !estrutura_imc) = 0,
['aux_nota_imc'],
['aux_nota_imc']/['qtd_metric']
);

#------------------------------------------------------------ FEEDERS

FEEDERS;
['qtde_meta_visitas'] => ['nota_%_rm','qtd_metric','aux_nota_imc','qtd_metric'];

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

Without FEEDERS

without_feeders.JPG (280.28 KiB) Viewed 4775 times

With FEEDERS

with_feeders.JPG (239.39 KiB) Viewed 4775 times

Thanks in advance

[BigG]

Hi, since its a heirarchy consolidated element that you have highlighted, is 12 the total of all its children? I think possibly 27 is the sum of all 'URB VILA' children? so isnt the 27 value more correct?

Without digging deeper - The feed is created only once, so I assume another feed was set up to give you a 12 total (only partially feeding all children)? possibly if you have time, comment out feeder, right click cube, unload, then go back to rules and make a small change (add a space) save, do this twice to make sure all refeed occurs. See results... should be zero for total? Then reapply feeder and total is 27? Note: if any other feeds are coming from other cubes you may need to 'change' and save these to see all feeders refire. Not ideal way to test but works if you do the right order.

['qtde_meta_visitas'] values are feeding all calcs so if value in ['qtde_meta_visitas'] then it will cause the target to calculate and then rollup the total.

Note: use \ rather than / for division to prevent #NA

[bruno.zanelli]

Hi, since its a heirarchy consolidated element that you have highlighted, is 12 the total of all its children? I think possibly 27 is the sum of all 'URB VILA' children? so isnt the 27 value more correct?

Without digging deeper - The feed is created only once, so I assume another feed was set up to give you a 12 total (only partially feeding all children)? possibly if you have time, comment out feeder, right click cube, unload, then go back to rules and make a small change (add a space) save, do this twice to make sure all refeed occurs. See results... should be zero for total? Then reapply feeder and total is 27? Note: if any other feeds are coming from other cubes you may need to 'change' and save these to see all feeders refire. Not ideal way to test but works if you do the right order.

['qtde_meta_visitas'] values are feeding all calcs so if value in ['qtde_meta_visitas'] then it will cause the target to calculate and then rollup the total.

Note: use \ rather than / for division to prevent #NA

@BigG, coincidently, 12 was a bad number in this example!!!

The cell with "27" value should be the sum of all 'URB VILA' children for "2012/Jan" and not a sum for all 'URB VILA'. (I don't know if I made myself clear at this point, but instead of "27", it should sum [0, 5, 1, 2, 2, 1, 1, 0, 0, 0])

I tested your suggestion and the "unload" got the "27" cell zeroed. Now, even with FEEDERS it's still zero I guess it should give me a different value, right?


Thanks for your tip on \ rather than /!!!

[BigG]

Maybe ['qtde_meta_visitas'] needs refiring if from a external cube...iif so, go to the source cube open rules and make small change then save, then go back to tst_calc2 and do the same...

Feeders should refire when you save changes to rules, then you should see 27 month you describe,

Then all is good? I cannot see what the orginal problem was really, your 27 with feeders is correct, then why worry about the 12 without feeders?

[bruno.zanelli]

It's a single cube, without DB() functions from other cubes!

The original problem is that the correct value in this case should be 12 (the sum of it's directly children, not the sum of the dimensional member in this scenario). That's what I still don't get using FEEDERS.

Thanks again!!

[asutcliffe]

Hi Bruno

Have you tried tracing the calculation (right click-> Trace Calculation) to see where it's getting 27 from? Is it really being consolidated or is it being set by a rule?

If it is a being calculated as a consolidation, are you sure there aren't any more children rolling up to "URB Vila Prudente"? If it really is just those ten elements (and they all have a weighting of 1) I don't see how underfeeding can cause you to get 27 given the data in your view.

Cheers,
Alex

[paulsimon]

Hi Bruno

Firstly it would help people on the Forum and probably yourself in the future, if you used some indentation in your rules, eg

Code: Select all

['aux_nota_imc'] = 
  if(ELLEV('estrutura_imc', !estrutura_imc) = 0
    , 
    If(['nota_%_rm'] >= 1
        ,
        1
        ,
        If(1 > ['nota_%_rm'] & ['nota_%_rm'] >= 0.9
          ,
          2
          ,
          If(0.9 > ['nota_%_rm'] & ['nota_%_rm'] >= 0.8
            ,
            3
            ,
            If(0.8 > ['nota_%_rm'] & ['nota_%_rm'] >= 0.6
               ,
               4
               ,
               If(['nota_%_rm'] < 0.6
                  ,
                  5
                  ,
                  0
               )
            )
          )
        ) 
    )
    ,
    STET 
  ) ; 

The reason that you are getting a problem is because your are overriding consolidation. Your view only shows consolidations in one dimension but your cube has two other dimensions with consolidations. Depending on which set of consolidations it uses to get the figures you will get different results.

Is it possible to do the rule at N: level?

If not, is it possible to make the rule look at eg top level consolidations in the other dimensions.

If not then you can look at ConsolidateChildren but that has a performance impact.

Possibly your feeder should be from the numerator rather than the denominator, but I can't tell without understanding more about your data.

I suspect that adding the feeder is changing the order of calculation and that it is consolidating up one of the other dimensions, before applying the rule, or more likely applying the rule at the N: level and then consolidating the results up the other two dimensions, which would tie in with you getting a higher number. Essentially when you override consolidation as you are, TM1 potentially has more than one way to calculate the value for a consolidation and you can get inconsistent results.

Regards

Paul Simon