Data Modeling - Pivot

1 - About

The principal article on the pivot (format|analysis) or structure.

3 - Disadvantage as a storage structure

Disadvantage of this structure as a storage structure, if a pivot is used to represent different possible values of a column.

If you want:

  • to add a category, you need to add a category column for each column
  • to do an update on one of this fields, you end up with a list of “IF THEN ELSE”.
  • to do an aggregation on one of this fields, you end up with a list of “UNION”.
  • to add an audit function on it, you need to add an audit on each column
  • to set a value that is dependent of others dimensions, you end up adding a lot of column.
    • If you have 1 metrics column with 2 dimensions, you add 2 columns
    • If you have 2 metrics column with 2 dimensions, you add 4 columns

Furthermore, it's not a third normal form. Then if the data is sparse, you cannot save it in a parse form and you end ups using a lot of storage for nothing and can degrade seriously then the performance.

  • Bookmark "Data Modeling - Pivot" at del.icio.us
  • Bookmark "Data Modeling - Pivot" at Digg
  • Bookmark "Data Modeling - Pivot" at Ask
  • Bookmark "Data Modeling - Pivot" at Google
  • Bookmark "Data Modeling - Pivot" at StumbleUpon
  • Bookmark "Data Modeling - Pivot" at Technorati
  • Bookmark "Data Modeling - Pivot" at Live Bookmarks
  • Bookmark "Data Modeling - Pivot" at Yahoo! Myweb
  • Bookmark "Data Modeling - Pivot" at Facebook
  • Bookmark "Data Modeling - Pivot" at Yahoo! Bookmarks
  • Bookmark "Data Modeling - Pivot" at Twitter
  • Bookmark "Data Modeling - Pivot" at myAOL
data_modeling/pivot.txt · Last modified: 2015/12/09 10:10 by gerardnico