Oracle Analytics and AI

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)