0 Replies Latest reply: Aug 27, 2012 5:07 AM by Marius2 RSS

    Slowly Changing Type 2 giving me unexpected results

    Marius2
      Using: OWB 11.2.0.3.0

      I have made a dimension with three levels: top, mid, low. Each of the level has one attribute triggering history. (This thing is created for test purpose since it did not work when I tried to use it).

      Just looking at the history triggering attribute, the first row I insert is:
      top = 1
      mid = 2
      low = 5

      Then I load with a change at 13:33:12
      top = 1
      mid = 3
      low = 5

      Then I load with a change at 13:33:33
      top = 1
      mid = 3
      low = 6

      Then I load with a change at 13:33:53
      top = 1
      mid = 4
      low = 6

      I would expect that looking at the low row I should be able to go in at any time in history and find exactly the state it had then. But here is what I get

      Looking at the row for low, where 00:01:00 (my default is 00:00:00) is between effective and expiration date for the low level I get:
      top = 1
      mid = 3
      low = 5

      The mid is here wrong, the first row I inserted was 1,2,5 and it was not changed until 13:33:12. So even though I'm looking for what was valid at 00:01:00 I get a row that was only valid from 13:33:12

      Looking at next change using for instance 13:33:13 I get
      top = 1
      mid = 3
      low = 5

      which is what I expect to see.

      Then looking at the next one I get the same mistake as for the first one. The time I use is now 13:33:34:
      top = 1
      mid = 4
      low = 6

      again mid is wrong with a more recent value than the one that was valid at the requested time.

      Finally if I look at the newest entry I get the expected result of
      top = 1
      mid = 4
      low = 6

      In conclusion it looks like the value of mid, for the low row is overwritten with each change of mid. I would have expected that the row would be closed and a new row created with the appropriate mid value for a low row.

      Is this the way it is suppose to be working? Is there any workaround that make a change to a higher level row properly close the lower level row and create a new lower level row?