Minnu's Blog on Informatica & Data warehouse concepts

Archives

Monday, June 9, 2008

HOW TO: De-normalize data/pivot rows into columns

Problem Description
The Normalizer transformation is used to "normalize" data, or to pivot columns into rows. How can you do the opposite (de-normalize, denormalize), or pivot multiple rows into multiple columns in a single row? Suppose you have a source table with this data that is a record of monthly expenses for each of your Sales Reps:

Image Hosted by ImageShack.us


Solution
To accomplish this do the following:
1. Create an Aggregator transformation with the following ports and expressions:

Image Hosted by ImageShack.us


2. Connect the SALES_ID, MONTH and the AMOUNT ports from the Source Qualifier to the Aggregator.
3. Connect the JAN_AMT, FEB_AMT, MAR_AMT, etc. ports to the target.



This approach can be used anytime the source has a column containing finite, repeating values that are known in advance such as the names of months used in this example.

1 comments:

Anonymous said...

Even if an Limited Liability package that has got all the services that you
purchase from those companies. The Country delivers PPC service in such a way that makes
the most fuel-efficient sense.

Feel free to visit my blog post :: best seo companies