oralce大学ODI培训资料5

Reference
Condition
5 - 12
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Additional Metadata in ODI
• Filters:
– Apply when data is loaded from a datastore
1266 23 …
ADDRESS
23 St. Martin St. 32 South Ave. …
23 55
…
…
5-8
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model: Constraints
Description in ODI
Datastore; Column Not Null/Mandatory; Default value Primary keys; Alternate keys Not unique indexes; Alternate keys
Foreign Key
Check constraint
Oracle Data Integrator Model Concepts
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Objectives
After completing this lesson, you should be able to: • Describe the purpose of Oracle Data Integrator (ODI) models and reverse engineering in ODI • Describe methods of reverse engineering • Create ODI models by reverse engineering
5-9
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model: Constraints
• Check constraints are another type of constraints.
ห้องสมุดไป่ตู้
5-5
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model: Tables and Columns
Column CUSTOMERS table contents
ID
1001 1002 …
•
Trying to insert or update a value violating a constraint usually generates an error at the database level.
5 - 10
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
5-7
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model: Foreign Keys
• • Foreign keys (FK) define relationships between tables. A foreign key uses one or more columns from one table to reference a candidate key in another table.
Each column may or may not allow null values. Each column may have a default value.
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model
• • • ODI is strongly based on the relational paradigm. In ODI, data is handled through tabular structures defined as datastores. Datastores are used for all types of ―real‖ data structures, including flat files, Extensible Markup Language (XML) files, and Java Message Service (JMS) messages.
5 - 13
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model: Indexes
• Indexes are defined on a group of columns in a table.
– Indexes speed up access to the table data when a query selects data through this group.
•
Unique indexes are a specific type of index that prevent duplicate values.
5 - 11
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Relational Model Support in ODI
Relational Model: Keys
Candidate keys Primary key Alternate Key
ID
1001 1002 …
FIRST_N
John Clark …
LAST_NAME
Adams Gable …
CITY_ID
1266 23 …
ADDRESS
23 St. Martin St. 32 South Ave. …
All elements of relational models can be described in ODI metadata:
Relational model
Table; Column Not Null; Default value Primary keys; Alternate Keys Indexes; Unique Indexes
• • •
A candidate key can uniquely identify one row in the table. One of the keys is chosen as the primary key (PK). Other keys are called alternate keys (AK).
5-2
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
What Is a Model?
• • • A model is an ODI description of a relational data model. It is a group of datastores stored in a given schema on a given technology. A model contains metadata reverse-engineered from the ―real‖ data model, or created in ODI.
—
(is unique, primary key, or not unique)
– A constraint reference is a relationship between datastores; a constraint condition is the where clause. – Foreign keys are constraint references, not conditions.
FIRST_N
John Clark …
LAST_NAME
Adams Gable …
CITY_ID
1266 23 …
ADDRESS
23 St. Martin St.
Row (record)
…
• •
Tables store data in rows and columns. Each column has a data type.
•
Heterogeneous references:
– Link datastores from different models or technologies
•
Additional optional technical or functional metadata:
– – – – Online analytical processing (OLAP) type on datastores Slowly changing dimension behavior on columns Read-only data types or columns User-defined metadata (FlexFields)
– Optionally, a length and precision
CUSTOMERS table structure (example)
Column Name
ID
Data Type
NUMERIC(10), Not Null
•
•
5-6
FIRST_NAM VARCHAR(255), Not Null E LAST_NAME VARCHAR(255), Not Null CITY_ID ADDRESS NUMERIC(5), Not Null VARCHAR(500), Allows Nulls
5-3
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Understanding the Relational Model
5-4
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
• • Constraints enforce business rules on data. PKs, AKs, FKs, and Not Null are types of constraints.
– A customer ID must be unique. – Two customers cannot have the same first name and last name. – A customer city must be referenced in the CITIES table. – A customer must have a last name.
– They map more complex business rules.
—
For example: ―Every customer’s age must be between 16 and 120.‖
•
Constraints ensure the quality of data.
– A constraint key characterizes the identifier for an index.
FK_CUSTOMER_CITY (Foreign Key)
CITIES table
ID
CUSTOMERS table ID
1001 1002 … 1266
CITY_NAME
Newyork Boston Tokyo
FIRST_N
John Clark …
LAST_NAME
Adams Gable …
CITY_ID