Snapshot fact table with Type 2 SCD

• Matt Frowe

  SSC Enthusiast

  Points: 121

  More actions

  August 13, 2014 at 8:04 am

  #289039

  Hi all,

  I'm looking for some advice from someone more experienced than me on how to handle a particular scenario in my DW.

  My fact table is a snapshot table which is counting/summing various facts at a particular point in time. This is joined to a dimension called 'session' which can be considered a time window within which events must happen. The basic structure can be simplified down to:

  Fact table

  SessionKey (FK)

  SnapshotDateKey (FK)

  Fact1

  Fact2

  FactN

  ....

  DimSession

  SessionKey(PK)

  SessionAlternateKey

  SessionOpenDateKey(FK)

  SessionCloseDateKey(FK)

  SessionStatus

  IsCurrent

  Example session data - current date is 25th August 2014

  3311

  10345-5

  20140801

  20140831

  Open

  True

  Example session data - current date is 1st September 2014

  3312

  10345-5

  20140801

  20140831

  Closed

  True => Set to False for Sessionkey 3311

  My business requirement is to be able to query/report on what proportion of Facts were completed within the open and close dates of the session. For example, for a session a customer placed an order for 10,000 blue widgets. My snapshot table keeps track on a daily basis from the session open date how many have been shipped until the order is completed (note - this may be beyond the session close date). Once the close date passes I need a way of flagging that subsequent shipments were 'late' i.e past the session close date, but they must be considered part of the same session.

  At the moment I'm generating a SessionStatus column during the ETL process that carries out a DATEDIFF comparison and determines whether the window has expired or not. The column is then populated with 'Open' or 'Closed'. There is then a SCD transformation monitoring that column for changes, and when one is found the current row is marked as expired and a new row inserted into the dimension table with a new PK (SessionKey) and IsCurrent=True. The previous row has IsCurrent marked as False. The Fact table load selects the SessionKey where IsCurrent=True.

  My problem is that by adopting this model, the 'sessions' are now considered uniquely different in the DW. I can't find any way of getting an overall view, from SessionOpenDate onwards, of the same individual session.

  I also considered adding the SessionStatus column in the Fact table, but then I can't slice by it in SSAS (at least as far as I know). Besides, instinctively this seems wrong as whether the session is open or closed is an attribute of the session, so belongs in the session dimension.

  Can anyone offer any advice on the best way to tackle this issue? I hope I've managed to explain it clearly enough but if I can provide any further info just let me know.

  Thanks

  Matt