Visual ETL Development With IBM DataStage

ETL (Extract Transform Load) is the most resource consuming part of data warehouse development and maintenance. An ETL tool, particularly one that is GUI-based, can leverage the productivity and quality of ETL development and maintenance.

WebSphere DataStage from IBM is an ETL tool. In DataStage you build and execute ETL jobs visually on its GUI clients. This article by Djoni Darmawikarta shows how to build and execute an ETL job with DataStage. Specifically, we’ll build a job that loads a Customer Dimension table from an input sequential file (implementing an SCD1, Slowly Changing Dimension Type1; a well-known technique from Dimensional Data Warehouse methodology).

You use Designer, one of the DataStage clients, for ETL jobs development. When you start Designer, in addition to entering your host system and credential, you must also specify the project you’d like to work on.

A DataStage project stores jobs and define their environment, such as their security and execution resources. Your project, as well as your user account, is typically created by your DataStage administrator.

To develop a new job, on the icon toolbar, click the drop-down arrow to the right of the New icon (I circle in red).

We’ll develop a parallel job, so click the Parallel Job from the drop-down list.

In DataStage, a parallel job can execute in parallel mode (in simple terms, this means the job does not process its data records sequentially, one record after another).

We can now start to design our job in the job canvas. For now, our parallel job name is Untitled 1, as shown on the canvas title.

Our job extracts its input data from a customer sequential file, transforms the data, and then loads it into an Oracle table. The customer file contains new customers and name changes of the existing customers. The customer file has more data (more columns) than we need; we’ll extract only those we need.

In DataStage, you build a job by selecting and linking stages on the job canvas. So, on the Palette, click File, look for Sequential File stage by clicking the up or down arrow.

Drag and drop our first stage, the Sequential File stage, from the Palette to the left side of the canvas.

We’d like to layout our stages left to right to visualize them processing data in that direction, so we start placing the input stage on the left of the canvas (we can move stages around the canvas, though, by dragging them).

Rename the Sequential File stage to Customer by selecting (clicking) and clicking it again, and typing over the default name (Sequential_File_1).

Next, add Transformer stage (under Processing palette) and Oracle Enterprise stage (under Database palette) that we need for our job. Rename them as shown.

We use the Transformer stage to change (transform) customer names to uppercase.

Now that we have the stages we need for our job, we’ll link them together. Right click the Customer stage, hold and drag & drop on the Uppercase stage, and then, from Uppercase stage to the Customer_Dim stage.

You can rename the links just like the way you rename stages.

Just in case something happens to our system, we’d like to save our works so far.

Name the job Load_Customer_Dim and you can choose the folder (called Category in DataStage) of your choice where you want to put the job.

Notice the canvas title now reflects the name of our parallel job (changes from its default name given when we’re creating it).

The Customer stage has a warning sign (the yellow! sign), which indicates that it has an error. This is because we haven’t specified its parameters. For example, the file it refers to. So, let’s fix it.

Double-click the Customer stage. On the Properties tab select the File property, and type in the file name including its full path. The sequential file must be accessible by the DataStage server.

Next, we need to specify the format of our file. For example, set the Record level’s Final delimiter and Field default’s Delimiter as shown.

We must also specify the record layout, i.e. its fields (columns).

To test that the stage will be able to read the data as specified, click the View Data button (upper right hand corner).

You can specify the number of rows (records) you’d like to view.

When you click OK and you have set the correct specification for the file, you’d see the data.

When you finish viewing the data, close the view window as well as the property window. We’re done with the Customer stage.