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.

  • 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
    Commented May 1, 2013 at 15:41
  • mind if we ask what you're storing? Commented May 2, 2013 at 5:54
  • @haki: wondered if having a very wide table is a bad idea, perhaps in terms of performance (and table width limitation) Commented May 2, 2013 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.) Commented May 2, 2013 at 6:59
  • text mining app? Commented May 2, 2013 at 7:11

1 Answer 1


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.

  • 1
    Out of curiosity what could possibly be gained from having 1600 Columns in one table??????????
    – Zane
    Commented Aug 2, 2013 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. Commented Aug 2, 2013 at 20:27
  • Or you could store them in rows and pivot at runtime (perhaps outside). Or maybe not use an RDBMS at all. Commented Aug 2, 2013 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
    Commented Aug 3, 2013 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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