Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Timespan Transaction Fact

4 posters

Go down

Timespan Transaction Fact Empty Timespan Transaction Fact

Post  Noslaner 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

Back to top Go down

Timespan Transaction Fact Empty Re: Timespan Transaction Fact

Post  manickam 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

Back to top Go down

Timespan Transaction Fact Empty Timespan Transaction Fact

Post  zoom 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

Back to top Go down

Timespan Transaction Fact Empty Re: Timespan Transaction Fact

Post  manickam 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

Back to top Go down

Timespan Transaction Fact Empty Re: Timespan Transaction Fact

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

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

http://aginity.com

Back to top Go down

Timespan Transaction Fact Empty Re: Timespan Transaction Fact

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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