oralce大学ODI培训资料07.ppt

Organizing ODI Models and Creating ODI Datastores,Objectives,After completing this lesson, you should be able to: Organize Oracle Data Integrator (ODI) models Create datastores in ODI models Create constraints in ODI: Create mandatory columns Create keys and references in ODI models Create and check conditions Explore data Audit an ODI model or data store,Organizing Models,Model Folders and Submodels,What Is a Model Folder?,A model folder is an optional means of grouping several models together. It can be used to organize models into a meaningful tree structure; for example, models relating to customers.,Creating a Model Folder,1. In the Models view, click the New Model Folder icon, and select New Model Folder. 2. Enter the name. 3. Now you can drag models or other model folders into the folder.,What Is a Submodel?,A submodel is a group of several datastores in the same model. Data stores can be arranged manually or automatically into submodels.,Creating a Submodel,1. Select a Model. 2. Right-click and select New Sub-Model. 3. Enter the name and code.,Organizing Datastores into Submodels,The three possible ways to achieve this are: Manually: Drag datastores and submodels into submodels. Set up automatic distribution: Data stores are distributed into submodels according to a name mask. This moves all datastores or only those not already in a submodel. Masks can be applied in a specific order after reverse engineering. It is possible only when using standard reverse engineering. Use certain customized Reverse Engineering Knowledge Modules (RKMs) to distribute datastores into submodels.,Setting Up Automatic Distribution,1. Click the Distribution tab. 2. Select a distribution rule. 3. Enter the mask. 4. Set the order in which the mask is applied. Higher numbers take precedence. 5. Optionally, click the Distribution button to run this rule.,Creating Datastores,Creating Datastores,Data stores are usually created by reverse-engineering. However, you can create datastores manually: In a model Using Common Format Designer These datastores do not need to exist in the data server.,Creating a Datastore in a Model,1. Select a model. 2. Right-click and select New Datastore. 3. Enter the resource name and alias. 4. Enter the name. 5. Optionally, enter a description.,Adding Columns to a Datastore,1. Click the Columns tab. 2. Click the Add Column icon. 3. Define columns properties: Name, Type, Length, Scale, Not Null 4. Repeat and order the columns.,Constraints in ODI,What Is a Constraint in ODI?,A constraint is a statement that defines the rules enforced on data in datastores. A constraint ensures the validity of the data in a given datastore and the integrity of the data of a model. Constraints on the target datastore are used in interfaces to check the validity of the data before integration in the target.,Constraints in ODI,There are four basic types of constraints in ODI: Mandatory columns known as “not null” Keys, including: Primary keys Alternate keys Indexes References (equivalent to foreign keys): Simple: column A = column B Complex: column A = function (column B, column C) Conditions,Creating a Mandatory Column,1. Double-click the column in the Models view. 2. Click the Control tab. 3. Select the Mandatory check box. 4. Select when the constraint should be checked (Flow/Static).,Creating Keys and References,Creating a Key,1. Select the Constraints node under the data store. 2. Right-click and select New Key. 3. Enter the name. 4. Select the key or index type option. 5. Click the Columns tab. 6. Add or remove columns from the key. Any number of columns can be selected.,Checking a Key,1. Click the Control tab. 2. Select whether the key is defined in the database, and is active. 3. Select when the constraint must be checked (Flow/Static). 4. Click the Check button to perform a synchronous check of the key.,Number of duplicate rows,Creating a Reference,1. Select the Constraints node under the data store. 2. Right-click and select New Reference. 3. Enter the name. 4. Select the reference type: User Reference Complex User Reference 5. Select a parent model and table. Set the model and table to to manually enter the catalog, schema, and table name.,Creating a Simple Reference,1. Click the Columns tab. 2. Click the Add icon. 3. Select the column from the foreign key table. 4. Select the corresponding column from the primary key table. 5. Repeat for all column pairs in the reference.,Creating a Complex Reference,1. Select Complex User Reference (in the Definitions tab). 2. Click the Expression tab. 3. Set the alias for the primary key table. 4. Code the expression: Prefix with the tables aliases. Use the Expression Editor.,1. Click the Control tab. 2. Select when the constraint should be checked (Flow/Static). 4. Click the Check button to immediately check the reference.,Checking a Reference,Creating Conditions,Creating a Condition,1. Right-click the Constraints node and select New Condition. 2. Enter the name. 3. Select the Oracle Data Integrator Condition type. 4. Edit the condition clause. Prefix with the tables aliases. Use the Expression Editor. 5. Enter the error message for the condition.,Checking a Condition,1. Click the Control tab. 2. Select when the constraint should be checked (Flow/Static). 3. Click the Check button to perform a synchronous check of the condition.,Overview,Exploring and Auditing,When and Why?,Why explore and audit your data? To understand the data model To evaluate the quality of the data When? If you are using undocumented models Unimplemented business rules Totally unknown data,An Overview of the Process,1. Explore the data. Contents of datastores Distribution of values 2. Construct business rules based on your data exploration. Add constraints. Test the constraints. 3. Audit the data against your business rules. Verify data quality in batch.,Exploring Your Data,Displaying the Contents of a Datastore,1. Select the data store in the Models view. 2. Right-click and select Data. 3. In the Data window: Navigate View/Edit,Viewing the Distribution of Values,1. Select the column in the Models view. 2. Right-click, select Data. 3. In the Data window: Values Number of occurrences,Click to sort,For the AGE column, there is 1 occurrence of the value “42.0”,Analyzing the Contents of a Datastore,1. Open the data window for a datastore, as before. 2. Click the New Query icon. 3. Edit the SQL query. 4. Click OK. You can use any functions or features from the database engine.,Constructing Business Rules,Defining Business Rules in ODI,There are two ways to define business rules in ODI to maintain data quality: Automatic retrieval with other metadata Obtained by reverse engineering Addition by designers manually User-defined rules Rules can be deduced by data analysis and profiling.,From Business Rules to Constraints,Deduplication rules: Primary keys Alternate keys Unique indexes Reference rules: Simple: column A = column B Complex: column A = function (column B, column C) Validation rules: Mandatory columns Conditions,Deducing Constraints from Data Analysis,If a column has no null values, consider a mandatory constraint. If a set of columns never contains a set of values more than once, consider an alternate key. If a column contains distinct, not-null, numeric values, consider a primary key with the column flagged as mandatory. If a set of columns contains few different values: Consider an index Consider a condition based on these values If a table has a group of columns with data matching a key of the same type from another table, consider a reference.,Testing a Constraint,Synchronous data check: “Quick” check Available on the Control tab of the Constraint window For keys, references, and conditions Useful to check whether a rule is correct Requires an SQL-enabled system,Start synchronous control.,Number of erroneous rows,Auditing a Model or Datastore,1. Select the datastore or model in the Models view. 2. Right-click and select Control Check. 3. Select: Execution Agent Context Delete errors The session starts. 4. Use Operator to check the audits progress.,How to Review Erroneous Records,Select the datastore in the Models view. 1. Right-click, select Control Errors. 2. Review the erroneous rows.,Quiz,The only way to create datastores in ODI is by reverse engineering their structure from the database. True False,Quiz,In ODI, references can be represented by: Simple equalities between columns in tables Complex relationships between columns in tables Expressions that must evaluate to true or false All of the above,Summary,In this lesson, you should have learned how to: Organize ODI models Create datastores in ODI models Create constraints in ODI: Create mandatory columns Create keys and references in ODI models Create and check conditions Explore data Audit an ODI model or data store,Checklist of Lab Practice Activities,2-1: Creating Users and Repositories and Logging In 3-1: Creating an ODI Agent for Orchestrating Execution of ODI Objects 4-1: Defining the Topology 5-1: Creating a Model 6-1: Setting Up a New ODI Project 7-1: Checking Data Quality in the Model 8-1: ODI Interface: Simple Trans