Oracle Warehouse Builder 10.2 - Click by Click

1
Oracle Warehouse Builder 10.2 - Click by Click

• February 8, 2007
• Jim Raper
• Data Administration Manager
• BSS/IT
• City of Charlotte
• jraper_at_ci.charlotte.nc.us

2
DISCLAIMER
3
Building a Mapping of PeopleSoft Data

• Join Two Tables
• Build a Key Lookup
• Clean or Transform Data with an Expression
• Upsert Target Table with Results
• Deploy and Run Mapping

4
10gR2 Banner
5
10gR2 Banner
6
Log into Design Center
7
New Design Center Layout
Tool Bar
Connection Pane
Project Explorer Pane
Global Explorer Pane
8
Highlight Project Area
9
Project Expands
10
Available Databases
11
Logical Oracle Content Areas
12
Module Components
13
Warehouse Cycle of Mappings
14
(No Transcript)
15
Mapping We Plan to End Up With
16
Mapping Flow
Unique Key Generator
Join Tables
Target Table
Source Tables
Transformations
Key Lookup
17
End Result
18
Joining Tables With 10.2
19
Join ODS and Hub Tables
JOIN OBJECT
20
Select Joiner from Palette
21
Joiner Object
22
Connect Source Fields to Joiner Object
23
Open Details to Name
24
Common Field Names given _1
25
Give Join a Name and Description
26
Build Join Conditions
Joiner Properties Palette
27
Pick Fields from Input 1 and 2
28
Creating a Key Lookup
29
Purpose of Key Lookup

• Another Way to Join to a Table
• Primary use is to provide values from Table of
  Values
• Use when an Equijoin may eliminate rows

30
Select Key Lookup
31
Key Lookup Wizard
32
Defaults to key lookup
33
Enter Meaningful Object Name
34
Enter Purpose of Lookup
35
Input and Output Step
36
Minimum Enter Short Description
37
List of Possible Sources
38
Select the Source
39
Open Join Output Group
40
Use Scroll Bar to Find Field(s)
41
Click on Choice
42
Click Transfer Arrow
43
Business Unit Code Moved
44
Search for Table to Use for Lookup
45
Click on Selection
46
Match Sides of Equijoin
47
What Happens With No Match?
48
About Finished
49
Summary of the Key Lookup Process
50
Key Lookup Ready to Pass Data
51
Drag and Drop Connection -1
52
Drag and Drop Connection - 2
53
Drag and Drop Connection -3
54
Validate Mapping
55
Validation Completed Successfully
56
Data Transformation
57
Use of Expressions

• Clean the Data, handle nulls, etc.
• Conform the Data all CAPS, etc
• Conditional Logic if then else
• Dig out data from strings Substring
• Etc

58
Populate Expression
59
Expression Output
60
Edit Output Fields
61
Common Expression Transforms

• Trim off Blanks from character fields
• TRIM(INGRP1.CHAR_FLD_NAME)
• Select portions of fields
• SUBSTR(INGRP1.NUMBER_FLD_NAME,1,5)
• Handle Null Values
• NVL(INGRP1.FLD_NAME,U)
• Convert to Upper Case
• UPPER(INGRP1.FLD_NAME)
• Convert Characters to Numbers
• TO_NUMBER(INGRP1.FLD_NAME)
• If then Else Logic
• CASE Statement
• Compound Expressions and Functions

62
Define Expressions
63
Expression Builder Popup
Build Transform Logic
64
Connect Output to Next Object
65
Drag and Drop One Field at a Time
66
Drag and Drop All at Once
67
Expression Mapped to Target
68
Generating Unique Keys
69
Satellite Table Needs Unique PK
70
Sequence Near Bottom of Palette
71
Expand Sequence Generator
72
Select Next Value
73
Drag and Drop to Target Field
74
Define Output Result
75
Set Operation on Target Table
76
Loading Alternatives
77
Set Record Upsert Match
78
(No Transcript)
79
Mapping is Ready to Deploy
80
Deploy and Run Mapping
81
Deploy From Control Center
82
Deploy and Run Control
83
Configure the Running
84
Run Configuration
85
Operating Mode
86
Run in Set or Row Based Mode
87
Changed
None
88
Changed
89
Generate the Mapping Script
90
Mapping Executing
91
Executing the Mapping
92
Job Completed
93
Questions?

• Jim Raper
• Data Administration Manager
• City of Charlotte
• 600 E. 4th Str
• Charlotte, NC 28202
• 704 336 8068
• jraper_at_ci.charlotte.nc.us