6 Replies Latest reply on Dec 1, 2017 11:17 PM by Gianni Ceresa

    obiee 12c todate function query and performance issue

    srikanth_p

      Hi Experts,

       

      we started working on our new 12c and we built few YTD columns and seeing performance issue compared to our old 11g env . we have same columns and formulas in both envs . But 11g is fast compared to 12c.I deployed sample app rpd to re-create query coming from obiee.

       

      Attached the simple report i pulled using sample app data .

       

      Revenue YTD:

      logical formula:

      ToDate("SampleApp Lite"."F0 Revenue Base Measures"."Revenue", "SampleApp Lite"."H0 Time"."Year" )

       

       

      My report has YTD column and i have filter applied on the month column '2010 / 11'. I'm expecting OBIEE 12c to apply the month filter within the main query, instead it pull data for all years and months applying the filter on the outer queries due to the time series functions in 12c which is behaving differently comparing to obiee 11g. Since the main query is pulling the data for all years it is causing the performance issue?

       

      highly appreciated for someone to help improve proper query buliding as 11g . In our real time database we have 4 millions rows and the reports using ytd columns running slow.

       

      Is this a bug or do we need to do any tweak to our time dimension to get the right query and improve performance

       

       

      Logical Query:

      -------------------- SQL Request, logical request hash:
      bea3a341
      SET VARIABLE QUERY_SRC_CD='Report';SELECT
         0 s_0,
         "Sample Sales Lite"."Products"."Product Type" s_1,
         "Sample Sales Lite"."Time"."Per Name Month" s_2,
         DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product Type") s_3,
         "Sample Sales Lite"."Time Series"."Revenue YTD" s_4
      FROM "Sample Sales Lite"
      WHERE
      ("Time"."Per Name Month" = '2010 / 11')
      ORDER BY 2 ASC NULLS LAST, 4 ASC NULLS LAST, 3 ASC NULLS LAST

      FETCH FIRST 65001 ROWS ONLY

       


      Physical query:

       

      select sum(T10.Revenue) as c1,

           T11.Type as c2,

           T18.Per_Name_Month as c3,

           T11.Type_Key as c4

      from

           SAMP_PRODUCTS_D T11 /* D10 Product */ ,

           SAMP_TIME_MTH_D T18 /* D02 Time Month Grain */ ,

           SAMP_REVENUE_A T10 /* F11 Revenue Facts Aggregate */

      where  ( T10.Prod_Key = T11.Prod_Key and T10.Bill_Mth_Key = T18.Mth_Key and T18.Per_Name_Month = '2010 / 11' )

      group by T11.Type_Key, T11.Type, T18.Per_Name_Month

      order by c3, c4

       

      ]]

      [2017-11-30T10:22:26.220-06:00] [OBIS] [TRACE:6] [] [] [ecid: 50b72dc5-4072-4909-8d89-7c8382c38aa3-00041d49,0:1:9:5] [sik: ssi] [tid: 62a8f700] [messageid: USER-18] [requestid: 72250033] [sessionid: 72250000] [username: weblogic] -------------------- Sending query to database named Sample App Lite Data (id: <<63880>>), connection pool named CP_EDW, logical request hash bea3a341, physical request hash 25f91ad6: [[

      WITH

      SAWITH0 AS (select T18.Per_Name_Month as c3,

           T18.Per_Name_Year as c4,

           ROW_NUMBER() OVER (PARTITION BY T18.Per_Name_Year ORDER BY T18.Per_Name_Year DESC) as c5,

           ROW_NUMBER() OVER (PARTITION BY T18.Per_Name_Year, T18.Per_Name_Month ORDER BY T18.Per_Name_Year DESC, T18.Per_Name_Month DESC) as c6

      from

           SAMP_TIME_MTH_D T18 /* D02 Time Month Grain */ ),

      SAWITH1 AS (select Case when case D1.c5 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c3 else NULL end ) end as c1,

           Case when case D1.c6 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY D1.c4 ORDER BY case D1.c6 when 1 then D1.c3 else NULL end ) end as c2,

           D1.c3 as c3,

           D1.c4 as c4

      from

           SAWITH0 D1),

      SAWITH2 AS (select distinct min(D1.c1) over (partition by D1.c4)  as c1,

           min(D1.c2) over (partition by D1.c4, D1.c3)  as c2,

           D1.c3 as c3

      from

           SAWITH1 D1),

      SAWITH3 AS (select sum(T10.Revenue) as c1,

           T11.Type_Key as c2,

           T18.Per_Name_Month as c3

      from

           SAMP_TIME_MTH_D T18 /* D02 Time Month Grain */ ,

           SAMP_PRODUCTS_D T11 /* D10 Product */ ,

           SAMP_REVENUE_A T10 /* F11 Revenue Facts Aggregate */

      where  ( T10.Prod_Key = T11.Prod_Key and T10.Bill_Mth_Key = T18.Mth_Key )

      group by T11.Type_Key, T18.Per_Name_Month),

      SAWITH4 AS (select T18.Per_Name_Month as c3,

           T18.Per_Name_Year as c4,

           ROW_NUMBER() OVER (PARTITION BY T18.Per_Name_Year ORDER BY T18.Per_Name_Year DESC) as c5,

           ROW_NUMBER() OVER (PARTITION BY T18.Per_Name_Year, T18.Per_Name_Month ORDER BY T18.Per_Name_Year DESC, T18.Per_Name_Month DESC) as c6

      from

           SAMP_TIME_MTH_D T18 /* D02 Time Month Grain */ ),

      SAWITH5 AS (select Case when case D1.c5 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c3 else NULL end ) end as c1,

           Case when case D1.c6 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY D1.c4 ORDER BY case D1.c6 when 1 then D1.c3 else NULL end ) end as c2,

           D1.c3 as c3,

           D1.c4 as c4

      from

           SAWITH4 D1),

      SAWITH6 AS (select distinct min(D1.c1) over (partition by D1.c4)  as c1,

           min(D1.c2) over (partition by D1.c4, D1.c3)  as c2,

           D1.c3 as c3

      from

           SAWITH5 D1)

      select sum(D5.c1) as c1,

           D3.c3 as c2,

           D5.c2 as c3

      from

           SAWITH2 D4,

           SAWITH3 D5,

           SAWITH6 D3

      where  ( D3.c1 = D4.c1 and D3.c3 = '2010 / 11' and D4.c3 = D5.c3 and D3.c2 >= D4.c2 )

      group by D3.c3, D5.c2

      order by c2, c3

       

       

      Thanks

      Srikanth

        • 1. Re: obiee 12c todate function query and performance issue
          srikanth_p

          let me know if anyone got a chance to look at my issue above .

          • 2. Re: obiee 12c todate function query and performance issue
            Gianni Ceresa

            Hi,

            I didn't read all the details (of the query etc.) but few hints for you ...

            You can maybe try to add sequence numbers in your time hierarchy to help OBIEE generate a simpler query. As your issue seems to be on "when" (where) the filter is applied it will maybe not change anything, but still worth a look.

            Never forget that time series calculations will always (except few exceptions) be better when modelled in the RPD directly (by that I mean modelling them with joins on the time dimension table to automatically aggregate figures without using time series calculations). By doing that query will be simpler and will probably improve performance even compared to your old 11g.

            • 3. Re: obiee 12c todate function query and performance issue
              srikanth_p

              Thanks for your reply .

               

              In our system we don't have aggregate tables and based on effective date in the fact table we have created monthkey , qtrkey, yearkey and joined to their respective time dimension tables .

               

              but I expected 12c to be same as 11g in terms of query building for time series calculations.

              • 4. Re: obiee 12c todate function query and performance issue
                Gianni Ceresa

                As always the changes in query generation aren't officially listed, but 12c changed quite a bunch of things on how query are generated.

                That's why there is a flag to make 12c work as an old 11g, but don't get me wrong: I'm not saying you have to do that as you are going to lose all the new things, so what would be the point of having 12c if you use that setting?

                 

                You don't need aggregate tables in the RPD, you just need to model some aliases using a BETWEEN instead of a = to calculate your YTD figures.

                • 5. Re: obiee 12c todate function query and performance issue
                  srikanth_p

                  Thanks Gianni.

                   

                  You don't need aggregate tables in the RPD, you just need to model some aliases using a BETWEEN instead of a = to calculate your YTD figures.-----your statement from above

                   

                  I already joined fact to month table ,fact to qtr table ,fact to year table using there respective keys .

                   

                  in logical layer I am creating YTD columns using TODATE function (example: ToDate("SampleApp Lite"."F0 Revenue Base Measures"."Revenue", "SampleApp Lite"."H0 Time"."Year" ). what is another way of calculating YTD figures .

                   

                  In our reporting we usually show fact figures by per-month example : report filter 2017 /10 and we expect data from 2017/01 to 2017/10 from the fact tables. sometime users go back dated and use prompt to give month like 2016/12 and we expect to show whole 2016/01 to 2016/12 on the reports.

                   

                  Thanks

                  Srikanth

                  • 6. Re: obiee 12c todate function query and performance issue
                    Gianni Ceresa

                    srikanth_p wrote:

                     

                    I already joined fact to month table ,fact to qtr table ,fact to year table using there respective keys .

                    I just can't picture that in my mind: you have 3 tables for your time dimension?

                    In general you have a single time dimension with many columns, one of the columns could be "year_start_date", so you know that for a YTD you need all the dates between "year_start_date" and the current date.

                    So by aliasing the time dimension you can have that when period = "2017/10" for example, the query is generated as :

                    ... WHERE "time dim"."period" = "2017 / 10" and "fact"."date" BETWEEN "time dim"."year_start_date" AND "time dim"."date"

                     

                    That's just a generic example of how you can calculate it via the "joins" (of course your current data highly influence what is possible or not)