Monster Dimensions?

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?

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.

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