Data Warehouse schema design - how to improve schema model

0
votes

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:

enter image description here

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

enter image description here

2
mysqlmysql-workbenchdata-warehousestar-schemasnowflake-schema
Can you be more specific about which parts of this you're having difficulty with? Do your diagrams show all your fields in those dims? Also I don't think payment type should really include year/month/day/hour/minute – Rich
I'm not a big fan of sub-dimensions. Once you start to introduce these the database design quickly resembles the standard OLTP. This removes many of the benerfits offered by a star schema. An exmaple: data via visualisation tools (like PowerBI, QlikView, etc) prefer the flatter dimension tables, recommended by Kimball. – David Rushton
Would like to point out that the first two comments are based on a previous version of the question. – Rich

2 Answers

1
votes

To take DimClient as an example. You have a nice surrogate key in there. Next you need to fill in all the things about a client (including the clientID) and then also include district, city, region and country. When you have all that in there, that dimension is complete.

You link to it in your Fact table by the ClientKey, so you need to put that Key in the Fact table as a foreign key.

Go through a similar process with your other dimensions, filling out both the dimensions and facts, and you'll be in a good shape. You don't need subdimensions to reflect your hiearchies: dimensions are denormalised.

Edit: The question was originally quite different, hence the answer above which was relevant to its original form.

0
votes

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!