Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it.

I tried to design a star schema, but I'm not sure how to handle this many columns. Normalising it sounds wrong, but I can't just have flat columns either. The combination of attributes are also too diverse to have a simple dimension table for this, even if I'd reduce the numerical values into categories (ranges), which is an option. I thought about storing them as XML or JSON for each row, but that doesn't sound great either.

If it helps, I'm planning to use Amazon's redshift for the DB.

Note: We have strong preference for RedShift as it fits perfectly for at least other few operations we do on this data. Hence I want to avoid other technologies like HBase if possible.

share|improve this question
    
what is the problem of having 1K attribute columns ? sounds like star is the solution for you. if you split or denormelize you'l have to deal with other problems like missing values for example. –  haki May 1 '13 at 15:41
    
mind if we ask what you're storing? –  Neil McGuigan May 2 '13 at 5:54
    
@haki: wondered if having a very wide table is a bad idea, perhaps in terms of performance (and table width limitation) –  Enno Shioji May 2 '13 at 6:57
    
@NeilMcGuigan: A wide range of probabilities. The raw data is decimal but it would be acceptable to turn them in to categories by range (e.g. 0.0 - 0.1, 0.1 - 0.2, etc.) –  Enno Shioji May 2 '13 at 6:59
    
text mining app? –  Neil McGuigan May 2 '13 at 7:11

1 Answer 1

up vote 2 down vote accepted

Regarding why you would want to do this, imagine you want to see which words/short phrases in customer emails are associated with costly repairs, and you want to be able to analyze this using OLAP. It can be costly to tokenize/grammify many documents, so you might want to store the tokens/grams in a form which your OLAP server understands, ie columns.

Consider MonetDB, which allows for a practically unlimited number of columns.

Redshift maxes out at 1600 columns.

Another option would be to use Principal Component Analysis and only choose the top 1600 components, but that can make interpretation difficut.

Another option would be to use Postgres and store the tokenized strings or n-grams in a string array field, but your OLAP server would need to support that.

share|improve this answer
1  
Out of curiosity what could possibly be gained from having 1600 Columns in one table?????????? –  Zane Aug 2 '13 at 20:12
    
@Zane, In text mining, you create one column for each unique word in your corpus. This is called a word vector. This could be 10,000 to 50,000 words. Instead of re-creating the word vector every time, you might want to store it in the database. I would probably serialize it and store it in a blob, but some might want it in a regular tabular form. –  Neil McGuigan Aug 2 '13 at 20:27
    
Or you could store them in rows and pivot at runtime (perhaps outside). Or maybe not use an RDBMS at all. –  Aaron Bertrand Aug 2 '13 at 20:36
    
Rows. THe problem with the text vector as you give it is that th table is not stable. Add a text and you may get new fields. That is always VERY problematic. –  TomTom Aug 3 '13 at 5:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.