6 Replies Latest reply on Dec 22, 2016 6:36 PM by 2828715

    Additonal where clause filters in OBIEE query

    2828715

      Hi,

      I have a case where Dim A and Fact A are joined using a complex join in the physical layer

      Dim A :  ColA ColB StartDateId EndDateID

      Fact A: ColA ColB ActivityStartDateId ActivityEndDateID Metric1

       

      Both are joined as

      Fact A left outer join Dim A on Fact.ColA=Dim.ColA  and Fact.ColB=Dim.ColB and Fact.activitystartdateid between Dim.StartDateId and Dim.EndDateID

       

      Everything works fine but the problem comes up when I introduce a filter say Fact.ColA='ABC' then the OBiEE is generating another additional filter DimA.ColA='ABC' which is negating the outer join. Pls suggest

        • 1. Re: Additonal where clause filters in OBIEE query
          Christian Berg

          Is Fact.ColA an attribute? If yes, then that belongs into a degenerate dimension and not into the fact table.

          • 2. Re: Additonal where clause filters in OBIEE query
            2828715

            Fact.ColA is an attribute so I have split up the degenerate dimension columns from physical fact table in the logical layer, leaving only the metrics in the logical fact and all attributes into logical dimension.

            • 3. Re: Additonal where clause filters in OBIEE query
              Christian Berg

              Physically this is represented how?

               

              Alias or are you using the same object?

               

              Plus if you already split that...why do you say you filter on the fact? You'll be filtering on the dimension anyways if you have done what you said above.

              • 4. Re: Additonal where clause filters in OBIEE query
                2828715

                In the physical layer its just one single alias table, the split to dimension and fact is done only in the BMM layer. The reason I am doing this is I do not have a primary key on the fact table and all I have is a couple of business keys, so if I have to do this in Physical layer, it creates the burden of a fact to fact join over these columns,

                • 5. Re: Additonal where clause filters in OBIEE query
                  Christian Berg

                  Ok but that still doent explain your contradicting statements:

                   

                  1) "when I introduce a filter say Fact.ColA='ABC'"

                   

                  2) so I have split up the degenerate dimension columns from physical fact table in the logical layer, leaving only the metrics in the logical fact and all attributes into logical dimension.

                   

                  So how can you do this filter if you have properly put attributes into a degen dimension?

                  • 6. Re: Additonal where clause filters in OBIEE query
                    2828715

                    Alright sorry for the confusion.

                     

                    1) "when I introduce a filter say Fact.ColA='ABC'"

                       When I say Fact.ColA='ABC'" its actually the physical column thats coming from the fact table, but in Presentation layer and BMM layer its DegenreateDim.ColA

                     

                    To summarize I have taken an alias of the fact table say Fact_A, created all the joins in Physical layer.

                    IN BMM the Fact_A has been split up into Dim A and Fact A with attributes and metrics.

                     

                    Dim B :  ColA ColB StartDateId EndDateID

                    Fact A: ColA ColB ActivityStartDateId ActivityEndDateID Metric1

                     

                    Both are joined as in physical layer

                    Fact A left outer join Dim B on Fact.ColA=Dim.ColA  and Fact.ColB=Dim.ColB and Fact.activitystartdateid between Dim.StartDateId and Dim.EndDateID

                    IN presentaion I am filtering out on DimA.ColA='ABC'

                     

                    the problem is with the additional filter on DimB.ColA interoduced by OBIEE which I am not issuing