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.

Creating Workflow

Hands on 03_Creating_Workflow:-

 

                                            The exercises in this lab familiarize the student with the process 

                      of creating a simple workflow with a start task and session task. This workflow

                      will essentially define a run-time version of the mapping created in the previous lab.

                      The exercises will also familiarize the student with the process of starting and 

                      monitoring a workflow.

Objectives:

                      After completing the lab,students will be able to:

• Use the Workflow Manager
• Create a new workflow.
• Create and edit session tasks.
• Link tasks within a workflow.
• Validate a Workflow
• .Start a workflow.
• Use the Workflow Monitor to monitor a workflow.
• View session information and the session log after
  a workflow has run.

Exercises:

                                       8.1 Exercise 1: Create a Workflow

                         In this exercise,Students will create a new Workflow

            Step 1. Start the Workflow Manager:

1. Select Start ->Programs -> Informatica 9.0.1 -> Client -> Power Center                   Client-> Power Center Workflow Manager
2. Click on OK

              Step 2. Connect to the Repository:

                              6. Select the RRITEC_REP repository.
                              7. Right Click on Repository RRITEC_REP | Connect
                              8. The Connect to Repository dialog box appears 

                  

                                   9. Enter the username and password--
                                         username: Administrator
                                         password: Administrator
                                  10. Click the Connect button.
                                  11. Right-click on the RRITEC folder and select Open.
                                  12. Open the Workflow Designer by selecting Tools | Workflow Designer or click

                                         the Workflow Designer button above the workspace

            Step 3. Set the default target load type:

                          1. Select Tools | Options, Miscellaneous tab, and set the default target
                              load type for all sessions to Normal.

 

                                       2. Click OK.

            Step 4. Create a new Workflow:

 

 

                                  1. Select Workflows | Create.
                                  2. The Create Workflow dialog box appears 

                                  3. On the General tab, enter wf_ODS_EMPLOYEE in the Name field

                                    4. Click OK.
                                    5. The Start task appears in the Workflow Designer workspace:

                               6. Save the repository

            8.2 Exercise 2: Create Source and Target Database Connections

Creating Source Connection :

                              1. Go to Connection menu -> Click on Relational -> Click on

                                  new ->Select Oracle ->click on OK ->

                                             o Name : Native_source
                                             o Username : sdbu
                                             o Password :Rritec123
                                             o Connect String : ORCL

      

                              2. Click on OK -> Click on Close

Creating Target Connection :

                            1. Go to Connection menu -> Click on Relational -> Click on new 

                                ->Select Oracle ->Click on OK ->

                                                     o Name : Native_target
                                                     o Username : tdbu
                                                     o Password :Rritec123
                                                     o Connect String : ORCL

                                      2. Click on ok -> Click on Close

                8.3 Exercise 3: Create a Task and Run the Workflow

 

 

                                       In this exercise,Students will create a Session task

                    Step 1. Create a Session task:

1. Note that each session task is associated with a single mapping.                               Click on the Session button on the Task toolbar.
   
   
   
   

                             This appears by default at the top right of the Designer window and
                             may be part hidden. Click on the vertical bar at the left of the toolbar
                             to drag it to another position.

                         2. Move the pointer into the workspace and Click -> 

                             The Mappings dialog box appears -> Select the mapping

                              m_ODS_EMPLOYEE -> Click OK.
                         3. The session task s_m_ODS_EMPLOYEE appears in the workspace.
                         4. Save the repository.

• Note : We can also create using navigation Tasks ->Create ->provide name 

                     m_ODS_ EMPLOYEE ->Create -> Select the mapping m_ODS_EMPLOYEE 

                     -> Click OK

        Step 2. Edit the Session task’s properties:

                     1. Double-click on the s_m_ODS_EMPLOYEE Session task. Then 

                         Edit Tasks dialog appears -> Select the Properties tab

                     2. Note that the default Session Log File Name, s_m_ODS_EMPLOYEE.log, 

                        and the default Session Log File directory, $PMSessionLogDir, may be
                        overridden, but we will use the defaults for this lab.
                    3. Select the Mapping tab.
                    4. In the Navigator window, select the source sq_EMPLOYEE.
                         In the Connections section, click on the down arrow , select native_source
                         and click OK.

.

                         5. In the Navigator window, select the target ODS_EMPLOYEE.
                         6. In the Connections section, click on the down arrow , select 

                             native_target and click OK.
                        7. In the Properties section, the Target load type should be defaulted to Normal.
                        8. Scroll down (if necessary) and check the Truncate target table option
                            check box.
                        

                     9. Click  OK to close the Edit Tasks dialog box.
                  10. Save the Repository

          Step 3. Link the Workflow tasks:

                    1. Click the Link button on the Task toolbar.
                    2. Left-click and drag the cursor between the Start task and the
                       s_m_ODS_EMPLOYEE task:

                  3. Toggle off the ‘link mode’ by clicking again on the Link button, or 

                      single click on one of the objects.

         Step 4. Validate the workflow:

                 1. In order for a workflow to be valid, all tasks must be linked. 

                    Save the repository.
                 2. Locate the Save tab in the Output Window at the bottom of the 

                   Workflow Manager and view the results of the validation check:

      Step 5. Start the Workflow:

                      1. Select Workflows | Start workflow, or right-click in the workspace and

                         select Start Workflow.
                     2. The Workflow Manager dialog box appears:

 

 

                                       8.4 Exercise 4: Monitor the Workflow 

 

Step 1. Navigate in Workflow Monitor:

                         1. A workflow will show a Succeeded status if it starts up and complete

                             successfully, in and of itself. There may be several tasks within the 

                            workflow and each will succeed or fail but the workflow itself will can 

                            complete successfully. One of the only reason to workflow will fail,that  is 

                            when the Server cannot write the workflow log. The Workflow Monitor 

                            window opens automatically when a workflow is started and automatically 

                            logs into the repository, displaying the workflow status.

                       2. The Workflow Monitor has two tabs – Gant Chart and Task View. 

                           Look at the Task View.

                    

   

                        3. Notice the two objects:

                                      wf_ODS_EMPLOYEE (workflow)

                                      s_m_ODS_EMPLOYEE (session task)

                        4. The Status column shows the session status:

•  Succeeded indicates that the Informatica Server was able to                   successfully complete a workflow or task.

• Failed indicates that the Informatica Server could not complete the            workflow or task due to a fatal processing error.

 

 

        Step 2. View the Session properties:

                    1. Session properties provide detailed information about processing, 

                       such as the number of rows loaded into the target by a session task. 

                       Locate the s_m_ODS_EMPLOYEE session task under the Workflow 

                       Run column
                  2. Right click on s_m_ODS_EMPLOYEE session -> Click on get run
                      properties -> Observe source success rows and target success rows.

 

      Step 4. View the Session log:

             1. Right-click on the session in the Task Name column (Time window) 

                 and select Get Session Log, or by clicking on the Session Log button.
             2. The session log is displayed: