0 Replies Latest reply: Apr 21, 2013 9:24 PM by BIDev RSS

    How to optimizate the performance?

    BIDev
      Hi Expert,

      I installed BIEE11g-11.1.1.6 64 bit on windows 2008 OS. And the ESSBASE is 11g.
      The detail of my problem as follow:

      When I make use of left or right functionality for cuting out the columns derived from Essbase in Analytics, the performance will be decline dramatically.
      Is there any better method for implementing this requirement? Or How to optimizate the SQL? Thanks.
      So I go to view the log for MDX as below:
      Original SQL:
      logic SQL:
      RqList distinct
      0 as c1 GB,
      Operation.Gen8,Operation as c2 GB,
      Products.Gen3,Products as c3 GB,
      Products.Gen3,Products - Memnor as c4 GB
      DetailFilter: Operation.Gen8,Operation = '1400002 FS XINGHUA'
      OrderBy: c1 asc, c2 asc NULLS LAST, c4 asc NULLS LAST

      Physical SQL:
      With
      set [_Operation8] as '{Distinct({[Operation].[1400002 FS XINGHUA]})}'
      set [_Products3] as 'Generate([Products].Generations(2).members, Descendants([Products].currentmember, [Products].Generations(3), leaves))'

      select
      {} on columns,
      {crossjoin({[_Operation8]},{[_Products3]})} properties GEN_NUMBER, [Products].[Memnor] on rows
      from [PMIX.PMIX]

      left or right SQL:
      logic SQL:
      SET VARIABLE QUERY_SRC_CD='Report';SELECT
      0 s_0,
      "PMIX"."Products"."Gen3,Products" s_1,
      LEFT("PMIX"."Operation"."Gen8,Operation",7) s_2,
      SORTKEY("PMIX"."Products"."Gen3,Products") s_3
      FROM "PMIX"
      WHERE
      (LEFT("Operation"."Gen8,Operation",7) = '1400002')
      ORDER BY 1, 3 ASC NULLS LAST, 4 ASC NULLS LAST
      FETCH FIRST 5000001 ROWS ONLY

      Physical SQL:
      With
      set [_Operation8] as 'Generate([Operation].Generations(2).members, Descendants([Operation].currentmember, [Operation].Generations(8), leaves))'
      set [_Products3] as 'Generate([Products].Generations(2).members, Descendants([Products].currentmember, [Products].Generations(3), leaves))'

      select
      {} on columns,
      {crossjoin({[_Operation8]},{[_Products3]})} properties GEN_NUMBER, [Products].[Memnor] on rows
      from [PMIX.PMIX]