datawarehouse modelisation

[joe69008]

hi everybody,

Please i need help for modelising a datawarehouse.
I am creating my first datawarehouse for a rent car company during my training period.
after analysing their needs, i select several measure that is :

-> number of car put in the park
(by month, by agence, by categorie and by type of car)
-> number of car Available
(by month, by agence, by categorie and by type of car)
->Day number of car located
(by car, by month, by agence, by categorie and by type of car)
->Day number of car detention
(by car, by month, by agence, by categorie and by type of car)
->Rate of use (Day number of car detention/Day number of car located )

->turn over
( by month, by agence, by categorie , by type of car, by type of client, By contract charged )

--> ...

Someone can he confirm me that i must create several table fact because certains measures can't be put together. that true that this question seems obvious but as i am never create datawarehouse, i don't want do a bad one.
So We do not have to hesitate to create as much facts tables than there is different measures? that true or logic instead. and so that will be several datamart because a datamart contains only one table fact?

 

[joe69008]

hi

Has anyone here designed enterprise data warehouse dimensional models for rent of car?

Best regard

 

[johnherman]

There is only one base fact table, but there could be several aggregation tables built around it for performance reasons.

Dimensions are
Time (used at least two times)
Agency (name, location, etc)
Vehicle (year, make, model, type, location or agency, VIN)
Client (name, address, license, type)
Contract (contract holder, address, etc)


As I see it, the only fact table is the Vehicle Rental.

Vehicle Rental:
Date Rented Key
Date Returned Key
Agency Key
Vehicle Key
Client Key
Contract Key
Rental Revenue
Discount Amount - if needed
Insurance Amount - if needed
Taxes Due - if needed

If a vehicle is not rented, we assume it is available. Unless you need to track vehicles out for repair or maintenance which would be another fact table.

You then would create summary/aggregate tables, probably by month as you have mentioned month several times. The customer has indicated what they think their Key Performance Indicators (KPI's or Scorecard Categories):

Cars available, rented, rate of use, etc.

Hope this helps getting you started.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[MDXer]

If the vehicle is out for maitnance and needs to be tracked you could do this within the same fact table by the Addition of a Car Status dimension

Members would be
Rented
Available
Maintance
Etc.

This key in the fact would also allow you to get a quick count of cars either rented or available, the revenue values would be either 0.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[joe69008]

thank you very much guys for your help.

Please john , can you tell me,according your model that seems very good and thanks you for that, how i could calculate the number total of vehicule Available (rented and no rented) by agency and by month, of course i add in dimension vehicule the attribute Date of exit and date of entry. So i could have a resultat only in request the dimension vehicule because i can't put in fact table the indicator number of vehicule available. and so how i can manage to have for example all vehicule available for an agency X by month for the years 2004. can i do that with aggregate table?

its the same with number of new vehicule that entry in agency by month. i can use also only the dimension vehicule withe the attribute date of entry and date of exit

sorry but i never use table agregate and so i don't see how that it works.

as for the dimension time, so i should created 2 dimension, one for Date Rented and the other for Date Returned, it's that?

thank you again

I hope not to have been too vague

best regards

 

[johnherman]

I can only guess at what you mean.

Does Date of Entry and Date of Exit pertain to the rental time or to when the vehicle was placed in or removed from service (joins or leaves agency's rental car fleet)?

If pertains to rental, it's already in the Rental Fact as the Date Rented and Date Returned. If it pertains to a vehicle's being place in service, it belongs int he Vehicle dimension.

I guess that available days are the total days in the month multiplied by the total vehicles, less any days which vehicle was not available (holiday, maintenance, repair, did'nt join fleet until 15th of month, etc).

As for aggregates and summary tables, alot will depend on your platform. What are you using? Oracle? Oracle OLAP? Hyperion? Microsoft? Cognos? etc.
Some platforms calculate counts and sums for you without your asking. Some you need to program.


-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[ManneetSingh]

John,
Sorry if I am digressing from the topic. I had a question related to your last post -
"If it pertains to a vehicle's being place in service, it belongs int he Vehicle dimension."

Is it normal to do metrics from dimensions only? For example, if I want to find out how many vehicles did an agency have at its disposal in any month, nothing prevents me to just go to vehicle dimension and use the Entry date and exit date to come up with metric.

I have done this many times in the past (doing metrics from dimensions only), but always wondered if this is what a pure star schema preaches?

Another example would be number of customers a company had in a month. I can easily do this metric from the customer dimension itself without creating a separate fact and then linking to dimension in order to maintain a true star schema.

 

[johnherman]

There is absolutely nothing wrong from getting your query answer from a single table rather than from the (joined) star schema.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[joe69008]

john, thanks again for your help , I am very grateful to you for your help.

as for Date of Entry and Date of Exit, it pertains to a vehicle's being place in agence, so according to you, it will be in Vehicle dimension ans so when i want to know for a month or for several month how car was available for one agency, i can request only this table, can you confim please? and so i can create agregrate table only on this dimension if i want the number of all car available by agency for all month of several years?

i will going to use MS sql server 2000 with analyses services and does it do itself the agregate tables?

thanks a lot

 

[johnherman]

Microsoft Analysis Services is good with generating counts and aggregates.

Upon further review, it appears you need a bridge (or relation) table between Vehicle and AGency, since a Vehicle could belong to more than one agency during that vehicle's lifetime in rental service. This bridge table would look like this.

VehicleAgencyAssignment
VAAKey (a unique identifier)
VehicleKey
AgencyKey
EntryDate
ExitDate

An alternative would be to make vehicle a type 2 (Slowly Changing) dimension. Using this solution, the Vehicle dimension would look like this.

Vehicle
VehicleKey (a unique identifier)
VehicleMake
VehicleModel
VehicleYear
VehicleVIN
.
.
AgencyAssigned
RecordStartDate
RecordEndDate

Each time anything about the vehicle changes, a new dimension record is produced. This method is a little less efficient than the first since the vehicle Make, Model, Year, and VIN are NEVER going to change, so you are only tracking the Agency changes. If, however, you had additional info about the vehicle which might change (such as vehicle color, assessed value, or mileage), then the slowly changing dimension for Vehicle is the better solution.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[joe69008]

ok thanks you john,

i will ask you a last question, for example if i want to know the number of all car available by agency and for all month of several years?
how i can do that? with table agregate link with dimension time? with only one " decisionnel Request "? by create a cube on only one dimension table or bridge table? ( it is possible?)


thanks

 

[johnherman]

Whichever of the two solutions I outlined above can provide the raw data you need to answer the question.

Based on earlier remarks, I firmly believe you need one or more monthly summary tables or monthly summary OLAP (Analysis SErvices) cubes. Either way, they should be able to provide you with those counts.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[joe69008]

ok, thanks a lot for all your help