Data Warehouse schema design - how to improve schema model

Question

I have to create Data Warehouse for travel agency. I'm doing it for the first time. I've learned all the basics about star, snowflake and constellation schema and about creating data warhouses. I would like to ask what could be changed for better and if this design is good overall.

Here are my dimensions hierarchy:

Here is what I've achived for now (creating schema in MySQL Workbench):

Answer 1

Here's a new answer based on the revised question. There are a number of things you might want to look at for this design. Here's a few pointers but not a complete list:

• What granularity is your DimTime dimension supposed to be? Normally you have a date dimension at the day/date granularity, but in your table it looks like weeks.

• You could create a separate time of day dimension if that is important for analysis of when sales or satisfaction reviews were.

• The loyalty fact seems to be a summary of customer behaviour over a time period- is that supposed to be weeks? If so you could go for an extra dimension at the week level

• Why does payment type have seconds of the day in it? That doesn't seem right- payment types aren't to do with seconds in a day. Perhaps this is your missing time of day dimension, and payment type should be separate?

• Should the product dimension have a regional hierarchy? Are you saying a product is different if it is in a different City? You might want to look at that again.

I'm sure other suggestions could be found, good luck with your course!