Data Warehouse: Concepts on ETL Processes (Pivoting) (II)

Transforming presentation: What is a Pivoting operation?

• It is a key technique in DWH: allow you to transform multiple rows of input into fewer and generally wider rows
• Allow to transform the presentation of the data, rotating rows into columns and presenting data in a crosstabular format, or vice-versa.
• When pivoting, an aggregation operator is applied for each item in the pivot column value list
• Use the pivot_clause of the SELECT statement to write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation.
  
• Pivoting: Transactional format <=> Crosstabular (relational) format

Consider the example below:

• The view SALES_VIEW displays data in a transactional format
• It lists amount and quantity sold by product, country, quarter and channel.
• In this case, a product may be sold through various channels in a given country and a given quarter of year.
• If you need to see the sales by channel_id on a relational or crosstabular format, you can use a pivoting operation:

SQL> CREATE VIEW sales_view AS
 SELECT
   prod_name product, country_name country, channel_id channel,
   SUBSTR(calendar_quarter_desc, 6,2) quarter,
   SUM(amount_sold) amount_sold, SUM(quantity_sold) quantity_sold
 FROM sales, times, customers, countries, products
 WHERE sales.time_id = times.time_id AND
    sales.prod_id = products.prod_id AND
   sales.cust_id = customers.cust_id AND
   customers.country_id = countries.country_id
 GROUP BY prod_name, country_name, channel_id,
 SUBSTR(calendar_quarter_desc, 6, 2);

• Using the PIVOT clause you can reconstruct the data on a cross tabular form:

SQL> SELECT * FROM
 (SELECT product, channel, amount_sold
   FROM sales_view
   WHERE country = 'United States of America' 
 ) S PIVOT (SUM(amount_sold)
   FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,
                   5 AS CATALOG_SALES, 9 AS TELESALES))
ORDER BY product;

PRODUCT                   DIRECT_SALES           INTERNET_SALES         CATALOG_SALES          TELESALES           
------------------------- ---------------------- ---------------------- ---------------------- ------------------ 
...
Home Theatre Package...   2052150.1              310431.22                                                            
Internal 6X CD-ROM        135284.16              15839.73                                                             
Internal 8X CD-ROM        158157.23              24398.22                                                             
Keyboard Wrist Rest       110554                 22477.41                                      1354.87                
...
 71 rows selected