Monster Dimensions?

View previous topic View next topic Go down

Monster Dimensions?

Post  Bob Probst on Wed Jun 08, 2011 9:27 pm

Several posts around here have mentioned "Monster Dimensions" as being type 2 dimensions with a lot of attributes and a lot of source records (hundreds of thousands) and there seems to be some concern about managing these "monsters".

I suppose you could say I have some monster dimensions then, and I'm having no problems with them. Account Dim handles 700k records a day and is just over 100 fields wide -- all managed as type 2. It's not terribly volatile, we have about 1500 change record inserts a day. Our customer dim is about half as long and about half as wide but would still fit into the "monster" category as some people would define it.

Performance remains good however, nightly loads are done in 45 minutes. Queries against the base grain fact, across a dozen dims come back in seconds.

My question is: Am I missing something? have I created a monster? and why does there seem to be concerns over big type 2 dimensions?


Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Monster Dimensions?

Post  hang on Wed Jun 08, 2011 11:18 pm

Why not concerned. In your case, the Account Dim is only 700k and increased by 1500 daily, so after even one year it may become 700k+547.5k, a little over one million, which is barely a monster dimension, if not at all.

With real monster dimension, we are talking about many or tens of million rows in a single dimension with daily increment of a few hundreds of thousands. Then you might become worried as the performance will degrade quickly, even table partitioning won't save you because itís only applicable to fact table with time series but not to dimensions tables.

Hopefully now you see the picture why we should leverage fact table to reflect changes in monster dimension. I have dealt with 20 million record dimension with many attributes in it. You know what, the performance is horrible. To minimise the growth, someone has turned many valid SCD 2 attributes into SCD 1. So it has lost its change tracking ability and yet is very slow.

I think Kimballís mini dimension idea is a brilliant solution to tackle monster dimensions. Once you know how to use the technique, you may even apply it to not so monster dimension as it gives the fact table a quick entry for your dimension profiles (>10 attributes) by joining a tiny dimension with few hundreds or thousand rows instead many more rows in the main dimension.


Last edited by hang on Thu Jun 09, 2011 12:34 am; edited 1 time in total

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Monster Dimensions?

Post  Bob Probst on Wed Jun 08, 2011 11:56 pm

Thanks, I got the sense from some of the other posts that I might be out in left field.

Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Monster Dimensions?

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