Timespan Transaction Fact

View previous topic View next topic Go down

Timespan Transaction Fact

Post  Noslaner on Thu Sep 03, 2015 9:56 pm

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?

Noslaner

Posts : 1
Join date : 2015-09-03

View user profile

Back to top Go down

Re: Timespan Transaction Fact

Post  manickam on Fri Sep 04, 2015 2:54 am

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..

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Timespan Transaction Fact

Post  zoom on Fri Sep 04, 2015 8:51 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Timespan Transaction Fact

Post  manickam on Mon Sep 07, 2015 12:37 am

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

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Timespan Transaction Fact

Post  ngalemmo on Mon Sep 07, 2015 6:35 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Timespan Transaction Fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum