Big dimensional attributes

View previous topic View next topic Go down

Big dimensional attributes

Post  ATran on Tue Apr 20, 2010 2:48 pm

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)

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

Re: Big dimensional attributes

Post  ngalemmo on Tue Apr 20, 2010 5:46 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Big dimensional attributes

Post  ATran on Wed Apr 21, 2010 9:22 am

thanks for the tip
i'll dig on that

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

Re: Big dimensional attributes

Post  VHF on Wed Apr 21, 2010 4:37 pm

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.


Last edited by VHF on Wed Apr 21, 2010 4:38 pm; edited 2 times in total (Reason for editing : fix typo)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Big dimensional attributes

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