In the previous practices, you learned how to create ODI Interfaces, which transform data from one or more relational tables in the source to a relational table in the target.
In this practice, you create from scratch an ODI project and an interface to export data from a flat file to a relational table, execute and monitor the Interface with ODI, and check data quality.
Note: Completing this practice is critical for all the following practice sessions.
In this practice, you perform the following 10 steps, detailed here:
1. In Topology Navigator, define the FILE_GENERIC.C:\labs\Files\Flat_files physical schema.
2. In Topology Navigator, define the FLAT_FILES_SRC logical schema.
3. In Designer Navigator, create the Export-FF-RT project.
4. In Designer Navigator, create the Flat File1 source model.
a. Create the SRC_SALES_PERSON datastore.
b. Point to the resource: C:\labs\Files\Flat_files\SRC_SALES_PERSON.TXT.
c. Reverse-engineer and format the data (fixed length positions, data types).
5. In SQL Developer, create the RDBMS schema ODI_STAGE to host the ODI target datastore.
6. In SQL Developer, create the TRG_SALES_PERSON table to serve as the ODI datastore for the target model.
7. In Topology Navigator, create the ODI target data server, ODI_STAGE; physical schema
ODI_STAGE; and logical schema ODI_STAGE.
8. In Designer Navigator, create the ODI target model, Oracle_RDBMS1.
9. Reverse-engineer the model and check the populated TRG_SALES_PERSON datastore table.
10. Create a new ODI interface to perform the flat file to RDBMS table transformation.
Your Tasks:
1. Create a new physical schema for the source flat-file model.
a. If not connected, connect to the Work repository, DEV_ODI_REPO. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to log in.
b. Click Topology navigator. In Topology navigator, click the Physical Architecture tab,select Technologies > File. Right-click FILE_GENERIC, and then select New Physical Schema.
c. For the Directory (Schema) and Directory (Work Schema) fields, enter the path to the directory where your input flat files are located (C:\LABS\Files\Flat_files).Select the Default check box and click Yes in the Confirmation window, and then click the Save button. Close the editing window for your new physical schema. On the Information window that follows, click OK. Close the editing tab.
d. Verify that your new Physical schema was added under the FILE_GENERIC data server in the Technologies tree view.
e. Open Logical Architecture, navigate to Technologies > File, right-click File, and select New Logical Schema.
f. Enter the Logical Schema name: FLAT_FILES_SRC, and select the Physical schema FILE_GENERIC in all three contexts as shown here. Click Save and close the editing window.
g. Verify that your new Logical schema was added under the File node in the Technologies tree view.
2. Create the new project in ODI.
a. Open the Designer tab and then click Projects. In the window that opens, click the New Project button
and then select New Project to add a new project.
b. On the screen that appears, set the Name of the project to Export-FF-RT in the Name field. The Code field is filled in automatically. Click Save, and then close the tab. The newly created Export-FF-RT project now appears in the Projects pane.
3. Create a new model for the flat-file source datastore.
a. In ODI Designer, click the Models tab, and then click the New Model
. Then select the New Model option to insert a new model.
b. On the screen that follows, enter the values provided in the following table. Click the
Reverse Engineer tab. Set the Context to Development. Click the Save icon.
Parameter Value
Name Flat_File_1
Technology File
Logical Schema FLAT_FILES_SRC
4. Create a new ODI source datastore.
a. Right-click the newly created Flat_File1 model, and then select New Datastore.
b. On the screen that appears, set the Name to SRC_SALES_PERSON. Click the button
next to the Resource Name field. Navigate to the C:\labs\Files\Flat_files folder,and then select the SRC_SALES_PERSON.txt file. Click Open.
c. Click the Files tab. Set the File Format to Fixed, and then click the Columns tab.
d. On the Columns tab, click the Reverse Engineer button. The following Confirmation screen appears. Click Yes. The Column Setup Wizard appears.
e. Place the cursor, and then click the ruler at the beginning of each column as shown next. The wizard marks each column as shown in the following screens.
In this practice, you create from scratch an ODI project and an interface to export data from a flat file to a relational table, execute and monitor the Interface with ODI, and check data quality.
Note: Completing this practice is critical for all the following practice sessions.
In this practice, you perform the following 10 steps, detailed here:
1. In Topology Navigator, define the FILE_GENERIC.C:\labs\Files\Flat_files physical schema.
2. In Topology Navigator, define the FLAT_FILES_SRC logical schema.
3. In Designer Navigator, create the Export-FF-RT project.
4. In Designer Navigator, create the Flat File1 source model.
a. Create the SRC_SALES_PERSON datastore.
b. Point to the resource: C:\labs\Files\Flat_files\SRC_SALES_PERSON.TXT.
c. Reverse-engineer and format the data (fixed length positions, data types).
5. In SQL Developer, create the RDBMS schema ODI_STAGE to host the ODI target datastore.
6. In SQL Developer, create the TRG_SALES_PERSON table to serve as the ODI datastore for the target model.
7. In Topology Navigator, create the ODI target data server, ODI_STAGE; physical schema
ODI_STAGE; and logical schema ODI_STAGE.
8. In Designer Navigator, create the ODI target model, Oracle_RDBMS1.
9. Reverse-engineer the model and check the populated TRG_SALES_PERSON datastore table.
10. Create a new ODI interface to perform the flat file to RDBMS table transformation.
Your Tasks:
1. Create a new physical schema for the source flat-file model.
a. If not connected, connect to the Work repository, DEV_ODI_REPO. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to log in.
b. Click Topology navigator. In Topology navigator, click the Physical Architecture tab,select Technologies > File. Right-click FILE_GENERIC, and then select New Physical Schema.
c. For the Directory (Schema) and Directory (Work Schema) fields, enter the path to the directory where your input flat files are located (C:\LABS\Files\Flat_files).Select the Default check box and click Yes in the Confirmation window, and then click the Save button. Close the editing window for your new physical schema. On the Information window that follows, click OK. Close the editing tab.
d. Verify that your new Physical schema was added under the FILE_GENERIC data server in the Technologies tree view.
e. Open Logical Architecture, navigate to Technologies > File, right-click File, and select New Logical Schema.
f. Enter the Logical Schema name: FLAT_FILES_SRC, and select the Physical schema FILE_GENERIC in all three contexts as shown here. Click Save and close the editing window.
g. Verify that your new Logical schema was added under the File node in the Technologies tree view.
2. Create the new project in ODI.
a. Open the Designer tab and then click Projects. In the window that opens, click the New Project button
and then select New Project to add a new project.b. On the screen that appears, set the Name of the project to Export-FF-RT in the Name field. The Code field is filled in automatically. Click Save, and then close the tab. The newly created Export-FF-RT project now appears in the Projects pane.
3. Create a new model for the flat-file source datastore.
a. In ODI Designer, click the Models tab, and then click the New Model
. Then select the New Model option to insert a new model.b. On the screen that follows, enter the values provided in the following table. Click the
Reverse Engineer tab. Set the Context to Development. Click the Save icon.
Parameter Value
Name Flat_File_1
Technology File
Logical Schema FLAT_FILES_SRC
4. Create a new ODI source datastore.
a. Right-click the newly created Flat_File1 model, and then select New Datastore.
b. On the screen that appears, set the Name to SRC_SALES_PERSON. Click the button
next to the Resource Name field. Navigate to the C:\labs\Files\Flat_files folder,and then select the SRC_SALES_PERSON.txt file. Click Open.c. Click the Files tab. Set the File Format to Fixed, and then click the Columns tab.
d. On the Columns tab, click the Reverse Engineer button. The following Confirmation screen appears. Click Yes. The Column Setup Wizard appears.
e. Place the cursor, and then click the ruler at the beginning of each column as shown next. The wizard marks each column as shown in the following screens.
No comments:
Post a Comment