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

View previous topic View next topic Go down

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

Post  bertrand01 on Mon Feb 09, 2015 8:06 am

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 ?

bertrand01

Posts : 3
Join date : 2015-02-09

View user profile

Back to top Go down

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

Post  nick_white on Mon Feb 09, 2015 9:27 am

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,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

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

Post  bertrand01 on Mon Feb 09, 2015 11:20 am

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 ?

bertrand01

Posts : 3
Join date : 2015-02-09

View user profile

Back to top Go down

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

Post  nick_white on Mon Feb 09, 2015 12:11 pm

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?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

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

Post  ngalemmo on Mon Feb 09, 2015 2:13 pm

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  bertrand01 on Mon Feb 09, 2015 2:43 pm

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

bertrand01

Posts : 3
Join date : 2015-02-09

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum