Modeling for Service related facts and dimension (for Tour Operating Company)

View previous topic View next topic Go down

Modeling for Service related facts and dimension (for Tour Operating Company)

Post  shajeerkp on Wed Jul 29, 2009 11:56 pm

I am currently doing a dimensional model design for a Tour operating company. Their business is like selling hotels rooms, flights, and excursion to customers. All these are called SERVICES.

Now I am designing dimension and fact table to hold bookings of services. Each service has different measures and dimension.

For example,

1) Hotel room service have room type, number of nights booked, room rate, etc.

2) Flight has starting city, ending city, flights duration etc

3) Excursion has pickup point, drop off point etc

My doubt is how I should design service dimension and fact table.

Whether

A) There should be multiple dimension and fact table to hold each service?

B) There should be single dimension and fact table to hold all the services booking by providing multiple columns for each service in single table?

The problem with the approach A is multiple joins would require getting all the services for each booking and is not the standard one

The problem with the approach B is lot sparse column will appear in dimension and fact table (That is some excursion attribute are not feasible for flight services etc).

Please advice.

Thanks in advance
avatar
shajeerkp

Posts : 3
Join date : 2009-07-29
Location : Dubai

View user profile

Back to top Go down

Re: Modeling for Service related facts and dimension (for Tour Operating Company)

Post  BoxesAndLines on Thu Jul 30, 2009 11:20 pm

What, if any, dimension is common to all services? Do you need to combine reporting across services or does each service "stand" on its own? Answers to those questions will drive your design.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Modeling for Service related facts and dimension (for Tour Operating Company)

Post  shajeerkp on Sat Aug 01, 2009 11:46 pm

Service Type, Service and Service Categoey are common dimension among these services.
We have seperate anlysis for each of these services and also combined analysis for these services
avatar
shajeerkp

Posts : 3
Join date : 2009-07-29
Location : Dubai

View user profile

Back to top Go down

Re: Modeling for Service related facts and dimension (for Tour Operating Company)

Post  ngalemmo on Mon Aug 03, 2009 12:17 pm

Generally, I would lean toward option B. However, if the dimension is extraordinarily large, I go with option C... subtype dimension tables.

Under option C, there would be a primary dimension table with attributes that are common to all services... ID, type, description, etc..., then there would be companion tables for types of services based on their attributes. The primary table and the subtype tables share the same primary key value, so this is not a snowflake and the fact only carries a single foreign key.

Non-specific queries would only use the primary table, while queries for a specific type of service would use one of the companion sub-type tables. Since the subtype table only contains rows for particular types of services, using the sub-type table explicitly filters the fact table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Modeling for Service related facts and dimension (for Tour Operating Company)

Post  shajeerkp on Tue Aug 18, 2009 3:03 am

Thanks, Option C is also a very good choice, let me complete this design and then I will talk about my experience here
avatar
shajeerkp

Posts : 3
Join date : 2009-07-29
Location : Dubai

View user profile

Back to top Go down

Re: Modeling for Service related facts and dimension (for Tour Operating Company)

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