RPD vs. Ad-hoc

Captain_Cook

Hello

I have a fairly basic question. Say, I am doing a Percentage Calculation (Num/Den)*100.

Num = Count (A) Where Col_1 = 'abc' and col_2 < sysdate

Den = Count (A) Where Col_1 = 'abc'

In some cases, there could be multiple conditions for num and den.

I am trying to evaluate two cases - I do the calculation on the fly using Ad-hoc layer or I create two objects - Num and Den in BMM and use them in front-end. Is there going to be a difference in query execution time in both the cases?

Thanks!

FTisiot

Hi,

As a general rule I believe the more you push down to the RPD the better it is.

If for example you create an analysis with three columns: Num, Den, Num/Den

your SQL will probably look like

SELECT NUM, DEN, NUM/DEN from TABLE

But if you are only interested in Num/Den then creating an analysis with only Num/Den will provide

SELECT NUM/DEN from TABLE

Regarding performances, I believe if you push down to the RPD your'll have more chances that the column value gets cached properly.

Another +1 of using the RPD is reusability: you define the formula once and it's there for all the other users. No need to redefine it every time (with this process being error prone)

John_K

Also bear in mind that if you need to a calculation pre-aggregation then you must do that in the RPD using a physical expression. Physical expressions are evaluated pre-aggregation, logical expressions post-aggregation.

Thomas Dodds

+1 to your answer ....

In addition, the more you push back to the database the better ... within reason ... especially calcs like this you don't want to store the end result, but want to fetch, aggregate then divide -- it's logical in nature.

[Deleted User]

pre-agg vs post-agg ftw!