6 Replies Latest reply: Aug 26, 2013 2:03 AM by user007009 RSS

    OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion

    user007009

      Hello everyone

          I have a Degener@teDimen$ion Dimension A it has 4 LTS ( Fact1, Fact1A ,Fact2 and FACT2A).--> FACTS from

      Physical Layer.. FACTS in BMM are FACT_ONe and FACT_TWO.

       

       

      columns in Degener@teDimen$ion Dimension A

      col1 --> mapped to all 4 LTS(logical source). It is the key column.

      col2 --> mapped to all 4 LTS(logical source)

      col3 --> mapped to all 4 LTS(logical source)

      The content Tab of the LTS for this Dimension are mapped to Detail of

      Dimension Hierarchy name is DDimension A

       

       

      Dimension Hierarchy name is DDimension A, Levels in it are

      Total --> This has all the fact columns from FACT1 and FACT2. Basically dragged dropped from facts to

      this level.

      & Detail ---> It has col1,col2 and col3 and Degener@teDimen$ion Dimension A.

       

       

      FACT_ONE --> Fact1, Fact1A are LTS for this fact 

      FACT_TWO --> Fact2 and FACT2A are LTS for this fact.

       

       

      When i am doing a analysis with

       

       

      Year, Degener@teDimen$ion_Dimension A_COL2 , FACT_ONE.measure1, FACT_TWO.measure1

       

       

      It is basically showing the dimension value of FACT_TWO.measure1 only...

      even the select statement is not having column from FACT_ONE...

      Like

      select x,y, col2 from fact_two full outer join select x,y from fact_one...> I just provided this

      syntax just for an idea.. the this fact_one is not having that column in the select..

      I referred this link

      "http://www.rittmanmead.com/2010/01/oracle-bi-ee-10-1-3-4-1-modeling-degenerate-dimensions-fact-attributes/"

      and others but i am not able to find the reason why it is not issuing a select...

       

       

      Just to add more in the Total level of the hierarchy, if i added FACT_TWO only removed FACT_ONE measures..

      it is showing only fact_one value (this time the FACT_ONE SELECT is having col2 but not FACT_TWO)...

      the behaviuor is vice versa as well...

        • 1. Re: OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion
          user007009

          Hi --- Can some through some light into my  modelling of "Conforming Degener@teDimen$ion" did any one came across this issues .... or is it bug?

          • 2. Re: OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion
            Dhar

            Hi,

             

            I have been trying to go through your problem for couple of times now, but could not grab the issue. I know you have been trying to keep it simple, but would you mind posting it some other way for easier understanding. I am sure I could be of help here.

             

            Dhar

            • 3. Re: OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion
              user007009

              hi thanks a lot for your reply... the issue i am having is... when i have all the measures from all facts included in "Conforming Degener@teDimen$ion" totals level and using that dimension value on the report, it is picking  data from one of the fact table only.. please let me know if this helps or else will find another way...

              • 4. Re: OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion
                user10615659

                your dimension should have two logical table source. It has only one. You need to create one dimension source joining for Fact1 and other for fact 2.

                Right now the dimension has only one LTS. Hence you are seeing a one of the fact measures as null as other cannot participate in the query.

                If you have two LTS in dimension, the OBIEE query engine will try to stitch the queries from 2 stars.

                • 5. Re: OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion
                  user007009

                  hi -- thanks a lot for your response... actually the dimension that i have is a fact based dimension like order_id (this column exists in both the fact tables). This dimension has both the fact tables included in it's LTS.

                   

                  FACT_ONE --> Fact1, Fact1A are LTS for this fact

                  FACT_TWO --> Fact2 and FACT2A are LTS for this fact.

                  Degener@teDimen$ion Dimension A it has 4 LTS ( Fact1, Fact1A ,Fact2 and FACT2A)

                     Reason for not able to split the dimension is that the we end up having a ORDER_ID dimesions twice in the Subject area one that maps/works with FACT_ONE and the other that maps/works with FACT_TWO.

                     Is it possible to have them split in BMM and have one ORDER_ID dimension in SUbject area and when we do the analysis, it refers to both the fact tables and get the data.. really appreciate your time and thoughts.

                  • 6. Re: OBIEE not selecting columns from all LTS of a Conforming Degener@teDimen$ion
                    user007009

                    I resolved this. The Issue is, when using Degener@teDimen$ion ( this is !nner joned to FACT tables in BMM) and if any of the dimensions {other than the Degener@teDimen$ion (Let us say Dim X) } have an ()uter join to any of the fact tables, and you were doing your analysis using Degener@teDimen$ion,  Dim X, Measure value you will face the following issues.

                    when filtering the analysis on the ()uter join dimension ( Dim X), the IN filter will not work. Reason is that the filter is getting applied to both the Dimension and FACT tables and the values that exist in Dimension Dim X but not in FACT table wont show up.

                         The above issue can be fixed by changing the join between the fact and Degener@teDimen$ion from inner to outer. I think this is a bug.. because  it is supposed to filter the fact  table after the entire outer joined result is obtained but not filter the fact table for the Dim X values and do a outer join. I think the BI should be intelligent enough.

                         In order to solve the filtering issue what i did was mentioned in my Initial post. I think if we have the measures at the Totals level of dimension, it is picking the Degener@teDimen$ion,from the table that has less number of rows or the query from one of the two facts that gets submitted to database.. I couldn't find the relevant information in the oracle document though . . . .