Events in dimensional model

View previous topic View next topic Go down

Events in dimensional model

Post  nba411 on Wed Jul 10, 2013 12:08 pm

Hi was wondering if someone could point me in the right direction.

How would I model an event status in the dimensional model? In my case, I'm looking at rental cars. Each rental car has a status of rented, returned, on hold, damaged, etc. A car can be rented more than once and a car could be

I have a these dimensions:

1. CAR dimension with all appropriate attributes about the car: color, make, model, year, etc
2. DATE dimension: day, year, month, etc.
3. PERSON dimension: Name, address, Drivers license, etc
4. STORE dimension: rental branch name, city, state, zip, etc. I'll create a role play off of this to make a return and originating store.

IF I want to know how often a car is being rented, whether one branch (store) is renting more then the other, and whether the car is currently rented or not

Do I need a separate EVENT dimension? Something with maybe the status of each car at a particular point in time or is that something that is reflected in the dimension? or do I just enter a '1' in a fact table for every time the car is rented and count those up? Confused.

Any input would be appreciated.

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Events in dimensional model

Post  BoxesAndLines on Wed Jul 10, 2013 2:10 pm

I would go accumulating snapshot here. One row per rental with the different statuses. Here's a Kimball Tip, http://www.kimballgroup.com/2002/06/13/design-tip-37-modeling-a-pipeline-with-an-accumulating-snapshot/
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Events in dimensional model

Post  nba411 on Wed Jul 10, 2013 3:05 pm

Thanks for the link, that was great!

So in that example, they are essentially just adding a '1' or '0' for every step completed to query the questions on how many people are at each step of the "pipeline" What is the Admissions Decision dimension referenced in the fact table for? Would this be a small dimension with preset values similar to 'ACCEPTED' , 'DENIED', or 'PENDING' in them and then referenced by the fact table?

Where can I find out more about the lags? I don't recall reading that in the book. Is the lag just a number of days in these case between the phases?

Thanks again.

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Events in dimensional model

Post  BoxesAndLines on Wed Jul 10, 2013 6:37 pm

Yep, lags are days between events. If you use views, I normally add these there since it is just simple addition.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Events in dimensional model

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