6 Replies Latest reply: Feb 27, 2013 9:33 PM by MonikaGarg RSS

    Issue on adding calculation logic to a measure in a report.

    MonikaGarg
      Hi All,

      We are facing a strange issue in our reports. Any inputs on this would be highly appreciated.

      Issue Description: Adding a calculation logic [in our case divide by 1000] to a measure, modifes the query drastically and hence performace degrades by atleast 6times.
      As soon as I remove "/1000", query and performance both comes back to normal.
                This is happening in both compound view and pivot view.

      Steps to reproduce:
      1. A report created with columns: Fiscal Month, Manager Level3, Revenue amount (CD)
      2. Modify the pivot view to add total at Fiscal Month level and at the Grand total level
      3. Run the report. Data comes back in about 1 min.
      3. Modify the report column formula "Revenue Amount (CD)" to include "/1000"
      4. Save it and run the report
      5. Report comes back in about 6mins.

      Debugging:
      1. The sql generated in the original report is pretty straight forward with tables and joins used as required
      2. The sql generated later is huge, running the same main query multiple times and then joining that data and doing lots of other stuff.

      Not sure why just adding "/1000" in the measure formula should modify the sql in such a major way.

      SQLs:
      1. If you wish to have a look at the sql, I can paste them as well.

      Temporary workaround:
      1. Moved the "/1000" logic to the RPD itself.

      Any thoughts?

      Thanks & Regards
      Monika
        • 1. Re: Issue on adding calculation logic to a measure in a report.
          Srini VEERAVALLI
          Did you try with multiply by 0.001 instead of divide?
          • 2. Re: Issue on adding calculation logic to a measure in a report.
            Sasi  Nagireddy
            Can you paste the SQL if possible
            • 3. Re: Issue on adding calculation logic to a measure in a report.
              MonikaGarg
              Multiplying by 0.001 also does not help.
              • 4. Re: Issue on adding calculation logic to a measure in a report.
                MonikaGarg
                =================== G O O D Q U E R Y ===================================

                select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7 from ( select D1.c1 as c1,
                D1.c2 as c2,
                D1.c3 as c3,
                D1.c4 as c4,
                D1.c5 as c5,
                sum(D1.c6) over (partition by D1.c2) as c6,
                sum(D1.c7) over () as c7
                from
                (select D1.c1 as c1,
                D1.c2 as c2,
                D1.c3 as c3,
                D1.c4 as c4,
                D1.c5 as c5,
                sum(D1.c5) as c6,
                sum(D1.c5) as c7
                from
                (select distinct 0 as c1,
                D1.c2 as c2,
                D1.c3 as c3,
                D1.c4 as c4,
                D1.c1 as c5,
                D1.c5 as c8
                from
                (select sum(T1132909.CONVERSION_RATE * T1155314.ACCTD_AMOUNT) as c1,
                T1147257.ENT_PERIOD as c2,
                T1132282.LVL_03_RCODE as c3,
                T1147257.ENT_DIM_PERIOD_NUM as c4,
                T1147257.PER_NAME_ENT_PERIOD as c5
                from
                (
                (
                GL_DAILY_RATES T1132909 /* THE RATES TABLE */
                inner join MISFII_REVENUE_AR_JE_OM_V T1155314 /* THE FACT TABLE */
                On T1132909.CONVERSION_DATE = TRUNC(T1155314.GL_DATE) and T1132909.TO_CURRENCY = 'CD' and T1132909.CONVERSION_TYPE = '1022' and T1132909.FROM_CURRENCY = T1155314.FUNCTIONAL_CURRENCY
                ) left outer join (WC_RCODE_DH T1132282 /* THE MANAGER DIM */
                inner join WC_OFA_RCODE_TMP T1132288 /* THE MANAGER TEMP TABLE */
                On T1132282.RCODE = case when T1132288.RCODE is null then 'UNK' else T1132288.RCODE end
                )
                On T1132288.COST_CENTER_NUM = T1155314.COST_CENTER and T1132288.PROFIT_CENTER_NUM = T1155314.COMPANY_CODE
                ) left outer join W_DAY_D T1147257 /* THE TIME DIM */
                On T1147257.DAY_DT = TRUNC(T1155314.GL_DATE)
                where ( T1147257.PER_NAME_ENT_YEAR is not null )
                group by T1132282.LVL_03_RCODE, T1147257.ENT_DIM_PERIOD_NUM, T1147257.PER_NAME_ENT_PERIOD, T1147257.ENT_PERIOD
                ) D1
                ) D1
                group by D1.c1, D1.c2, D1.c3, D1.c4, D1.c5
                ) D1
                order by c1, c4, c3 ) D1 where rownum <= 65001
                • 5. Re: Issue on adding calculation logic to a measure in a report.
                  MonikaGarg
                  =============== B A D Q U E R Y ================================

                  select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7 from ( select D1.c1 as c1,
                  D1.c2 as c2,
                  D1.c3 as c3,
                  D1.c4 as c4,
                  D1.c5 as c5,
                  D1.c6 as c6,
                  D1.c7 as c7
                  from
                  (select D1.c1 as c1,
                  D1.c2 as c2,
                  D1.c3 as c3,
                  D1.c4 as c4,
                  D1.c5 as c5,
                  D1.c6 as c6,
                  D1.c7 as c7,
                  D1.c8 as c8
                  from
                  (select 0 as c1,
                  D1.c2 as c2,
                  D1.c3 as c3,
                  D1.c4 as c4,
                  D1.c5 as c5,
                  D1.c1 as c6,
                  D2.c7 as c7,
                  D1.c6 as c8,
                  ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, D1.c4, D1.c6 ORDER BY D1.c2 ASC, D1.c3 ASC, D1.c4 ASC, D1.c6 ASC) as c9
                  from

                  (select max(D1.c1) / 1000 as c1,
                  D1.c2 as c2,
                  D1.c5 as c3,
                  D1.c6 as c4,
                  D1.c4 / 1000 as c5,
                  D1.c3 as c6
                  from
                  (select distinct sum(D1.c1) over (partition by D1.c3) as c1,
                  D1.c2 as c2,
                  D1.c3 as c3,
                  sum(D1.c1) over (partition by D1.c5, D1.c3, D1.c6) as c4,
                  D1.c5 as c5,
                  D1.c6 as c6
                  from
                  (select sum(T1132909.CONVERSION_RATE * T1155314.ACCTD_AMOUNT) as c1,
                  T1147257.ENT_PERIOD as c2,
                  T1147257.PER_NAME_ENT_PERIOD as c3,
                  T1132282.LVL_03_RCODE as c5,
                  T1147257.ENT_DIM_PERIOD_NUM as c6
                  from
                  (
                  (
                  GL_DAILY_RATES T1132909 /* THE RATES TABLE */
                  inner join MISFII_REVENUE_AR_JE_OM_V T1155314 /* THE FACT TABLE */
                  On T1132909.CONVERSION_DATE = TRUNC(T1155314.GL_DATE) and T1132909.TO_CURRENCY = 'CD' and T1132909.CONVERSION_TYPE = '1022' and T1132909.FROM_CURRENCY = T1155314.FUNCTIONAL_CURRENCY
                  ) left outer join (WC_RCODE_DH T1132282 /* MANAGER DIM */
                  inner join WC_OFA_RCODE_TMP T1132288 /* MANAGER TEMP */
                  On T1132282.RCODE = case when T1132288.RCODE is null then 'UNK' else T1132288.RCODE end
                  )
                  On T1132288.COST_CENTER_NUM = T1155314.COST_CENTER and T1132288.PROFIT_CENTER_NUM = T1155314.COMPANY_CODE
                  ) left outer join W_DAY_D T1147257 /* TIME DIM */
                  On T1147257.DAY_DT = TRUNC(T1155314.GL_DATE)
                  where ( T1147257.PER_NAME_ENT_YEAR is not null )
                  group by T1132282.LVL_03_RCODE, T1147257.ENT_DIM_PERIOD_NUM, T1147257.PER_NAME_ENT_PERIOD, T1147257.ENT_PERIOD
                  ) D1
                  ) D1,
                  (select sum(T1132909.CONVERSION_RATE * T1155314.ACCTD_AMOUNT) as c1
                  from
                  GL_DAILY_RATES T1132909 /* RATES TABLE */ ,
                  MISFII_REVENUE_AR_JE_OM_V T1155314 /* THE FACT TABLE */
                  where ( T1132909.CONVERSION_DATE = TRUNC(T1155314.GL_DATE) and T1132909.CONVERSION_TYPE = '1022' and T1132909.FROM_CURRENCY = T1155314.FUNCTIONAL_CURRENCY and T1132909.TO_CURRENCY = 'CD' )
                  ) D2
                  group by D1.c4 / 1000, D1.c2, D1.c3, D1.c5, D1.c6
                  ) D1 ,
                  (select max(D2.c1) / 1000 as c7
                  from
                  (select distinct sum(D1.c1) over (partition by D1.c3) as c1,
                  D1.c2 as c2,
                  D1.c3 as c3,
                  sum(D1.c1) over (partition by D1.c5, D1.c3, D1.c6) as c4,
                  D1.c5 as c5,
                  D1.c6 as c6
                  from
                  (select sum(T1132909.CONVERSION_RATE * T1155314.ACCTD_AMOUNT) as c1,
                  T1147257.ENT_PERIOD as c2,
                  T1147257.PER_NAME_ENT_PERIOD as c3,
                  T1132282.LVL_03_RCODE as c5,
                  T1147257.ENT_DIM_PERIOD_NUM as c6
                  from
                  (
                  (
                  GL_DAILY_RATES T1132909 /* RATES TABLE */
                  inner join MISFII_REVENUE_AR_JE_OM_V T1155314 /* THE FACT TABLE */
                  On T1132909.CONVERSION_DATE = TRUNC(T1155314.GL_DATE) and T1132909.TO_CURRENCY = 'CD' and T1132909.CONVERSION_TYPE = '1022' and T1132909.FROM_CURRENCY = T1155314.FUNCTIONAL_CURRENCY
                  ) left outer join (WC_RCODE_DH T1132282 /* MANAGER DIM */
                  inner join WC_OFA_RCODE_TMP T1132288 /* MANAGER TEMP */
                  On T1132282.RCODE = case when T1132288.RCODE is null then 'UNK' else T1132288.RCODE end
                  )
                  On T1132288.COST_CENTER_NUM = T1155314.COST_CENTER and T1132288.PROFIT_CENTER_NUM = T1155314.COMPANY_CODE
                  ) left outer join W_DAY_D T1147257 /* Dim_W_DAY_D_GL_Date */
                  On T1147257.DAY_DT = TRUNC(T1155314.GL_DATE)
                  where ( T1147257.PER_NAME_ENT_YEAR is not null )
                  group by T1132282.LVL_03_RCODE, T1147257.ENT_DIM_PERIOD_NUM, T1147257.PER_NAME_ENT_PERIOD, T1147257.ENT_PERIOD
                  ) D1
                  ) D1,
                  (select sum(T1132909.CONVERSION_RATE * T1155314.ACCTD_AMOUNT) as c1
                  from
                  GL_DAILY_RATES T1132909 /* THE RATES TABLE */ ,
                  MISFII_REVENUE_AR_JE_OM_V T1155314 /* THE FACT TABLE */
                  where ( T1132909.CONVERSION_DATE = TRUNC(T1155314.GL_DATE) and T1132909.CONVERSION_TYPE = '1022' and T1132909.FROM_CURRENCY = T1155314.FUNCTIONAL_CURRENCY and T1132909.TO_CURRENCY = 'CD' )
                  ) D2
                  ) D2
                  ) D1
                  where ( D1.c9 = 1 )
                  ) D1
                  order by c1, c4, c3 ) D1 where rownum <= 65001
                  • 6. Re: Issue on adding calculation logic to a measure in a report.
                    MonikaGarg
                    I pasted the queries with proper indentation. But I see that all indentation is now gone :(