4 copyright © 2009, oracle. all rights reserved. defining source metadata
TRANSCRIPT
![Page 1: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/1.jpg)
4Copyright © 2009, Oracle. All rights reserved.
Defining Source Metadata
![Page 2: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/2.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 2
Objectives
After completing this lesson, you should be able to do the following:
• Define and create flat file and Oracle modules
• Import and sample flat file sources
• Import relational database objects in an Oracle module
• View source data in Data panel
![Page 3: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/3.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 3
Lesson Agenda
• Data warehouse implementation: Typical steps– Metadata to data– Relational and flat file source metadata: Difference– Data sources
• Flat file sources– Create a flat file module– Import and sample a flat file
• Oracle database sources– Create an Oracle module and import Oracle database
objects– View the source tables data
![Page 4: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/4.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 4
Data Warehouse Implementation: Typical Steps
1. Define the source metadata.
2. Define staging area metadata.
3. Define a relational dimensional model.
4. Create process flows.
5. Deploy the mappings.
6. Execute or run the mappings.
7. View the data. Part 1
Part 2
![Page 5: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/5.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 6
Metadata to Data
1. Initialize and organize the modules.
2. Import the metadata. (This gives you only the data definition.)
3. Use the metadata to define the mapping.
4. Deploy and execute the mapping to load the data.
![Page 6: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/6.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 7
Difference Between Obtaining Relational and Flat File Source Metadata
Flat filesource module:FILE_SOURCE
Staging area
The database link extracts Oracle tables metadata.
XSALES Tables
The flat files are imported and
sampled.
Oracle Databasemodule:XSALES
![Page 7: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/7.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 8
Source Module
• A module is a logical grouping of related objects under a single folder.
• Source modules hold metadata describing source systems from which you extract data.
OWB 11g does not distinguish between source and target modules
![Page 8: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/8.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 9
Lesson Agenda
• Data warehouse implementation: Typical steps– Metadata to data– Relational and flat file source metadata: Difference– Data sources
• Flat file sources– Create a flat file module– Import and sample a flat file
• Oracle database sources– Create an Oracle module and import Oracle database
objects– View the source tables data
![Page 9: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/9.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 10
Data Sources
Flat file source
customers.txt (delimited)
Relational source
Channels
Products
Cities
XSALES
file_geography_multi.csv (multi-record)
FILE_SOURCE
ORDERS_SRC
![Page 10: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/10.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 11
Creating a Flat File Source Module
12
3
![Page 11: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/11.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 12
Create Module Wizard: Connection Information
Location is the path or the drive and directory in the file system where the flat files reside.
![Page 12: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/12.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 14
Metadata to Data
1. Initialize and organize the modules.
2. Import the metadata. (This gives you only the data definition.)
3. Use the metadata to define the mapping.
4. Deploy and execute the mapping to load the data.
![Page 13: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/13.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 15
Importing Source Metadata
• You can import and sample the following types of flat files using the Flat Files Sample wizard:– Simple delimited– Fixed-length– Multirecord
![Page 14: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/14.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 16
Selecting the Flat File for Sampling
![Page 15: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/15.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 17
View the Sample File
![Page 16: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/16.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 18
Flat File Sample Wizard: Welcome
The Flat File Sample wizard provides the ability to define simple, delimited text files in as few steps as possible.
![Page 17: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/17.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 19
Describing the File
![Page 18: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/18.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 20
Sampling Simple Delimited File
The Flat File Sample Wizard allows specification of more than one character for record terminators, field delimiters and field enclosures. It recognizes X'hex-value' and x'hex-value' as hex-format where 'hex-value' is 0-9 or A-F.
![Page 19: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/19.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 21
Setting Field Properties
![Page 20: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/20.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 22
Flat File Sample Wizard: Summary
![Page 21: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/21.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 23
Advanced: Record Organization
The Sample Wizard enables you to define files that contain logical records.
![Page 22: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/22.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 24
Advanced: Selecting the File Format
Specify the file format to be Delimited or Fixed Length
![Page 23: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/23.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 25
Advanced: File Layout
Select Multi Record if file contains two or more types of records. In the Record Types page, specify the Type Value and their corresponding record names.
![Page 24: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/24.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 26
Advanced: Field Properties
This option is available in the Create Flat File wizard or in the Edit Flat File window > Structure link.
For each record type, you define the fields.
![Page 25: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/25.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 27
Quiz
OWB samples the following types of flat files (select all that apply):
a. Character-delimited files
b. Fixed-length files
c. Multirecord files
d. Multibyte fixed length
![Page 26: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/26.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 28
Quiz
Out of the following, which one describes the correct sequence of tasks you perform in OWB to use flat file sources:
a. Create a flat file module, select the file for import, and then run through the Sampling wizard to define its metadata.
b. First sample the file to define its metadata, create a flat file module, and then import the flat file.
![Page 27: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/27.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 29
Lesson Agenda
• Data warehouse implementation: Typical steps– Metadata to data– Relational and flat file source metadata: Difference– Data sources
• Flat file sources– Create a flat file module– Import and sample a flat file
• Oracle database sources– Create an Oracle module and import Oracle database
objects– View the source tables data
![Page 28: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/28.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 30
Data Sources
Flat file source
customers.txt (delimited)
Relational source
Channels
Products
Cities
XSALES
file_geography_multi.csv (multi-record)
FILE_SOURCE
ORDERS_SRC
![Page 29: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/29.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 31
Creating an Oracle Module
![Page 30: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/30.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 32
Create Module Wizard: Nameand Description
For Non-Oracle modules, you can select the access method.
ORACLE MODULE
NON-ORACLE MODULE
![Page 31: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/31.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 33
Create Module Wizard: Connection Information
![Page 32: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/32.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 34
Editing the Oracle Database Location
![Page 33: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/33.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 36
Finishing Module Creation and Proceeding to Import
You can either select "Import after finish" option in the Connection Information page or right-click the module in the Projects Navigator panel, and select Import > Database Objects.
![Page 34: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/34.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 37
Import Metadata Wizard: Filter Information
Filter the objects you want to import
![Page 35: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/35.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 38
Selecting the Tables for Import
Use the arrow to move available objects to the selected list
![Page 36: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/36.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 39
Completing the Metadata Import
![Page 37: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/37.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 40
Viewing Data
![Page 38: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/38.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 41
Quiz
To get the XSALES schema tables as sources, you will perform the following tasks (select all that apply):
a. Create a separate OWB project
b. Create an Oracle Module
c. Edit the module location to point to XSALES schema
d. Import the selected tables
![Page 39: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/39.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 42
Summary
In this lesson, you should have learned how to:
• Define and create flat file and Oracle modules using the Create Module wizard
• Import and sample flat file sources using the File Import and the Flat File Sample wizard
• Import relational database objects in an Oracle module
• View source data in Data panel
![Page 40: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/40.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 43
Practice 4-1 Overview: Defining and Importing Flat Files and a Relational Source
This practice covers the following topics:
• Creating a flat file source module
• Importing two flat files:– customers.txt (delimited)– File_geography_multi.csv (multi-record)
• Examining the XSALES Oracle database module
• Importing ORDERS_SRC table into the XSALES module.
![Page 41: 4 Copyright © 2009, Oracle. All rights reserved. Defining Source Metadata](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649eeb5503460f94bfc82d/html5/thumbnails/41.jpg)
Copyright © 2009, Oracle. All rights reserved.4 - 44