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

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

Back to top

- Similar topics

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