Dimensional Data Operation - (Pivot|Transpose|Cross-tab|Matrix)

Data System Architecture

Dimensional Data Operation - (Pivot|Transpose|Cross-tab|Matrix)

About

The pivot is an dimensional data operation where rows and columns are interchanged.

Pivot is also known as:

  • Transpose in the linear algebra word
  • Matrix in the Microsoft world
  • Cross-tab

This operation is typically performed by the visualization tool.

Statistics

wiki/Cross tabulation (or crosstabs for short) is a statistical process that summarises categorical data to create a contingency table. Some entries may be weighted, unweighted tables are commonly known as pivot tables.

Analytics Client

SQL

select cust_id, 
       max(decode(day,trunc(sysdate-0)),sales)) day1,
       max(decode(day,trunc(sysdate-1)),sales)) day2,
       .....
       max(decode(day,trunc(sysdate-6)),sales)) day7
  from fact,dim
 where <join>
   and day >= sysdate minus whatever....
  group by cust_id;

Excel

Copy > Paste Special > Transpose

Excel Transpose Pivot

Microsoft Report (SSRS)

Ssrs Report Wizard Matrix

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.

Documentation / Reference





Discover More
Data System Architecture
Dimensional Data Operations (OLAP Operation)

This sections is all cube operations. The analyst can navigate through the database and screen for a particular subset of the data, changing the data's orientations and defining analytical calculationsSlicDicDrill...
Card Puncher Data Processing
Linear Algebra - Matrix

The Traditional notion of a matrix is: a two-dimensional array a rectangular table of known or unknown numbers One simple role for a matrix: packing together a bunch of columns or rows Matrix...
Saw Object
OBIEE 10G/11G - Pivot View

This view displays results in a pivot table, which provides a summary view of data in cross-tab format. Pivot tables provide the ability to rotate rows, columns, and section headings to obtain different...
Card Puncher Data Processing
Oracle Database - (Pivot|Unpivot) Statement

Pivot statement in Oracle The column from the first select will be created with as prefix the value of the column in the for statement of the pivot statement. Unpivot can be used to see all column...



Share this page:
Follow us:
Task Runner