Product Dim for Car manufacture

Hi,

Im designing a Product dimension for car manufacturing company and fairly new to dimensional modelling.

Any help is much appreciated

The data we have is

Model BodyType Engine Fuel
D 2 Door 2 D
D 4 Door 2.2 D
D 5 Door 2.4 D
D 2 Door 2 P
D 4 Door 2.2 P
D 5 Door 2.4 P
E 2 Door 2 D
E 4 Door 2.2 D
E 5 Door 2.4 D
E 2 Door 2 P
E 4 Door 2.2 P
E 5 Door 2.4 P

The plan is to create a star schema but if i consider model as my grain there are other attributes which will define the model in the source data and becuase of the this my model will repeating many times in the table

Do i need to snoflake the Bodytype and Engine etc

Thanks
keka

If you want to create a dimension that contains each unique vehicle type (i assume you do not consider options) then that is what you do. Have a natural key that considers more than model. Model & trim would be more appropriate.

Hi ngalemmo,

Thanks for your response.

The Fact table which i have is at lowest grain of Engine type and body type.

All the reporting is done at engine type and body type.

below are the attibutes related to product

BRANDDesc
model family
BodyType
FuelType
EngineType


There are four brands and each brand can have multiple models and each model can have multiple body type and each model can have mutlple engine types

When creating a dimension table for product is it better to consider engine type and body type as my primary key or consider model as primary key and snow flake the rest of the attriutes

any help is much appreciated.

Thanks
keka

You can't snowflake the other attributes if the PK of the primary table is model. So there is no point in snowflaking. You could treat each as separate dimensions and include those dimensions in the fact.

Hi ngalemmo,

Thanks for your reply.

Is this something you are referring to.

https://servimg.com/view/18505305/1

Correct me if any thing different

Thanks
Keka

That looks fine.

Thanks ngalemmo