Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
Pentaho Data Integration

Using Pentaho Row Denormaliser step

 
Updated on Oct 03, 2020

In this article we will learn how to use Pentaho Data Integration Row Denormaliser Step. Denormaliser step is used to denormalize or pivot data from normalized dataset or table. The Denormaliser step combines data from several rows into one row by creating new Target columns/fields. For each unique values for the Key field and each selected Group field, this step produces a target column in the output data set. It basically creates one row of data from several existing rows, i.e. to Pivot Rows to Columns.

Based on our previous use case, let's take the Budget table as source and try to generate Pivot table data in Excel file. For that we have to use the Denormaliser step to pivot the normalized dataset.

Below is how our source sales budget data in Oracle database looks like:

STOREYEARQUARTERBUDGET
Clementi2016Q150000
Clementi2016Q270000
Clementi2016Q365000
Clementi2016Q485000
River Valley2016Q130000
River Valley2016Q229000
River Valley2016Q333000
River Valley2016Q431000
Clementi2017Q155000
Clementi2017Q275000
Clementi2017Q370000
Clementi2017Q488000

This is how our expected target data in Excel as yearly & quarterly sales budget should look like:

STOREYEARQUARTER1QUARTER2QUARTER3QUARTER4
Clementi201650000700006500085000
River Valley201630000290003300031000
Clementi201755000750007000088000

Below are the steps we will be covering in this use case scenario.

  • Read Oracle database table, using Table input
  • Sort data based on Group fields, using Sort rows
  • Denormalize or pivot data, using Row Denormaliser
  • Sort data before writing to excel, using Sort rows
  • Write pivot data to excel data file, using Microsoft Excel Writer

Find below the use case transformation:

Transformation
Transformation

First in order to read database table from relational database, in our case Oracle RDBMS we will be using Table input. Let's name the step as STG_BUDGET. At first we need to create the connection object. Click New or Wizard and create a connection.

Let's name the connection as Con_Staging. Select Connection type as Oracle & Driver as Native (JDBC). The Oracle jdbc driver might not be available to PDI. In that case you have to download oracle jdbc driver ojdbc6.jar and place in the PDI lib directory, i.e. data-integration/lib/ . Next define the hostname, database name, port, username and password to connect to your oracle instance. Test to check the connection and click OK.

Next click the button Get SQL select statement. Browse Schema & select the table. Once selected, click Yes for Do you want to include the field names in SQL.. That's all. So the source RDBMS table is ready for reading. Preview your records to confirm that PDI is able to read the source database table content as expected.

Table input
Table input

Next before we can use the Denormaliser step, we need to Sort the data based on the Group columns/keys in our case STORE & YEAR.

Sort rows
Sort rows

Next we are using a Row Denormaliser step. Select the Key field as the QUARTER. This is the source field which will be denormalized based on the values, i.e. Key value. Next in the Group field section map the Source fields STORE & YEAR. In the Target fields section lets put the Target fieldname corresponding to Key Values Q1, Q2, Q3, Q4 as QUARTER1, QUARTER2, QUARTER3, QUARTER4 respectively. For all these these target fields the source Value fieldname is BUDGET, which has the amount figures corresponding to the quarter key values.

Denormaliser
Denormaliser

Before we write the Pivot data to excel we want to sort the order of the data year wise. So for that we will again use another Sort rows step. Sort the denormalized data based on YEAR followed by STORE.

Sort rows
Sort rows

Next in order to write the data in Excel (xslx format) we will be using Excel writer step. Let's name this as XL_Sales_Budget. Browse and select the location where we want to write the file. Select extension as Excel 97 and above. go to the Sheets tab and click on Get sheetnames. Select the correct data sheet. In my case it is Sheet1. In the contents tab check the Header option, as we have a header record in our excel sheet.

Excel Writer
Excel Writer

Next go to the contents tab, select Write header. Next click the button Get fields. Check and modify the Fields name & data type.

Excel Writer Content
Excel Writer Content

All done! Let us run the Transformation from Spoon and validate the output data in the excel from the Preview tab of the Execution Results section.

Execution Results
Execution Results

So we have successfully completed rdbms read, denormalize data and excel write.

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles