Dimension Lookup table - good design? - 01-22-2004 , 09:32 AM
Any comments on the following design in terms of performance for a SQL
Server 2000 / BusObjects datawarehouse:
I have a Property dimension, and there are many many (50 or so)
categories of attributes of the property, such as "Garden", "Heating",
"Registered Rent", etc.
Let's take 'Garden'. A property can have Private or Shared
sub-category. Then this sub-category is divided into a
sub-sub-category, such as 'Front' or 'Back', etc. These then have a
description, such as "Shared Garden in the Front of the Property".
Origingally, I was thinking of just putting the lowest level
description in the Property dimension, but some of the descriptions
are 50 chars long, which I thought would really reduce the speed of
queries matching these criteria.
This is what I've come up with:
Property dimension has 50 fields (one for each attribute).
Each of these fields has a foreign, surrogate key to an Attribute
Attribute subdimension table has the surrogate key, the attribute
name, the sub-category code, the sub-sub-category code, and then the
Would this improve performance? I would expect 5000 records in this
Attribute subdimension and perhaps hundreds of thousands in the
My questions is: would the integer values in the column being WHERE'd
improve the performance more than the loss of performance due to
having this extra join to a sub-table?