Flatfile Filter T/R(TRANSFORMATION)

Hands on 04 Flat file Filter:

Lab at a Glance

                          The exercises in this lab are designed to walk the student through the 

                           process of importing and analyzing a flat file source using the Flat File Wizard. 

                          The student will also learn how to use the Filter transformation.

Objectives

                          After completing the lab,Students will be able to do:

•   Use the Flat File wizard.
•   Create a Filter transformation

Summary

                      In this lab,Students will create a mapping (and associated workflow) to extract 

                      the data from a flat file that contains date records.

                      An example of data from the flat file follows:

                 The mapping must transform the data and load it into a target table as follows:

             In addition, the mapping must filter the data for only those dates after 12/04/1999.

           The final mapping should look as follows: 

                      9.1 Exercise 1: Import Source and Target

 

          Step 1. Import the file :

                  3. Go to RRITEC Lab Copy/Lab Data folder copy TIME.txt file and paste

                      in Informatica\9.0.1\server\infa_shared\SrcFiles

                 4. In the Designer, open the Source Analyzer by clicking the Source Analyzer
                     button or by selecting Tools | Source Analyzer

                 5. Select Sources | Import From File.

                  6. The Open Flat File dialog box appears -> Change the Files of type: to All
                      Files(*.*) -> Locate the directory where the flat file resides – typically
                      Informatica\9.0.1\server\infa_shared\SrcFiles
                 7. Select the flat tile TIME.txt -> Click OK.

               8. The Flat File Import Wizard appears.
               9. Ensure the Delimited radio button is selected (default).
              10. Select the Import field names from first line check box

.

                     11. Click Next.
                     12. Make sure the Comma check box is selected under Delimiters.
                     13. Make sure the Double Quotes radio button is selected under Text Qualifier.

                      14. Click Next
                      15. By importing the field names from the first line, the port names have 

                            already been created. No other modifications are necessary.

                           16. Click  on Finish.
                           17. The source definition should look as follows:

 

                             18. Save the Repository.

Step 2. Import the target table:

                   1. Open the Target Designer by clicking the Target Designer button 

                       on the toolbar.
                   2. Select Targets | Import From Database.
                                    a. ODBC Data Source : RRITEC_TARGET

                                    b. Username : TDBU
                                    c. Owner :TDBU
                                    d. Password :RRitec123

                   3. Click on Connect
                  4. Expand the TDBU schema and TABLES node.
                  5. Select ODS_TIME -> Click OK ->The ODS_TIME target table should

                      look as follows:

                      6. Save the Repository

                                    9.2 Exercise 2: Create the Mapping

 

       Step 1. Create a mapping:

1. Open the Mapping Designer by clicking on the Mapping Designer button
2.  Create a new mapping called m_ODS_TIME

       Step 2. Add Source and Target Transformations:

                          1. From the Flat File category under the Sources node, drag-and-drop

                              the TIME source definition into the Mapping Designer workspace.
                         2. From the Targets node, drag-and-drop ODS_TIME into the Mapping
                             Designer workspace.

       Step 3. Create an Expression transformation:

                        1. Create a new Expression transformation.
                        2. Select all ports within the sq_TIME source qualifier, then drag-and-

                           drop them onto the Expression transformation.
                        3. Double-click on the header of the Expression transformation.
                        4. The Edit Transformations dialog appears.
                        5. Click the Rename button.
                        6. Enter exp_ODS_TIME in the Transformation Name field and Click on

                             OK button.
                        7.  Click on the Ports tab.

                        8. Highlight the third port, QUARTER, then add the following ports by 

                            clicking the Add Port button:

• DATE_ID, Data type=date/time, Prec=29(default), Port Type=Output Only

•  MONTH_out, Data type=string, Prec=3, Port Type=Output Only

•  QUARTER_out, Data type=string, Prec=2, Port Type=Output Only

•  YEAR_out, Data type=string, Prec=4, Port Type=Output Only

             An expression will be needed for each of the above ports to derive the values. 

             Open the Expression Editor and add expressions for each port as follows:

