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

[shajeerkp]

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

[BoxesAndLines]

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.

[shajeerkp]

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

[ngalemmo]

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.

[shajeerkp]

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

[Sponsored content]