Rule x Report (Report Studio) Performance

[bruno.zanelli]

Hello guys,

I have a rule wich is taking me about 2 minutes to conclude execution. There's no big deal about this execution time, but it's affecting the reports (made in report studio) in this specific application, running in Report Studio and Cognos workspace.

I've read about report studio tips on performance and I'm also working on improvements in this rule. I tried skipcheck and feeders, but it mess up some steps...so I'm looking for the correct point so I don't overfeed or underfeed.

Can you suggest anything? I attached the dimension properties and the rule code, to make this problem more visible.

Thanks in advance!

[qml]

Suggestion number 1:
Instead of using the clause IF (ELLEV(,) = 0 prefix your rules on the RHS with N: qualifiers.

Suggestion number 2:
Use skipcheck and feeders. I cannot stress that enough. If you can't get them to work, you need to post your attempts here with a description and someone will surely be able to help. The more detailed description, the better.

[bruno.zanelli]

Dear qml, thanks for your help! I'll write down about this calculation and my attempts.

I discovered the report doesn't open, even if I use only the first script:

['qtd_metric'] = if (ELLEV('estrutura_imc', !estrutura_imc) = 0,
1,
STET);

The reason I'm not using "N:" is that I want to place 1 only when it's Level 0 of Estrutura_imc dimension. "N:" would be applied to all dimensions.

Using SKIPCHECK and FEEDERS, didn't get me better results, as the report keeps "thinking" and refuses to open. Below, there is the test I made with this single script:

-------------------------------------------------------------------------------------------------------------------------
SKIPCHECK

['qtd_metric'] = if (ELLEV('estrutura_imc', !estrutura_imc) = 0,
1,
STET);

FEEDERS;

['qtd_metric'] => ['qtde_contatos_feito'];

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

FEEDERS, as far as I know, will check only the members that have some value, in my case "qtde_contatos_feito", wich also wouldn't be valid, because I need "1" in all members of Estrutura_imc dimension.

Thanks for your help!

[tomok]

I can't speak to your performance issues as I have no idea what you are trying to accomplish by assigning a value of 1 to every leaf level element in the estrutura_imc dimension, but your feeders statement is wrong. If estrutura_imc is the element that has an actual value, and not a calculated one, you should swap places with qtd_metric in your feeder code.

[bruno.zanelli]

tomok, I guess I didn't get your suggesntion.

The reason I'm fixing "1" on leaf members of Estrutura_IMC dimension is that need the average on upper levels and I need to know children (leaf) quantity for each member, but I was considering only Estrutura_IMC dimension and not all dimensions.

As I explained , I tried to use "N:", but it places "1" in all leaf members of all dimensions and messes up the calculation.

[tomok]

Your feeder statement is wrong. qtde_contatos_feito and qtd_metric need to swap places.

[bruno.zanelli]

tomok, I got your point, but there's still a problem.

I used:
--------------------------------------------------------------
SKIPCHECK;

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

FEEDERS;

['qtde_contatos_feitos'] => ['qtd_metric'];
--------------------------------------------------------------
Everything's fine (calculation time, reports), except that 'qtd_metric' gives me 2 as result of the test, instead of 10, as expected:

avg.png (10.5 KiB) Viewed 5964 times

Is there a way to keep the old behaviour ( "1" for each member) without having the impact I used to have?

Thanks a lot!!!

[Steve Rowe]

Just a side point on the way you are calculating your average. It's pretty unusal to count the null values when you calculate the average and I'd argue that the last set of results you posted are correct. i.e. The average of 3 numbers should be divided by 3 not the number of possible combinations there could be.

You might want to double check your requirements....

If you do still want to do this then there may be another approach using ElCompN but it is only really practical if you have a fairly simple dimension with only 1 hierarchy and not too many parents.


['qtd_metric'] = C:
#The number of children this element has
ElCompN( 'metricas_imc' , !metricas_imc)+
#The number of children this elements children have
ElCompN( 'metricas_imc' ,ElComp( 'metricas_imc',!metricas_imc,1))+
ElCompN( 'metricas_imc' ,ElComp( 'metricas_imc',!metricas_imc,2))+
ElCompN( 'metricas_imc' ,ElComp( 'metricas_imc',!metricas_imc,3))+
ElCompN( 'metricas_imc' ,ElComp( 'metricas_imc',!metricas_imc,4))......

You need one of these for maximum number of children consolidations at L2 and above have. The higher this number the less likely it is that this rule will perform well.

A very similair but alternative rule is as follows

['qtd_metric'] = C:
#The number of children this element has
ElCompN( 'metricas_imc' , !metricas_imc)+
#The number of children this elements children have
DB('your cube', 'metricas_imc' ,!otherdimerefs,ElComp( 'metricas_imc',!metricas_imc,1))+
DB('your cube', 'metricas_imc' ,!otherdimerefs,ElComp( 'metricas_imc',!metricas_imc,2))+
DB('your cube', 'metricas_imc' ,!otherdimerefs,ElComp( 'metricas_imc',!metricas_imc,3))+
DB('your cube', 'metricas_imc' ,!otherdimerefs,ElComp( 'metricas_imc',!metricas_imc,4))......

A rule is probably the wrong way to calculate this though as you calculating what is really static or slow moving data with a rule and in many more places than you need to.
If there is such a thing as a "right" answer it is to create a numeric attribute and populate this with the child count in a TI and then reference this from your rules.

HTH
Cheers,

[lotsaram]

SKIPCHECK;

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

FEEDERS;

['qtde_contatos_feitos'] => ['qtd_metric'];
--------------------------------------------------------------
Everything's fine (calculation time, reports), except that 'qtd_metric' gives me 2 as result of the test, instead of 10, as expected:

Is there a way to keep the old behaviour ( "1" for each member) without having the impact I used to have?

To follow from what Steve pointed out, based on the fact that 'qtde_contatos_feito' contains only 2 populated cells then a value of 2 for 'qtd_metric' is EXACTLY what you would expect to see since you have defiled 'qtd_metric' as a leaf calculation hence values will only consolidate where they are fed.

Based on your stated requirement you need to divide by the count of all leaf descendants for this dimension. So you need some logic to get the ELCOMPN from the first level and then sum from there. The rule Steve showed will do what's needed but it is a fair bit of work to write the rules and also subject to breaking should the dimension structure change and there be more children at any level than you have allowed for. Assuming the hierarchy isn't ragged and leaf elements are only found as children of the 1st level of consolidations you could have an alternative rule for C: only with a test for ELLEV and if +1 then ELCOMPN else ConsolidateChildren. This will work and it is low maintenance but it would be slow - you might as well keep your current rule and feed to 'BANCO'.

The best solution is the one Steve has suggested but I would suggest a purpose built 2D cube to calculate qtd_metric (really a leaf count) with the rule exactly as you have it
['Leaf Count'] = N: 1; C: STET;
The reason for a 2D cube is you don't need skipcheck or feeders in a 2D cube since the data will be naturally dense and performance will still be good without skipcheck. The reason for creating a special purpose 2D cube is that numeric rules don't really work in attribute cubes.

Also the rule for qtd_metric should then be C: only and lookup the cube
['qtd_metric'] = C: DB('Cube', !estrutura_imc, 'Leaf Count');
There's actually no need to feed this rule, but if you want it to display in a zero suppressed view then you can continue to feed from qtde_contatos_feitos as you are currently.

Given your current level of understanding that might be a bit much to take in but give it a go and good luck!

[bruno.zanelli]

Gentlemen,

I really would like to thank your help, sharing your knowledge and ideas with me. I tested some of your suggestions and they fit perfectly!

I'll share these solutions with the customer and keep developing based on the information you gave me!

Thanks once more!!!