Dimension : add many specific colmuns or add another (one to one link) dimension

Hi everybody,

I have a DIM_CLIENT table.
This table has around 35 columns.
There is a Column named DIM_CLIENT.CLIENT_TYPE which can take 3 values :  M, P and S.

DIM_CLIENT.CLIENT_TYPE='M' represent less than 10 percent of the rows.
And for DIM_CLIENT.CLIENT_TYPE='M', I need to specifically had about 20 mores columns.

My question is : would you make another dimension like DIM_CLIENT_M or add the 20 columns to the DIM_CLIENT dimension ?

Hi, have a look at "Supertype and Subtype Schemas for Heterogeneous Products" in Kimball's chapter on Financial Services. This probably covers what you are describing

Regards,

Thanks for your reply, that help me to focus on my search.
Anyway, after reading the chapter, as I understand how I can apply the Subtypes and Supertypes for Heterogeneous Products,
I have already 3 specific fact tables for the 3 DIM_CLIENT.CLIENT_TYPE which can take 3 values :  M, P and S.

It seems to me that it doesn't reply to my question related to the dimension :
DIM_CLIENT.CLIENT_TYPE='M' represent less than 10 percent of the rows.
And for DIM_CLIENT.CLIENT_TYPE='M', I need to specifically had about 20 more dimensions columns.

My question is : would you make another dimension like DIM_CLIENT_M or add the 20 columns to the DIM_CLIENT dimension ?

Hi - I was suggesting that you create a supertype DIM_CLIENT and subtype DIM_CLIENT_M (and, if you need them, DIM_CLIENT_P and DIM_CLIENT_S dimensions.

Whether you go down this route or just add the extra columns to DIM_CLIENT probably depends on how you use the Dim. If most of the time you are joining to Client Type-specific fact tables then it may make more sense to create subtype Dims; alternatively, if most of the time you are looking at all your clients then it may make more sense to use a single DIM_CLIENT dimension.

Hope this helps?

From a documentary stand-point it would make sense to create a subtype dimension so it is clear which attributes belong to a particular type.

From a performance stand-point it makes sense to keep it as one table. From a size perspective, most DBMS perform some form of compression, so the extra null columns for the other 90% won't make much difference. And, unless you are dealing with hundreds of millions of rows, it probably isn't something to worry about.

I would try to keep the model as simple as possible.

Perfectly clear this time for me :),
Thanks a lot nick_white and ngalemmo !