1 Reply Latest reply on Feb 21, 2017 12:12 PM by Gianni Ceresa

    the value will be filtered twice

    User472204-OC

      Hi Experts,

      I try to create the relationship between Dimension tables(AGENT/CALENDAR) and Fact tables (DIGL_ORD) in RPD,after I filter data in dimension table in analytics and see the SQL generated by OBIEE Analytics, I find the strange phenonmenon that the value will be filtered twice (Both dimension and fact table will filter the value,Why?)
      I think only the dimension table will filter the values,right?
      Because the dimension table is inner join the fact table and the filter value is in dimension table.

      SQL_TWICE.JPG

        • 1. Re: the value will be filtered twice
          Gianni Ceresa

          Hi,

          You link between Dim Agent and Fact Digital Order is AGNT_USER_ID = AGNT_USER_ID, so filtering the AGNT_USER_ID on both tables gives you the exact same result as filtering only the dimension table and then let the inner join make the implicit filter on the fact table.

          By filtering both tables OBIEE tries to generate a better query.

           

          But as said the result doesn't change at all.

          Of course if you would set the join as an outer join you wouldn't see the same result (physical query) depending on the kind of join you selected.

           

          So in theory you are right: in the dimension model you setup in the business layers filters happen on the dimensions, in practice OBIEE does what it guess being a better query as it doesn't change the result but can maybe hit a different index or something improving the query.

           

          You can also see that this happen only when the filter is on the join column, if you look at your Dim Calendar the filter happen on the dimension only because in your analysis you filter a column being different than the one used by the join.