Wednesday, August 30, 2017

LOADING DATA FROM MULTIPLE FLAT FILES TO SINGLE TARGET TABLE

Implementing the steps for loading the data from multiple flat files to
 single oracle table

Source:
  • Let us consider the Flat files(txt files) namely
            1) flatfile1.txt
            2) flatfile2.txt
            3) flatfile3.txt
            4) flatfile4.txt
            5) flatfile5.txt
keep all these files in a folder and save it in a Local Drive.

  • These flat files are having same structure with unique data and to be considered as the source.

  • In oracle DB, create a new schema as source and grant DBA permissions to it. In this schema, create a table(KC) with the columns as FID, FILENAME, STATUS and insert the values as follows

FID             FILENAME              STATUS
-----            ---------------            ------------
  1                flatfile1                    N
  2                flatfile2                    N
  3                flatfile3                    N
  4                flatfile4                    N
  5                flatfile5                    N

  • This table could be called by refreshing variable at ODI level.

Target:

  • In Oracle DB, in the same schema create a table (CL) with columns similar to the flat files structure (i.e., the columns present in the flat files are to be taken).

  • Data is to be loaded after executing the ETL process.

Loading data from Source to Target:

  • In ODI, login to work repository. We have the source as Flat file. So in Physical Architecture select the Technology as File Technology.

  • File Technology -->New Data Server --> Name: Give any name.


  • Go to  JDBC ,
JDBC Driver: Click on search icon, keep the JDBC Driver as appears and
                        click ok.
JDBC Url      : Click on search icon, keep the Url up to jdbc:snps:dbfile and
                        delete the   remaining and click ok.


  • Data Server for source is created.
  • Now create Physical Schema for this Source Data Server.
  • Right click on the Data Server select Physical Schema.
  • In the Physical Schema (Definition), select
                 Directory (schema)          : Place the path were the flat files are saved
                 Directory (work schema): Place the same path as above


  • Now move to context and set the logical schema. Select context as Global & set Logical Schema with any name ending with “_LS”.


  • Hence, the process completed for file technology.
  • Now select the Oracle Technology in Physical Architecture for the Target.
  • Oracle Technology -->New Data Server --> Name: Give any name.
  • Give the User name & Password as the Schema and password created for the Target.


  • Move to JDBC,
          JDBC Driver: Click on search icon, keep the JDBC Driver as it appears and click ok.
          JDBC URL    : Click on Search icon, set
                                    Hostname as localhost, Port number as 1521, SID as orcl


  • Data Server is created for Target.

  • Now create Physical Schema for the Target Data Server.
  • Right click on the Data Server, select Physical Schema.
  • In the Physical Schema (Definition), select
Directory (schema)           : Point out the Schema which was created
                                           for target.
Directory (work schema) : Same as above



  • Now move to context and set the logical schema.


  • Hence, the process completed for the Oracle Technology.

  • Now go to Designer component and create a new Model folder


  • Now create a new model for source (i.e., for flat files) as follows


  • Here, the source is Flat file. So for this we have to create Datastore.
  • Right click on the source model and select New Datastore. It displays a window.


  • In this, for Resource Name: Point out the flat file name with “.txt”  extension as below


  • Now create a model for Target


  • Move to selective Reverse-Engineering and  follow as below


  • Reverse Engineering for target is completed.

  • Create a Global Variable as below


  • Move to Refreshing, set the Schema and for Select Query write the query as follows


  • Create a New Project


  • In this Project, Create a Project Variable as below


  • Move to Refreshing, set the Logical Schema and Select Query as below—


  • Now create an Interface


Now change your resource name as the global variable name .
Note: change resource name before using them in the mappings.


  • Now go for Mappings


  • Import the Knowledge Modules as per the requirement
For LKM select FILE TO SQL
For IKM select SQL CONTROL APPEND
  • Set  Flow Control as TRUE
  • Set Truncate as False


  • Create a Procedure as follows


  • Go to Execution and make as follows


  • Create a Package in this Project and arrange as below
  1. v_filecount as refresh variable
  2. v_filecount as evaluate variable and remaining variables as
  3. refresh variables

  • The arrangements should appear like below


  • Now save and execute it.
  • Check the status in Operator Component.
  • Hence, the data loaded into Target Oracle Table.

  • Also check the KC table in DB. Observe that the status was updated to “Y”.

              FID              FILENAME              STATUS
-----             ---------------            -----------
     1                  flatfile1                       Y
     2                  flatfile2                       y
     3                  flatfile3                       y
     4                  flatfile4                       y
     5                  flatfile5                       y