Prologika Forums
Making sense of data
Dimensionalizing Pivoted Measures

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

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.


Posted Mon, Dec 15 2008 7:43 PM by tlachev