Lab B: Load Date Staging Table

Business Purpose

The date staging area in the operational data store must be loaded with one record for each date covered in the data marts. Each date must be described with the date attributes used in the data mart, such as the month name, quarter number, whether the date is a weekday or a weekend, and so forth.

Technical Description

To load the date staging area, we will use Informatica date functions and variables to transform a date value and date id. The raw dates are in a flat file.

Objectives

♦ Copy an Expression transformation to convert a string date to various descriptive date columns.

♦ Use the Expression Editor to create or view expressions and become familiar with date function syntax.

♦ Understand the evaluation sequence of input, output, and variable ports.

♦ Learn how to use variable ports.

Duration

Velocity Deliverable: Mapping Specifications

Short Description A text file will run through an expression to do date manipulation and load to our date staging area.

Load Frequency Once

PROCESSING DESCRIPTION (DETAIL) This mapping will generate the date staging table from the dates text file. The Expression transformation is used to derive the different date values. SOURCE TO TARGET FIELD MATRIX Target TableTarget ColumnSource FileSource ColumnExpressionDefault Value if Null

Data Issues/ Quality STG_DATESDATE_ID_LEGACYdates.txtDATE_ID STG_DATESDATE_VALUEdates.txtDerivedReformat the DATE column to MM/DD/YYYY STG_DATESDAY_OF_MONTHdates.txtderivedThe current day of the current month. EX - TUESDAY STG_DATESMONTH_NUMBERdates.txtderivedThe month number of the year. STG_DATESYEAR_VALUEdates.txtderivedThe year for each record. STG_DATESDAY_OF_WEEKdates.txtderivedThe day of the week for the record. STG_DATESDAY_NAMEdates.txtderivedThe name of the day for the record. STG_DATESMONTH_NAMEdates.txtderivedThe month name for the record. STG_DATESDAY_OF_YEARdates.txtderivedThe day number of the year for the record. EX - 1-365 STG_DATESMONTH_OF_YEARdates.txtderivedThe month number of the year for the record. STG_DATESWEEK_OF_YEARdates.txtderivedThe week number of the year for the record. STG_DATESDAY_OVERALLdates.txtderivedThe day number overall. STG_DATESWEEK_OVERALLdates.txtderivedThe week number overall. STG_DATESMONTH_OVERALLdates.txtderivedThe month number overall. STG_DATESYEAR_OVERALLdates.txtderivedThe year number overall. STG_DATESHOLIDAY_INDICATORdates.txtderivedThis flag will tell us whether the record date is a holiday. STG_DATESWORKDAY_INDICATORdates.txtderivedThis flag will tell us whether the record date is a workday. STG_DATESWEEKDAY_INDICATORdates.txtderivedThis flag will tell us whether the record date is a weekday. STG_DATESWEEKEND_INDICATORdates.txtderivedThis flag will tell us whether the record date is a weekend. STG_DATESQUARTER_OF_YEARdates.txtderivedThe quarter number of the year.

STG_DATESSEASONdates.txtderivedThe current season. STG_DATESLAST_DAY_IN_MONTHdates.txtderivedFlag to indicate the current date is last day of the month. STG_DATESLAST_DAY_IN_QUARTERdates.txtderivedFlag to indicate the current date is last day of the quarter. STG_DATESLAST_DAY_IN_YEARdates.txtderivedFlag to indicate the current date is the last day of the year.

Target TableTarget ColumnSource FileSource ColumnExpressionDefault Value if Null

Data Issues/ Quality

Instructions

Step 1: Create a Flat File Source Definition

1. Launch the Designer (if it is not already running) and connect to the PC8_DEV repository.

2. Open your student folder.

3. Import the dates.txt comma delimited flat file source using the Flat File Wizard. Make sure that you import the field names from the first line.

4. Save the repository.

Step 2: Create a Relational Target Definition

1. Import the STG_DATES table using the Target Designer.

2. Save the repository.

Step 3: Create a Mapping

1. Create a new mapping named m_STG_DATES_xx.

2. Add dates flat file source to the mapping.

3. Add the STG_DATES target to the mapping.

Your mapping should appear similar to Figure 6-17.

4. Expand the DEV_SHARED folder.

5. Expand the Transformations subfolder.

a. select the re_exp_STG_DATES.

b. With your left mouse button, drag the transformation toward your mapping but DO NOT DROP IT.

c. Hold down the Ctrl key.

d. Drop the transformation into the mapping.

e. Click “Yes” on the Copy Confirmation message box.

Note: If the confirmation box says “Shortcut” instead of “Copy”, try again and make sure that you hold down the Ctrl key continuously as you drop the transformation into the mapping.

Figure 6-17. Mapping with Source and Target definitions

6. Link the two output ports on the Source Qualifier to the two input ports on the Expression transformation, matching the names.

7. Use the “Autolink” feature to link the output ports in the Expression transformation to the corresponding fields in the target definition - by Position.

8. Save the mapping and confirm it is valid.

Your mapping will appear the same as in Figure 6-18.

9. Edit the Expression transformation and click on the Ports tab.

10.Examine the structure of the Expression transformation ports and expressions.

Note that the DATE_ID is an integer that is passed directly to the target table unchanged.

The input port DATE supplies a string that describes an individual date, such as 'May 20, 2005'. The variable ports will process that string in various ways in order to extract a specific descriptor, such as the day of the week, the quarter, the month, whether the date is a holiday, etc. These descriptors will later be used in the data warehouse to group and filter report data.

11.Examine some of the variable port expressions and see if you can determine how they work. You can use PowerCenter Help to view the syntax for any function. If you wish, ask your instructor for clarification on any of the expressions.

Note that variable ports cannot be output ports, so a separate set of output ports is used at the bottom of the transformation in order to output the data to the target. Most of these output ports simply call a variable port.

Variable ports were used in this transformation because they will be resolved one at a time, top to bottom. In this case, some of the later expressions are dependent on the results of the earlier expressions.

Figure 6-18. Completed Mapping

Tip: Informatica evaluates ports in the following order: input/output (input only as well), variable, and then output. Variables are evaluated in top down order, so it is important to put them in a specific order.

Step 4: Create a Workflow and a Session Task

1. Launch the Workflow Manager application (if it's not already running) and connect to the PC8_DEV repository.

2. Open your student folder.

3. Create a new workflow named wkf_Load_STG_DATES_xx.

4. Create a session named s_m_STG_DATES_xx that uses the m_STG_DATES_xx mapping,

5. Edit the session you just created.

a. Select the Mapping tab.

b. Select the Source Qualifier icon SQ_dates.

c. In the Properties area scroll down and confirm the source file name and location. Ensure that the Source Filename property value includes the .txt extension.

d. Select the target STG_DATES.

e. Select your appropriate target connection object.

f. Select the option “Truncate target table”.

6. Complete the workflow by linking the Start task to the session task.

7. Save the repository.

Step 5: Run the Workflow and Monitor the Results

1. Start the workflow.

2. Maximize the Workflow Monitor and select the Task View.

3. Review the Task Details.

Your information should appear the same as in Figure 6-19.

Figure 6-19. Task Details of the completed session run

4. Review the Source/Target Statistics.

Figure 6-20. Source/Target Statistics for the session run

Data Results

Use the Preview Data feature in the Designer to view the data results.

Your results should appear similar to those in Figure 6-21 through Figure 6-22.

Figure 6-21. Data preview of the STG_DATES table - screen 1

Figure 6-22. Data preview of the STG_DATES table - screen 2 scrolled right

Unit 7: Debugger

In this unit you will learn about:

♦ Debugging mappings