Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

RPD vs. Ad-hoc

Received Response
11
Views
4
Comments
Captain_Cook
Captain_Cook Rank 2 - Community Beginner
edited Aug 13, 2024 6:05PM in Oracle Analytics Forum

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!

Answers

  • FTisiot
    FTisiot Rank 6 - Analytics & AI Lead

    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
    John_K Rank 5 - Community Champion

    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
    Thomas Dodds Rank 8 - Analytics & AI Strategist

    +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]
    [Deleted User] Rank 2 - Community Beginner

    pre-agg vs post-agg ftw!