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 and to export data
from a flat file to a relational table. In this practice, you perform data quality control for the interface created in Practice 10-1.
First, for a certain datastore, you verify that the primary key constraint is marked static, and two columns are marked mandatory with static control.
You then create an interface using that datastore as a target.
In the interface’s Flow tab, you set STATIC_CONTROL to True. In the Controls tab, you ensure the knowledge module is set to CKM SQL (a knowledge module that you will import) and you
set maximum allowed errors to 5.
For two columns in the target datastore, you will select the Check Not Null check box so that control errors will be generated if these columns are not loaded.
You will run the interface with Flow Control set to FALSE. You will verify that there are control errors on three records, and that the three records with errors were loaded into the target datastore.
Finally, you will rerun the interface, with Flow Control set to TRUE, and verify that errors are excluded from the target datastore.
Note: Completing this practice is critical for all the following practice sessions.
Your Tasks:
1. If not connected, connect to the Work repository, WORKREP. To connect, click Connect to Repository. Select DEV_ODI_REPO from the Login Name drop-down list. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.
2. Modify INT-Exp-FF-RT (created in Practice 10-1) to enable checking data quality in the target table.
a. Click the Projects tab. Select Knowledge Modules > Check (CKM). Right-click Check (CKM) and select Import Knowledge Modules:
b. In the File import directory field, select CKM SQL and click OK. Close the Import Report window. Verify that CKM SQL was successfully imported and is shown in the tree view.
c. In the Models tab, expand Oracle_RDBMS1 > TRG_SALES_PERSON > Constraints, and then double-click TRG_SALES_PERSON_PK. Click the Control tab and verify that
Static is selected.
d. In the model Oracle_RDBMS1, expand TRG_SALES_PERSON > Columns, doubleclick the column LAST_NAME, and then click the Control tab. Select Static to enable static control. Select the Mandatory check box. Click the Save button. Repeat the same
step to enable Static control for the DATE_HIRED column. Save your changes and close the tabs.
e. In the Projects tab, expand the Export-FF-RT project, and then duplicate the interface INT-EXP-FF-RT. Open the copy of the interface and rename it as INT-EXP-FF-RTQuality,and then click the Mapping tab.
f. Delete the filter for the column SALES_PERSON_ID, as shown in the following screen,and then click the Flow tab.
g. In the Flow tab, Click Target + Staging Area (ODI_STAGE) to open the Options section. Set the static control option to true. Click the Controls tab.
h. On the Controls tab, ensure that your KM is set to CKM SQL. Set the maximum number of errors allowed to 5. Leave the other options with the default values. Click the Mapping tab.
i. In Target Datastore, select the LAST NAME column, and then select the Check Not Null check box. Repeat the same step for the DATE HIRED column. Click the Save button to save your changes.
j. Execute your Interface
. Click the Execution tab to view the execution results with the number of errors, as shown in the screen.
Note: You may need to click the Refresh
icon to view your last execution.
k. On the Models tab, right-click the TRG_SALES_PERSON target datastore and select Control > Errors. View the records with errors as shown in the screen :
l. Right-click the TRG_SALES_PERSON datastore and select View Data. Verify that the rows with errors are still inserted in the target datastore. Close the TRG_SALES_PERSON data tabs.
m. Click the INT-EXP-FF-RT-Quality tab. On the Interface screen, click the Flow tab.Set Flow Control to true. Save your changes, and then execute the interface
n. On the Models tab, right-click TRG_SALES_PERSON and select View Data. Verify that the rows with errors are excluded from the data in the target datastore. Close the tabs.
from a flat file to a relational table. In this practice, you perform data quality control for the interface created in Practice 10-1.
First, for a certain datastore, you verify that the primary key constraint is marked static, and two columns are marked mandatory with static control.
You then create an interface using that datastore as a target.
In the interface’s Flow tab, you set STATIC_CONTROL to True. In the Controls tab, you ensure the knowledge module is set to CKM SQL (a knowledge module that you will import) and you
set maximum allowed errors to 5.
For two columns in the target datastore, you will select the Check Not Null check box so that control errors will be generated if these columns are not loaded.
You will run the interface with Flow Control set to FALSE. You will verify that there are control errors on three records, and that the three records with errors were loaded into the target datastore.
Finally, you will rerun the interface, with Flow Control set to TRUE, and verify that errors are excluded from the target datastore.
Note: Completing this practice is critical for all the following practice sessions.
Your Tasks:
1. If not connected, connect to the Work repository, WORKREP. To connect, click Connect to Repository. Select DEV_ODI_REPO from the Login Name drop-down list. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.
2. Modify INT-Exp-FF-RT (created in Practice 10-1) to enable checking data quality in the target table.
a. Click the Projects tab. Select Knowledge Modules > Check (CKM). Right-click Check (CKM) and select Import Knowledge Modules:
b. In the File import directory field, select CKM SQL and click OK. Close the Import Report window. Verify that CKM SQL was successfully imported and is shown in the tree view.
c. In the Models tab, expand Oracle_RDBMS1 > TRG_SALES_PERSON > Constraints, and then double-click TRG_SALES_PERSON_PK. Click the Control tab and verify that
Static is selected.
d. In the model Oracle_RDBMS1, expand TRG_SALES_PERSON > Columns, doubleclick the column LAST_NAME, and then click the Control tab. Select Static to enable static control. Select the Mandatory check box. Click the Save button. Repeat the same
step to enable Static control for the DATE_HIRED column. Save your changes and close the tabs.
e. In the Projects tab, expand the Export-FF-RT project, and then duplicate the interface INT-EXP-FF-RT. Open the copy of the interface and rename it as INT-EXP-FF-RTQuality,and then click the Mapping tab.
f. Delete the filter for the column SALES_PERSON_ID, as shown in the following screen,and then click the Flow tab.
g. In the Flow tab, Click Target + Staging Area (ODI_STAGE) to open the Options section. Set the static control option to true. Click the Controls tab.
h. On the Controls tab, ensure that your KM is set to CKM SQL. Set the maximum number of errors allowed to 5. Leave the other options with the default values. Click the Mapping tab.
i. In Target Datastore, select the LAST NAME column, and then select the Check Not Null check box. Repeat the same step for the DATE HIRED column. Click the Save button to save your changes.
j. Execute your Interface
. Click the Execution tab to view the execution results with the number of errors, as shown in the screen.Note: You may need to click the Refresh
icon to view your last execution.k. On the Models tab, right-click the TRG_SALES_PERSON target datastore and select Control > Errors. View the records with errors as shown in the screen :
l. Right-click the TRG_SALES_PERSON datastore and select View Data. Verify that the rows with errors are still inserted in the target datastore. Close the TRG_SALES_PERSON data tabs.
m. Click the INT-EXP-FF-RT-Quality tab. On the Interface screen, click the Flow tab.Set Flow Control to true. Save your changes, and then execute the interface
n. On the Models tab, right-click TRG_SALES_PERSON and select View Data. Verify that the rows with errors are excluded from the data in the target datastore. Close the tabs.
No comments:
Post a Comment