Timespan Transaction Fact

[Noslaner]

I have customers who spend time with our business, staying in different rooms for different periods of time and in different locations.
I want to track how long customers spend with us at a time, and which rooms at which locations they stayed in (and how long they spent in each room).
I was thinking of using a Timespan transaction fact, but am unsure if I need a record for any period of time the customer is not staying with us?

[manickam]

Below would be the dimensions:
1. Customer
2. Date
3. Room (rolls up to location)

Fact table granularity would be all the above dimensions along with start and end time..

[zoom]

Join customer dim to the Fact table to find those customer who stayed UNION that result and do outer join on customer dim to Fact table to find those customer who did not stay.

[manickam]

Do a left outer join having customer dim on the left hand side.

[ngalemmo]

If you need to find gaps in the stays, use either a LEAD (or LAG) windowing function to look at two stays. The time between the check out of one stay and check in of the next would be the 'not staying' time.

[Sponsored content]