Dimensionalizing Pivoted Measures

Issue

You have a table with pivoted measures, such as:

Use the following statement to create and populate the table:

CREATE
TABLE pvt
(VendorID int, DateID int, MaturityAmount1 int, MaturityAmount2 int,

MaturityAmount3 int, RepricingAmount1 int, RepricingAmount2 int, RepricingAmount3 int);

GO

INSERT
INTO pvt VALUES (1,1,4,3,5,4,4, 100);

INSERT
INTO pvt VALUES (2,1,4,1,5,5,5, 100);

INSERT
INTO pvt VALUES (1,2,4,3,5,4,4, 200);

INSERT
INTO pvt VALUES (2,2,4,1,5,5,5, 200);

INSERT
INTO pvt VALUES (3,1,4,3,5,4,4, 300);

INSERT
INTO pvt VALUES (3,2,4,3,5,4,4, 400);

INSERT
INTO pvt VALUES (3,3,4,3,5,4,4, 100);

INSERT
INTO pvt VALUES (4,1,4,2,5,5,4, 100);

INSERT
INTO pvt VALUES (5,1,5,1,5,5,5, 200);

GO

Let’s say you need a way to unpivot the results in the following format which is more suitable for OLAP:

In this way, you eliminate the repeating measure columns with a single column. Here, the BucketID column is introduced so you could join the results to a dimension to let the user cross-tab the results on columns.

Solution

Usually, ETL processes would take care of transforming data in format suitable for analysis especially with large data volumes. However, with smaller fact tables and an OLAP layer directly on top of the operational database, such as having SQL views directly on top of the source database, you may need an ad-hoc approach to unpivot the results. Assuming you are fortunate to have SQL Server 2005 or above, you can use the SQL Server UNPIVOT function to unpivot on multiple columns. The following query does the trick:

SELECT VendorID, DateID,
CAST(REPLACE(Maturity,
‘MaturityAmount’,
”)
as
int)
as BucketID,

MaturityAmount, RepricingAmount

FROM

(SELECT VendorID, DateID, MaturityAmount1, MaturityAmount2, MaturityAmount3,

RepricingAmount1, RepricingAmount2, RepricingAmount3 FROM pvt) p

UNPIVOT

(MaturityAmount FOR Maturity IN
(MaturityAmount1, MaturityAmount2, MaturityAmount3))
AS UnPivotedMaturity

UNPIVOT

(RepricingAmount FOR Repricing IN
(RepricingAmount1, RepricingAmount2, RepricingAmount3))
AS UnPivotedRepricing

WHERE
REPLACE(Maturity,
‘Maturity’,
”)
=
REPLACE(Repricing,
‘Repricing’,
”)

Notice that a WHERE clause is needed to join the two unpivoted sets so you don’t end up with a Cartesian set. The net result is that the number of rows in the unpivoted dataset is:

Number of Rows in Pivoted (original) Table * Number of Repeated Columns

So, in our case, the dataset will have 9 * 3 = 27 rows.