Big dimensional attributes

[ATran]

in the customer dimension, there are a lot of descriptive column, information field by the customer etc
ie in the relational database, many big column, like nvarchar(200), nvarchar(200) ,nvarchar(1020) (around 50 fields or even more).
I simply cannot add all those attributes in the DIM_CUSTOMERS, because with only a couple of nvarchar(1020), the table is done for.

i was thinking of a junk text/ntext dimension, but i don't know if it is a good idea, because:
- the tables can grow very big (lets says 50000 customer * 50 fields)
- also if i need to do a listing for each attributes, i would end up with a query with 50 JOIN to the junk dimesion.
- also the type is ntext, to we miss a lot of function

What to do ?


ps: have to be compatible for sql2000 and sql2005(and oracle)

[ngalemmo]

A common technique is to perform 'vertical partitioning' of the table... that is, you split the columns among multiple tables, with each table having the same primary key. The primary table should contain all the basic customer attributes most commonly used in queries and report filtering. The secondary tables can contain one or more long text fields.

Logically, it is all one big row (and you could create a view to make it look that way). But queries perform better since only those that need the large text fields are those that are burdened with having to retrieve them.

[ATran]

thanks for the tip
i'll dig on that

[VHF]

SQL Server 2005 has the nvarchar(max) data type, but that won't help you with SQL 2000 or Oracle.

nvarchar(max) gives you the best of both worlds for text fields that need to be able to contain a large number of characters, but freqently contain only a small of amount of text. SQL Server stores these fields in the record itself as long as the record doesn't exceed 8K, otherwise puts them in their own 2K pages.

[Sponsored content]