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

Events in dimensional model

2 posters

Go down

Events in dimensional model Empty Events in dimensional model

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

Back to top Go down

Events in dimensional model Empty Re: Events in dimensional model

Post  BoxesAndLines 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/
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Events in dimensional model Empty Re: Events in dimensional model

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

Back to top Go down

Events in dimensional model Empty Re: Events in dimensional model

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

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

Back to top Go down

Events in dimensional model Empty Re: Events in dimensional model

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