Create an Expression Transformation

1. Create an Expression transformation directly after the Filter transformation. Select the Expression transformation tool button located on the Transformation tool bar and place it in the workspace directly after the Filter. The icon is shown highlighted below:

Figure 4-5. Properties Tab of the Filter Transformation

Figure 4-6. Completed Properties Tab of the Filter Transformation

2. Select the following ports from the Filter transformation and pass them to Expression transformation:

b. Change the port type to input for all of the ports except AGE. (AGE should remain an input/

output port.)

c. Prefix each of these input only ports with IN_.

d. Create a new output port after the AGE port by positioning the cursor on the AGE port and clicking the add icon.

♦ Port Name = OUT_CUST_NAME

♦ Dataytype = String

♦ Precision = 41

♦ Expression = IN_FIRSTNAME ||' ' ||IN_LASTNAME

4. Create a new output port after the OUT_CUST_NAME port.

♦ Port Name = OUT_CUST_PHONE

Figure 4-7. Filter Transformation Linked to the Expression Transformation

Velocity Best Practice: Prefixing input only ports with IN_ and output ports with OUT_ is a Velocity best practice. This makes it easier to tell what the ports are without having to go into the transformation.

Tip: This new port will concatenate the FIRSTNAME and LASTNAME ports into a single string.

Do not use the CONCAT function to concatenate in expressions. Use || to achieve concatenation.

The CONCAT function is only available for backwards compatibility.

♦ Datatype = String

♦ Precision = 14

♦ Expression = '(' || SUBSTR(TO_CHAR(IN_PHONE_NUMBER),1,3) || ') ' ||

SUBSTR(TO_CHAR(IN_PHONE_NUMBER),4,3) ||'-' ||

SUBSTR(TO_CHAR(IN_PHONE_NUMBER),7,4)

5. Create new output port after the OUT_CUST_PHONE port.

♦ Port Name = OUT_GENDER

6. Create a new output port after the OUT_GENDER PORT.

♦ Port Name = OUT_AGE_GROUP

♦ Datatype = String

♦ Precision = 20

♦ Expression = Write an expression using the DECODE function that will assign the appropriate age group label to each customer based on their age. Use the online help to see details about the DECODE. If after 5 minutes you have not successfully created the DECODE statement, refer to the reference section at the end of the lab for the solution. The valid age ranges and age groups are displayed in the table below. The format of the DECODE statement follows the table.

Tip: The expression above uses a technique known as nesting functions. TO_CHAR function is nested inside the SUBSTR function. The TO_CHAR function is performed first. The SUBSTR function is then performed against the return value from TO_CHAR.

Tip: The DECODE function used in the previous expression can be used to replace nested IIF functions or small static lookup tables. The DECODE expression in the previous step will return the value MALE if incoming port GENDER is equal to M, FEMALE if GENDER equals F, or UNK if GENDER equals anything else beside F or M.

Age Range Age Group Text

7. Save your work.

8. Connect the following ports from the Expression transformation to the target table:

9. Connect the following ports from the Filter transformation to the target table:

10.Save your work.

11.Verify that your mapping is valid.

12.Right click in the workspace and select Arrange All Iconic.

Step 6: Create and Run the Workflow

1. Launch the Workflow Manager and sign into your assigned folder.

Figure 4-8. Sample Expression

2. Open the Workflow Designer tool and create a new workflow named wkf_Stage_Customer_Contacts_xx.

3. Create a Session task using the session task tool button.

4. Select m_Stage_Customer_Contacts_xx from the Mapping list box, and click OK.

5. Link the Start object to the s_m_Stage_Customer_Contacts_xx session task.

6. Edit the s_m_Stage_Customer_Contacts_xx session.

7. Under the Mapping tab:

a. Select SQ_customer_layout located under the Sources folder in the navigator window.

b. Confirm that Source file directory is set to $PMSourceFileDir\.

c. In Properties | Attribute | Source filename type in customer_list.txt.

d. In Properties | Attribute | Source filetype click the dropdown arrow and change the default from Direct to Indirect.

Tip: The source instance you are reading is known a File List. It is a list of files which will be appended together and treated as one source file by Power Center. The name of the text file that is listed in Properties | Attribute | Source filename will be a text file that contains a list of the text files to be read in as individual sources. When you create a file list you open a blank text file with a application such as Notepad and type on a separate line each text file that is to be read as part of the file list. You may precede each file name with directory path information. If you don't provide directory path information, Power Center assumes the files will be located in the same location as the file list file.

Tip: When you use the file list feature in Power Center you have to set Properties | Attribute | Source filetype to Indirect. The default is Direct. To change the setting, click the dropdown arrow and set the value you want to use.

Your screen should appear similar to Figure 4-10.

The file list file used in this exercise lists three text files which are found in the default location of the file list file, $PMSourceFileDir\. Figure 4-11 displays the contents of customer_list.txt.

e. Select STG_CUSTOMERS located under the Target folder in the navigator window.

♦ Set the relational target connection object property to NATIVE_STGxx, where xx is your student number.

♦ Check the property Truncate target table option in the target properties.

8. Save your work.

9. Check Validate messages to ensure your workflow is valid.

10.Start the workflow.

11.Review the session properties.

Figure 4-10. Session Task Source Properties

Figure 4-11. Contents of the customer_list.txt File List

Your information should appear as displayed in Figure 4-12.

12.Review the Source/Target Statistics. Your statistics should be the same as displayed in Figure 4-13.

13.If your session failed or had errors troubleshoot and correct them by reviewing the session log and making any necessary changes to your mapping or workflow.

Figure 4-12. Properties for the Completed Session Run

Figure 4-13. Source/Target Statistics for the Completed Session Run

Data Results

Preview the target data from the Designer. Your data should appear as displayed in Figure 4-14.

Observe the CUST_PHONE, CUST_GENDER, CUST_AGE_GROUP columns. These columns required transforming using the Expression transformation. Scroll down and review these columns. Verify you wrote your expressions correctly.