1 2 Previous Next 20 Replies Latest reply on Jun 8, 2016 5:17 PM by Christian Berg

    How to implement the below SQL in RPD

    3174977

      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. Re: How to implement the below SQL in RPD
          Gianni Ceresa

          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 ...

          • 2. Re: How to implement the below SQL in RPD
            3174977

            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 ?

            • 3. Re: How to implement the below SQL in RPD
              Christian Berg

              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.

              1 person found this helpful
              • 4. Re: How to implement the below SQL in RPD
                3174977

                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 !

                • 5. Re: How to implement the below SQL in RPD
                  Gianni Ceresa

                  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.

                  1 person found this helpful
                  • 6. Re: How to implement the below SQL in RPD
                    3174977

                    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 !!

                    • 7. Re: How to implement the below SQL in RPD
                      Christian Berg

                      LTS content levels are not set correctly.

                       

                      Also: a view? Yuck.

                      • 8. Re: How to implement the below SQL in RPD
                        3174977

                        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 !

                        • 9. Re: How to implement the below SQL in RPD
                          Christian Berg

                          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

                          • 10. Re: How to implement the below SQL in RPD
                            3174977

                            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 !!

                            • 11. Re: How to implement the below SQL in RPD
                              Christian Berg

                              And how does HRS figure into this phyiscally?

                              • 12. Re: How to implement the below SQL in RPD
                                3174977

                                Hi Christian,

                                 

                                HRS is the OBIEE view created in physical layer and it has joins with W_PLAN_TIME & W_PA_HRS. So is the reason in BMM layer I added in LTS.

                                 

                                 

                                 

                                 

                                Thanks !

                                • 13. Re: How to implement the below SQL in RPD
                                  Christian Berg

                                  OK that looks fine bbut you havent answered my question about the content levels. Are they set for all fact sources?

                                  • 14. Re: How to implement the below SQL in RPD
                                    3174977

                                    Yes, I have set the content for all LTS in fact. I am having another issue now..

                                    without adding columns from HRS view  the report looks as below which is correct.

                                     

                                     

                                    when I add the column from HRS view it is duplicating the rows..Why is this happening.Sorry Christian if I am aksing silly questions..Please don't mind.

                                     

                                    Why this happening..

                                     

                                    Thank you so much !

                                    1 2 Previous Next