0 Replies Latest reply: Apr 22, 2014 2:01 AM by user1743824 RSS

    using ACTIVE_DATE in CUBE ( Bug 16601554 MISSING CODE CLAUSE USING ATTRIBUTE ACTIVE_DATE IN A CUBE FOR SCD2 DIMENSION)

    user1743824

      Hi,

      I'm using OWB 11.2.0.3.

      I want to fill a cube with some SCD Type 2 Dimensions. I want to use ACTIVE_DATE in my cube to match the best row in my Dimension.

      In official document, it is written that the ACTIVE_DATE value is used like this:

      ...

      WHERE

      (...

         (<dim_name>.DIMKEY = <lookup_for_dimension_dimkey> AND

                 (<level>_EFFECTIVE_DATE <= ACTIVE_DATE AND

      <level>_EXPIRATION_DATE >= ACTIVE_DATE) OR

                 (<level>_EFFECTIVE_DATE <= ACTIVE_DATE AND

                          <level>_EXPIRATION_DATE IS NULL))

      ...)

       

      But the generated scripts is like this:

      (EXPIRATION_DATE IS NULL)

      OR

      (EXPIRATION_DATE IS NOT NULL AND ACTIVE_DATE BETWEEN EFFECTIVE_DATE AND EXPIRATION_DATE)

       

      At the first look, these 2 scripts look the same, but they are not! Consider the situation when we have 2 records with the same business key in our dimension, obviously one of them has no EXPIRATION_DATE. But when the ACTIVE_DATE is between EFFECTIVE_DATE and EXPIRATION_DATE of the other record, BOTH records will pass this condition.

      Please help me find solution to this bug.

      rasoul_z@yahoo.com

      Thanks