How to implement the below SQL in RPD — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

How to implement the below SQL in RPD

Received Response
71
Views
20
Comments
3174977
3174977 Rank 5 - Community Champion
edited Aug 13, 2024 6:17PM in Oracle Analytics Cloud and Server

Hi All,

I have a requirement where I need to have two columns for my reports that are not available in the views/tables that we are using . In order to have those columns in the report we need to follow the below SQL. How can the below SQL logic can be used in RPD or the reports.

Please suggest me..

select d.project_wid,

(select  sum(p.HRS) plan FROM dw_PLAN_TIME p where p.project_wid=d.project_wid) total_plan_hrs

,(select  sum(a.HRS) plan FROM Dw_Pa_Hrs a where a.project_wid=d.project_wid) total_PA_hrs

from dw_project_d d


Thanks !

«1

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Your SQL can't be used in the RPD just because if you use it you are making the whole concept of RPD useless (and paying an OBIEE license when SQL Developer could do the same for free).

    Yours is probably the most simple model you can have: dw_project_d is conformed dimension and dw_plan_time and dw_pa_hrs are 2 fact tables (can also be included in the same fact logical table using 2 logical table source).

    Set SUM as aggregation and you are done ...

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Gianni,

    Please see the below . I think the same SQL logic is already implemented right? so whatever hours we pick from dw_plan_time and dw_pa_hrs tables satisfy that SQL condition ?

    Am I missing something ?

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Your model looks just fine. Both facts join to your dimension table so now you can put aggregation rules on the measures and then analyze them by dimensional attributes.

    Proper execution is obviously also the creation of a dimensional hierarchy for the logical dimension itself and the subsequent setting of content levels for all sources.

  • 3174977
    3174977 Rank 5 - Community Champion

    Thanks Christian & Gianni,

    One more question similar to this.. There is another SQl logic which needs to be implemented in OBIEE. How can the below SQL be transformed..

    SELECT T.PROJECT_NAME,

                   T.TASK_NAME,

                   T.EMPLOYEE_NAME,

                   T.ENDING_DATE,

                   T.HRS,

                   SUM(T.HRS) ACTUAL,

                   0 FORECAST,

                   'A' STATUS,

                   (select SUM(T1.HRS)

                      FROM XXESC_PA_TIMECARDS T1

                     where t.PROJECT_NAME = t1.PROJECT_NAME

                       and t.TASK_NAME = t1.TASK_NAME

                       and t.EMPLOYEE_NAME = t1.EMPLOYEE_NAME

                     GROUP BY t1.PROJECT_NAME, t1.TASK_NAME, t1.EMPLOYEE_NAME) Total_Actual,

                   (select SUM(F1.HRS)

                      FROM XXESC_FORECAST_TIME F1

                     where t.PROJECT_NAME = F1.PROJECT_NAME

                       and t.TASK_NAME = F1.TASK_NAME

                       and t.EMPLOYEE_NAME = F1.EMPLOYEE_NAME) Total_Forecast

              FROM XXESC_PA_TIMECARDS T

             GROUP BY PROJECT_NAME, TASK_NAME, EMPLOYEE_NAME, ENDING_DATE, HRS

    In this SQL I am clear on how to get the Actual hours..which means we need to get sum of all hours that are there in XXESC_PA_TIMECARDS table..And Total_Actual & Total_Forecast hours we are getting them only when project name, task name & employee name are equal ? Is my understanding correct ..If so how can this be implemented.

    Thanks a lot !

  • Gianni Ceresa
    Gianni Ceresa Mod

    Similar as before, you model your tables making the joins based on your conditions between xxesc_pa_timecards and xxesc_forecast_time, for the "Total_Actual" you can add a "by" in the formula.

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Gianni,

    I have one more doubt.I have added a new view to my existing model. When I just use the columns from that view it is giving data..But when I combine the columns of that view with the other columns from other tables the data from that view columns are becoming null.Why is this happening ?

    Any suggestions please.

    Thanks !!

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    LTS content levels are not set correctly.

    Also: a view? Yuck.

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Christian,

    I have set up the LTS content but that is not working..View is created in OBIEE . In physical layer I am not giving any join conditions. In BMM layer I have added in LTS to my existing fact table. I tried giving join conditions in physical layer .But the joins are not making any difference.I don't know if I am missing something.

    Thanks !

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Your fact must be joined to *something* dimensional. Otherwise it's jsut a standalone table/view and even that needs to have a degenerate dimension in order for fact measures to be aggreagated along dimensional attributes

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Christian,

    I have the fact joined to the dimension in both physical & BMM layer..Please see the below..Am I missing anything..

    This is the  Business diagram..

    Please help me with suggestions..Thank you !!