After the models are defined, you need to check the quality of the data in these models. In this
practice, you check the quality of data in the models and define constraints on models for the
given sample application.
First you create a new model, HSQL_ORDERS_APPLICATION, as a duplicate of the model
HSQL_SRC.
You then create a referential constraint on the SRC_CUSTOMER table's CITY_ID column, using
SRC_CITY as the parent table.
Next, you create a condition constraint on the SRC_CUSTOMER table:
Length(SRC_CUSTOMER.PHONE) > 6
Note: Completing this practice is critical for all the following practice sessions.
Your Tasks:
1. Create a new model, HSQL_ORDERS_APPLICATION, as a duplicate of the model HSQL_SRC.
a. In Designer, open the Models tab. Right-click HSQL_SRC model and select Duplicate selection to duplicate the datastore. Click Yes.
b. Open the Copy of the HSQL_SRC model and rename it as HSQL Orders Application.For Code, enter HSQL_ORDERS_APPLICATION. Click Save to save the model. If you get a warning message, click Yes to finish saving.
2. Create a Reference constraint between the SRC_CUSTOMER and SRC_CITY datastores in the new model. This reference is on the CITY_ID column.
a. Expand the HSQL Orders Application model, and expand the SRC_CUSTOMER datastore.
b. Select the Constraints node, right-click, and select the New Reference option.
c. From the Table drop-down list, select the SRC_CITY table. The name of the constraint is automatically generated. Click the Columns tab.
d. Click the
Add icon, and use drop-down lists to select the CITY_ID column for both
tables of reference. (Note that the Primary Table column is City, which you want to
also change to CITY_ID.) Click the Save button.
3. Create a Condition constraint on SRC_CUSTOMER to check that the phone number contains six or more characters.
a. Select the Constraints node again for SRC_CUSTOMER, right-click, and select the New Condition option.
to validate your expression syntax.
c. On the Oracle Data Integration Information screen, click OK. Click the Save button to add the condition, and the close the tab.
4. Run a static check on the HSQL Orders Application model.
a. Select the HSQL Orders Application model tab and click Control tab.
b. Select Knowledge Module CKM HSQL.HandsOnLoads. Click the Save button. Close the HSQL Orders Application model tab.
5. Select the HSQL Orders Application model in the tree view, right-click, and select Control >Check.
a. In the Execution dialog box, select Development context and then click OK.
b. Click OK when the Information dialog box notifies you that the session has started.
6. Click the Operator Navigator icon tab
. The Operator window appears. Click the Hierarchical Sessions tab. Expand the All Executions node. Expand the HSQL Orders Application session. Expand its Steps node. The session should appear complete,containing steps marked with check marks and warnings, as shown here.
Note: You can optionally review the content of this session and see the different SQL
commands issued to perform the check operations.
7. Review the errors detected in the SRC_CUSTOMER datastore.
a. Click the Designer Navigator tab. In the Designer window, expand the HSQL Orders
Application model, select the SRC_CUSTOMER datastore, right-click, and select
Control > Errors.
b. View the table that lists the errors detected in your table. You have one join error and
seven invalid phone numbers. Each line also contains the content of the invalid record.
c. Verify the following:
practice, you check the quality of data in the models and define constraints on models for the
given sample application.
First you create a new model, HSQL_ORDERS_APPLICATION, as a duplicate of the model
HSQL_SRC.
You then create a referential constraint on the SRC_CUSTOMER table's CITY_ID column, using
SRC_CITY as the parent table.
Next, you create a condition constraint on the SRC_CUSTOMER table:
Length(SRC_CUSTOMER.PHONE) > 6
Note: Completing this practice is critical for all the following practice sessions.
Your Tasks:
1. Create a new model, HSQL_ORDERS_APPLICATION, as a duplicate of the model HSQL_SRC.
a. In Designer, open the Models tab. Right-click HSQL_SRC model and select Duplicate selection to duplicate the datastore. Click Yes.
b. Open the Copy of the HSQL_SRC model and rename it as HSQL Orders Application.For Code, enter HSQL_ORDERS_APPLICATION. Click Save to save the model. If you get a warning message, click Yes to finish saving.
2. Create a Reference constraint between the SRC_CUSTOMER and SRC_CITY datastores in the new model. This reference is on the CITY_ID column.
a. Expand the HSQL Orders Application model, and expand the SRC_CUSTOMER datastore.
b. Select the Constraints node, right-click, and select the New Reference option.
c. From the Table drop-down list, select the SRC_CITY table. The name of the constraint is automatically generated. Click the Columns tab.
d. Click the
Add icon, and use drop-down lists to select the CITY_ID column for bothtables of reference. (Note that the Primary Table column is City, which you want to
also change to CITY_ID.) Click the Save button.
3. Create a Condition constraint on SRC_CUSTOMER to check that the phone number contains six or more characters.
a. Select the Constraints node again for SRC_CUSTOMER, right-click, and select the New Condition option.
- Enter Phone Number Length in the Name field.
- Select Oracle Data Integrator Condition from the Type drop-down list.
- Enter the following expression in the Where field: o LENGTH(SRC_CUSTOMER.PHONE) > 6.
- the Message field, enter A phone number should contain more than 6 characters.Note: You can also use the Expression Editor icon
to graphically edit the expression.
to validate your expression syntax.c. On the Oracle Data Integration Information screen, click OK. Click the Save button to add the condition, and the close the tab.
4. Run a static check on the HSQL Orders Application model.
a. Select the HSQL Orders Application model tab and click Control tab.
b. Select Knowledge Module CKM HSQL.HandsOnLoads. Click the Save button. Close the HSQL Orders Application model tab.
5. Select the HSQL Orders Application model in the tree view, right-click, and select Control >Check.
a. In the Execution dialog box, select Development context and then click OK.
b. Click OK when the Information dialog box notifies you that the session has started.
6. Click the Operator Navigator icon tab
. The Operator window appears. Click the Hierarchical Sessions tab. Expand the All Executions node. Expand the HSQL Orders Application session. Expand its Steps node. The session should appear complete,containing steps marked with check marks and warnings, as shown here.Note: You can optionally review the content of this session and see the different SQL
commands issued to perform the check operations.
7. Review the errors detected in the SRC_CUSTOMER datastore.
a. Click the Designer Navigator tab. In the Designer window, expand the HSQL Orders
Application model, select the SRC_CUSTOMER datastore, right-click, and select
Control > Errors.
b. View the table that lists the errors detected in your table. You have one join error and
seven invalid phone numbers. Each line also contains the content of the invalid record.
c. Verify the following:
- How many customers have an invalid CITY_ID? (1)
- How many customers have invalid phone number? (7)
d. Close these tabs.
No comments:
Post a Comment