5 Replies Latest reply: Jul 25, 2012 11:24 AM by SPowell42 RSS

    OBIEE 11g - serious problem when rotating columns into "table prompt" area

    SPowell42
      Hi, I'm hitting an OBIEE 11g issue that's causing huge problems on reports. Essentially, the problem is this - if I dump all the columns of a query to a table, everything looks fine. But if I rotate one of the columns into the "table prompts" area, OBIEE is actually resubmitting a WRONG query. The query format changes from:

      WITH
      SAWITH0 AS (select sum(T38965.ORIGINAL_BUDGET) as c1,
      T12637.ORG_ID_DESC as c2
      from
      GL_ANALYTICS.DIM_ACCOUNT T12582,
      GL_ANALYTICS.DIM_ORG T12637,
      GL_ANALYTICS.DIM_ACCOUNTING_PERIOD T12597,
      GL_ANALYTICS.FACT_LEDGER T38965 / FACT_LEDGER_ytd */*
      where  ( T12582.ACCOUNT_KEY = T38965.ACCOUNT_KEY and T12582.STATEMENT_TYPE_CODE = 'Income Statement' and T12597.ACCOUNTING_PERIOD = 11 and T12597.FISCAL_YEAR = T38965.FISCAL_YEAR and T12597.FISCAL_YEAR = 2012 and T12637.ORG_KEY = T38965.ORG_KEY and T38965.FISCAL_YEAR = 2012 and (T12637.DEPARTMENT_CODE_DESC in ('D0200 - Arts Administration', 'D0206 - Film Studies')) and T12597.ACCOUNTING_PERIOD >= T38965.ACCOUNTING_PERIOD )
      group by T12637.ORG_ID_DESC)
      select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,
      D1.c2 as c2,
      D1.c1 as c3
      from
      SAWITH0 D1
      order by c2 ) D1 where rownum <= 65001


      and changes to:

      WITH
      SAWITH0 AS (select distinct T12637.ORG_ID_DESC as c1
      from
      GL_ANALYTICS.DIM_ACCOUNT T12582,
      GL_ANALYTICS.DIM_ORG T12637,
      GL_ANALYTICS.DIM_ACCOUNTING_PERIOD T12597,
      GL_ANALYTICS.FACT_LEDGER T38927 / FACT_LEDGER_period */*
      where  ( T12582.ACCOUNT_KEY = T38927.ACCOUNT_KEY and T12597.ACCOUNTING_PERIOD = T38927.ACCOUNTING_PERIOD and T12597.FISCAL_YEAR = T38927.FISCAL_YEAR and T12582.STATEMENT_TYPE_CODE = 'Income Statement' and T12597.ACCOUNTING_PERIOD = 11 and T12597.FISCAL_YEAR = 2012 and T12637.ORG_KEY = T38927.ORG_KEY and T38927.ACCOUNTING_PERIOD = 11 and T38927.FISCAL_YEAR = 2012 and (T12637.DEPARTMENT_CODE_DESC in ('D0200 - Arts Administration', 'D0206 - Film Studies')) ) ),
      SAWITH1 AS (select sum(T38965.ORIGINAL_BUDGET) as c1,
      T12637.ORG_ID_DESC as c2
      from
      GL_ANALYTICS.DIM_ACCOUNT T12582,
      GL_ANALYTICS.DIM_ORG T12637,
      GL_ANALYTICS.DIM_ACCOUNTING_PERIOD T12597,
      GL_ANALYTICS.FACT_LEDGER T38965 / FACT_LEDGER_ytd */*
      where  ( T12582.ACCOUNT_KEY = T38965.ACCOUNT_KEY and T12582.STATEMENT_TYPE_CODE = 'Income Statement' and T12597.ACCOUNTING_PERIOD = 11 and T12597.FISCAL_YEAR = T38965.FISCAL_YEAR and T12597.FISCAL_YEAR = 2012 and T12637.ORG_KEY = T38965.ORG_KEY and T38965.FISCAL_YEAR = 2012 and (T12637.DEPARTMENT_CODE_DESC in ('D0200 - Arts Administration', 'D0206 - Film Studies')) and T12597.ACCOUNTING_PERIOD >= T38965.ACCOUNTING_PERIOD )
      group by T12637.ORG_ID_DESC),
      SAWITH2 AS ((select distinct 0 as c1,
      D1.c1 as c2,
      cast(NULL as  DOUBLE PRECISION  ) as c3
      from
      SAWITH0 D1
      union all
      select distinct 1 as c1,
      D1.c2 as c2,
      D1.c1 as c3
      from
      SAWITH1 D1))
      select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
      D1.c2 as c2,
      D1.c3 as c3
      from
      SAWITH2 D1
      order by c1, c2 ) D1 where rownum <= 65001



      I completely do not understand what is going on here. The second query has a ton of problems. First off, it's doing some type of UNION operation - no idea why it's doing that. And the real problem is that it's unioning results from a different fact table, FACT_LEDGER_period instead of FACT_LEDGER_ytd, which is completely screwing up our results.

      Long and short, I simply don't understand at all why the query should change just from rotating a column up into the table prompt area.

      Any ideas?

      Thanks,
      Scott
        • 1. Re: OBIEE 11g - serious problem when rotating columns into "table prompt" area
          Dhar
          Hi Scott,

          I see the query to be valid with each CTE doing the following Jobs.....

          1. I understand that this particular query is being generated to get the unique "ORG_ID_DESC " to fill up the table prompts.

          SAWITH0 AS (select distinct T12637.ORG_ID_DESC as c1
          from
          GL_ANALYTICS.DIM_ACCOUNT T12582,
          GL_ANALYTICS.DIM_ORG T12637,
          GL_ANALYTICS.DIM_ACCOUNTING_PERIOD T12597,
          GL_ANALYTICS.FACT_LEDGER T38927 / FACT_LEDGER_period /
          where ( T12582.ACCOUNT_KEY = T38927.ACCOUNT_KEY and T12597.ACCOUNTING_PERIOD = T38927.ACCOUNTING_PERIOD and T12597.FISCAL_YEAR = T38927.FISCAL_YEAR and T12582.STATEMENT_TYPE_CODE = 'Income Statement' and T12597.ACCOUNTING_PERIOD = 11 and T12597.FISCAL_YEAR = 2012 and T12637.ORG_KEY = T38927.ORG_KEY and T38927.ACCOUNTING_PERIOD = 11 and T38927.FISCAL_YEAR = 2012 and (T12637.DEPARTMENT_CODE_DESC in ('D0200 - Arts Administration', 'D0206 - Film Studies')) ) )

          2. This particular query is to get each prompt value and its corresponding data

          SAWITH1 AS (select sum(T38965.ORIGINAL_BUDGET) as c1,
          T12637.ORG_ID_DESC as c2
          from
          GL_ANALYTICS.DIM_ACCOUNT T12582,
          GL_ANALYTICS.DIM_ORG T12637,
          GL_ANALYTICS.DIM_ACCOUNTING_PERIOD T12597,
          GL_ANALYTICS.FACT_LEDGER T38965 / FACT_LEDGER_ytd /
          where ( T12582.ACCOUNT_KEY = T38965.ACCOUNT_KEY and T12582.STATEMENT_TYPE_CODE = 'Income Statement' and T12597.ACCOUNTING_PERIOD = 11 and T12597.FISCAL_YEAR = T38965.FISCAL_YEAR and T12597.FISCAL_YEAR = 2012 and T12637.ORG_KEY = T38965.ORG_KEY and T38965.FISCAL_YEAR = 2012 and (T12637.DEPARTMENT_CODE_DESC in ('D0200 - Arts Administration', 'D0206 - Film Studies')) and T12597.ACCOUNTING_PERIOD >= T38965.ACCOUNTING_PERIOD )
          group by T12637.ORG_ID_DESC)


          3. This particular query, I understand is to get the following data (Sample data) relating the prompts and their corresponding data

          column (c1) ;column (c2 ie ORG_ID_DESC); column(C3 ie sum(T38965.ORIGINAL_BUDGET))
          0;               ORG_ID_DESC1; NULL
          1;               ORG_ID_DESC1;10
          0;               ORG_ID_DESC2;NULL
          1;               ORG_ID_DESC2;40
          0;               ORG_ID_DESC3;NULL
          1;               ORG_ID_DESC3;29.8
          .
          .
          .

          SAWITH2 AS ((select distinct 0 as c1,
          D1.c1 as c2,
          cast(NULL as DOUBLE PRECISION ) as c3
          from
          SAWITH0 D1
          union all
          select distinct 1 as c1,
          D1.c2 as c2,
          D1.c1 as c3
          from
          SAWITH1 D1))

          4. The last select statement does nothing but selecting the needed data which later gets arranged in the format as needed by the OBIPS

          select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
          D1.c2 as c2,
          D1.c3 as c3
          from
          SAWITH2 D1
          order by c1, c2 ) D1 where rownum <= 65001

          Now, on the table being chosen for picking up "ORG_ID_DESC" might be pretty much depending on the best source available as you know. If you would like BI Server to pick up any particular source, probably you could try the "LTS Priority Group" way.

          Hope I was clear and it helps.

          Thank you,
          Dhar
          • 2. Re: OBIEE 11g - serious problem when rotating columns into "table prompt" area
            SPowell42
            Hi Dhar - thanks very much for the answer, appreciate it. Here's the underlying issue - in the #1 query, where "1. I understand that this particular query is being generated to get the unique "ORG_ID_DESC " to fill up the table prompts."

            This is exactly what it's trying to do - but compare the fact table it uses (fact_ledger_period) vs. the fact table used by the actual data query (query #2 - it's using fact_ledger_ytd). So the problem is that query #1 isn't returning the same set of orgs that the actual data query (query #2) does, and this is completely messing up the results.

            Unfortunately, I can't "prioritize" the LTS values....because it depends on what fact table the main data query (query #2) uses. If the end user asks for period only values, then query #1 would be correct as is. But if the end user asks for YTD values, then the fact table in query #1 is wrong.

            Long and short - the fact table being used to generate the table prompt values needs to be exactly the same as the fact table used to get the data. This isn't happening, and I simply don't understand why.

            p.s. one additional note, I became concerned about the LTS issue yesterday, so in the RPD I changed the model. Instead of using two aliases of the fact table and different joins to the time dimension, instead I just used a single fact table (i.e. a single LTS) and calculated the YTD values using the standard OBIEE TODATE function. However, this still has the EXACT same problem - the query for the prompts still didn't use the YTD logic, and was still returning the wrong set of possible values.

            This is very frustrating. I simply don't understand at all why there has to be a separate query to get the drop down values...just use the values that the main data query returns???

            Thx,
            Scott
            • 3. Re: OBIEE 11g - serious problem when rotating columns into "table prompt" area
              Dhar
              Hi Scott,

              I am sorry, If I got you wrong here but I am trying to understand how picking up different sources at the same grain might make a difference.

              If the "ORG_ID_DESC" from "fact_ledger_ytd" is not same as "fact_ledger_period" then, the "fact_ledger_ytd" might be at a different grain altogether, which we might be able to resolve using the 'Content' tab way, Am I correct to say that.

              However, for this issue how about picking up another measure which can be sourced only by "fact_ledger_period" and later be hidden in the report?

              Hope this helps and my sincere apologies If I am misunderstanding your model.

              Thank you,
              Dhar
              • 4. Re: OBIEE 11g - serious problem when rotating columns into "table prompt" area
                SPowell42
                Hi Dhar - the two fact tables are at the same grain. But here's the difference: FACT_LEDGER_PERIOD is used to report a single period (aka month). For example, if you pick June 2010, it reports the values for the month June 2010. The FACT_LEDGER_YTD does a year to date calculation, so choosing June 2010 shows the sum of Jan 2010, Feb 2010, ... June 2010.

                And that's what's causing the issue. I have orgs in Jan 2010, Feb 2010, etc. that do NOT have values in June 2010, so there are no rows in June 2010 for them. When the OBIEE server runs query 1, it's only getting orgs that have values in June 2010. But the data query is getting values for orgs that have values in ANY of the months Jan 2010 thru June 2010.

                So here is a concrete example: Org A has a value of $10 in January 2010, and that's the only record for it, no records in Feb, March, etc. The fact amount for YTD Actuals in June 2010 is also $10, because that's the sum of all values up to the month of June.

                But when the OBIEE server runs the first query, it doesn't see Org A...because it's using the FACT_LEDGER_PERIOD table, which is NOT the fact table that the data query uses. So Org A doesn't show up in that query (or the final results) - because it has no record for June.

                Hope this is clear, one of those things it's easier to show than describe on a forum.

                THx,
                Scott

                p.s. and as I mentioned in a previous post, even if I get rid of having multiple fact tables and use the OBIEE TODATE function to calculate the YTD results...It's still querying for the drop downs on the month of June only, so that isn't working either.

                Thx,
                Scott
                Scott
                • 5. Re: OBIEE 11g - serious problem when rotating columns into "table prompt" area
                  SPowell42
                  So I'm working with Oracle on this issue, and we've found something very interesting. If I use a pivot table to output the results, everything works as I expect (i.e. a single query is fired to the database to get the result set, and then it's reused with presentation caching to generate the dropdowns as columns are rotated around). However, if I use just a standard table, that's where funky queries are getting generated that are not correct. Will post an update as I get more information.

                  Scott