• DATE_ID ->  TO_DATE(DATE,’DD-MON-YYYY’)

• MONTH_out -> SUBSTR(DATE,4,3)

• QUARTER_out -> SUBSTR(QUARTER,6,1)

• YEAR_out -> SUBSTR(QUARTER,1,4)

                       9. Change the ports, DATE and QUARTER, to input only ports

                      10. The Ports tab of the exp_ODS_TIME expression should look as follows

                       11. Click the OK button.
                       12. The expression transformation should look as follows

Step 4. Create a Filter Transformation:

1.  Click on the Filter Transformation button on the Transformation
    toolbar.
2.  Click and drag the pointer (which appears as cross hairs in the workspace)
    to the right of the exp_ODS_TIME expression.
3. Drag and drop the following ports in the order specified from 

            exp_ODS_TIME to the FILTRANS window:

•  DATE_ID
• DAY_OF_WEEK
•  MONTH_out
•  QUARTER_out
•  YEAR_out

                         4.  Double-click on the Filter transformation.
                         5.  The Edit Transformations dialog box appears.
                         6.  Click the Rename button and enter fil_ODS_TIME.
                         7.  Click on the Ports tab and rename the following ports

•  MONTH_out = MONTH
•  QUARTER_out = QUARTER
•  YEAR_out = YEAR

                        8. Click on the Properties tab.
                        9. Click on the down arrow to edit the Filter Condition

                       10. To define a new filter condition, remove the default text, TRUE.
                       11. Open the Expression Editor and click on the Ports tab to the
                               right of the Functions tab

                        12. Double-click on DATE_ID.
                        13. Click the greater than or equals button on the operator keypad.
                        14. Click the Functions tab, expand the Conversion folder and
                             double-click on To_Date.
                        15. To complete the expression, type '12/04/1999', 'MM/DD/YYYY' between
                               the parentheses. The final expression should look as follows
                               DATE_ID >=TO_DATE('12/04/1999', 'MM/DD/YYYY')
                        16. Click on the Validate button to validate the filter condition.
                        17. Click OK twice to save changes.
                        18. The Filter transformation should look as follows

                             

         Step 5. Link the Target Transformation:

1. Right-click in the workspace and select Auto link…
2. Select fil_ODS_TIME in the From Transformation field and                                       ODS_TIME in the To Transformations field.
3. Make sure that the Name radio button is selected in the By field

 

 

                         4. Click OK.
                         5. All ports should be linked between these two transformations

                          6. Validate the mapping by selecting Mapping |Validate.
                          7. Remember to check the Validate tab in the Output window
                              for the results
                          8. Save the repository.
                          9. The completed mapping should look as follows

                 9.3 Exercise 3: Create and Run the Workflow

                                      In this exercise,We will create a workflow to run and test the 

                                      new mapping.

      Step 1. Start the Workflow Manager:

                                   Open the Workflow Manager -> Connect to the Repository 

                                   ->Open the RRITEC folder.

     Step 2. Create a workflow:

1.  Select Workflows | Create to create a new workflow.
2.  Name the workflow wf_ODS_TIME
3.  Create a session task for the m_ODS_TIME mapping                                                called  s_m_ODS_TIME
4.  Double-click on the Session task. Then Edit Tasks                                                   dialog box appears

• Select the Mapping tab.
• In the Navigator window, select the source SQ_TIME.

           Under Properties, TIME – File Reader, Source file name,                                            enter TIME.txt.

•  In the Navigator window, select the target ODS_TIME.
•  Under Connections, click on the down arrow , select native_target 
• click OK.
•  Under Properties, the Target load type should be defaulted

            to Normal. Scroll down to select the Truncate target table option.

•  Click OK to Close the Edit Tasks dialog box

                            Save the repository.
                            Link Start to s_m_ODS_TIME_xx.

 
                       Save, validate and start the workflow.
                       Monitor and review the results for s_m_ODS_TIME in the
                        Workflow Monitor.