none
Fact table Scd Type2 RRS feed

  • Question

  • Hi All,

    I am designing a warehouse for a property management system. We have properties and Occupants, each property can have multiple suites.
    A suite can be vacant(0) or Occupied(1).

    Lets say I have a fact with two dimension.

    Property
    PropertyID PropertyName
    1                      P1

    Occupant

    OID           OccpName                OccpStart              OccpEnd                    OccpStatus
    1                   O1                         1/1/2011              5/1/2012                         1

    2                   O1                         5/2/2012               6/30/2012                      0

    3                   O1                         7/1/2012                   NULL                          1


    FACT

    PID          OID            OccpStart      OccpEnd     OCCStatus 

    1               1                  1/1/2011      5/1/2012         1

    1               2                  5/2/2012      6/30/2012       0

    1               3                   7/1/2012       NULL              1

    So my question, how should I design my cube if a user selects a date 5/1/2012 I should see the "FIRST RECORD" i.e OID 1 from the fact table? 

    If he select 5/15/2012 I should get only the "SECOND RECORD" i.e OID 2.

    Can you please help me?

    Thanks in advance.

    Tinku





    Monday, August 27, 2012 11:31 PM

All replies