OWB

OWB
1-1
Copyright © 2009, Oracle. All rights reserved.
What Is Oracle Warehouse Builder?
•
•
1-2
Oracle Warehouse Builder (OWB) is a comprehensive
data integration, data warehousing, data quality, and
metadata management solution designed for the Oracle
database.
OWB is an integral part of Oracle Database and is installed
as part of every database installation (except Oracle
Database XE)
Copyright © 2009, Oracle. All rights reserved.
Basic Process Flow of Design and Deployment
1
OWB
client
Design target
object metadata.
source to target
3 Map
with transformations.
2
Validate, generate, and
4 deploy the code.
Extract source metadata:
 Relational databases (Oracle,
Non-Oracle)
 Flat files
 Mainframe (COBOL Copybooks)
 Applications
 XML
Derive and deploy
OWB
Workspace
5 BI reporting
infrastructure.
Sources
6
Extract, transform,
and load data.
1-3
Target
warehouse and
data marts
Copyright © 2009, Oracle. All rights reserved.
Full Support for SOA
•
•
Warehouse Builder fully supports
service-oriented architectures
for data integration.
OWB 11.2 can be used to:
– Publish a Web service
– Consume an existing Web service
•
The new Application Server module
is introduced to support Web services.
– Two types of Application Server modules: Public
and Private
For more information, refer to the "Appendix C: ServiceOriented Architectures" in Part 2 of the course.
1-5
Copyright © 2009, Oracle. All rights reserved.
Installing Oracle Warehouse Builder 11.2
•
•
•
•
1-6
Oracle Database 11g includes the complete
installation of OWB.
OWB In-Database functionality is licensed with the
Oracle Database while other functionality requires
additional licensing.
Oracle Database hosts the OWB repository and
serves as the transformation engine.
Refer to the Oracle Warehouse Builder Installation
and Administration guide for details.
Copyright © 2009, Oracle. All rights reserved.
Simpler OWB 11.2 Installation
Server installation
Stand-alone installation
DB home
OWB 11g
stand-alone
OWB 11g
server
SYSDBA-free
setup
OWBSYS
preseeded
SYSDBA-free
setup
Workflow
installation
files
Stand-alone installation with
DB 10g R2 or 11g R1
Workflow
installation
files
OWB Home
OWB 11g
stand-alone
Special
scripts
(Requires a few extra steps)
1-7
DB home
OWBSYS
preseeded
Copyright © 2009, Oracle. All rights reserved.
OWBSYS Schema
•
•
•
Stores all the OWB repository
objects
Is created as part of every
Oracle Database installation
Is registered as an OWB user
– Administrators and
developers generally register
other database users and
assign them required
privileges, rather than using
the OWBSYS account directly.
•
1-8
Needs to be unlocked before
any workspace is created
OWBSYS (Unified
Repository)
• Single copy of OWB
database objects
• Necessary grants on roles,
system and object privileges
Eliminates the need for
DBA/SYSDBA credentials to define a
workspace repository!
Copyright © 2009, Oracle. All rights reserved.
OWB 11.2 Server Installation
DB home
OWB 11g
server
OWBSYS
preseeded
The directory structure looks like
this:
•[ORACLE_HOME]/owb
•[ORACLE_HOME]/owb/wf
SYSDBA-free
setup
Workflow
installation
files
1-9
Copyright © 2009, Oracle. All rights reserved.
OWB 11.2 Stand-Alone Installation
Reasons for stand-alone installation:
•
To use the Design Client on a
developer’s computer
•
To deploy to Oracle Database 10g
R2 or 11g R1 with OWB repository
hosted on it
•
To run Control Center Agent on a
client
DB home
OWBSYS
preseeded
SYSDBA-free
setup
OWB 11g
stand alone
Workflow
installation
files
OWB Home
1 - 10
Copyright © 2009, Oracle. All rights reserved.
Unlock OWBSYS and OWBSYS_AUDIT
•
Run the following SQL commands:
– alter user OWBSYS identified by <password>
account unlock;
– alter user OWBSYS_AUDIT identified by
<password> account unlock;
1 - 11
Copyright © 2009, Oracle. All rights reserved.
Warehouse Builder Workspace
•
To define and work with your data and metadata and to
use the repository, you must create a workspace.
– Use the Repository Assistant.
•
Workspace is owned by a workspace owner.
Control Center Service
Oracle Database instance
Repository
(OWBSYS schema)
OWB
Workspace #1
Many
workspace users
1 - 12
OWB
Workspace #2
One workspace owner
Copyright © 2009, Oracle. All rights reserved.
Workspace Installation
Use the Repository Assistant to
create a workspace, its owner, and its users.
Workspace users’
possible job functions
Highly privileged
workspace owner
- Metadata design
- Deploying code
- Executing code
- Possible target schema
- Audit tables
- Run-time and
deployment statistics
- Physical location info
Workspace users
(schemas with usernames
in the database)
1 - 13
Users associated
with one workspace
Workspace owner
(schema with username
in the database)
Copyright © 2009, Oracle. All rights reserved.
Workspace Owners and Users: Job Functions
Job Functions
Workspace
Users
Workspace
Owner
Design metadata (using Design Center)
Yes
Yes
Deploy and execute code
Yes
Yes
Administrative tasks (access to runtime/deployment statistics, physical
location information, audit details)
No
Yes
Enabled as target schema
Yes
Yes
Security, users, and role privileges
management
No
Yes
The workspace owner can define an intermediate “DBA” role. Users
can then be assigned this role, allowing them to perform limited
administrative tasks without the privileges of the workspace owner.
1 - 14
Copyright © 2009, Oracle. All rights reserved.
OWB Product Components
•
•
•
Design Center (owbclient.sh/bat)
Repository Assistant (reposinst.sh/bat)
OMB*Plus scripting client (OMBPlus.sh/bat)
•
Start/stop Repository Browser OC4J Server
([startOwbInst.sh/bat/][stopOwbInst.sh/bat])
Start Repository Browser (openRAB.sh/bat or
openDB.sh/bat)
•
•
Start stand-alone Control Center Service
(run_service.sh/bat)
•
Start/stop Control Center Agent
([ccastart.sh/bat]/[ccashut.sh/bat])
•
Control Center Agent Administration
(cca_admin.sh/bat)
1 - 15
Copyright © 2009, Oracle. All rights reserved.
Installing Optional Components
Optional
Component
Supported Versions
Oracle Workflow
• Oracle Workflow 2.6.4 (included in the database installation in
the [ORACLE HOME]/owb/wf folder)
Oracle Job
Scheduler
•
Third-Party Name
and Address Data
• You need the following from one of the certified
vendors listed on Oracle Technology Network:
– Regional data libraries
– Name and address adapter software
Business
Intelligence (BI) tools
• Oracle Business Intelligence Enterprise Edition (OBI EE)
• Oracle Business Intelligence Standard Edition (OBI SE)
1 - 16
Oracle has a built-in DBMS Scheduler that can be used to
deploy Warehouse Builder schedules or to create new
scheduled jobs.
Copyright © 2009, Oracle. All rights reserved.
Simplified Installation of Oracle Workflow
In Database 11g environment
DB 11g
In a DB 10g environment
OWB 11g stand alone
OWB 11g
server install
Workflow installation
files in
[ORACLE_HOME]/owb/
wf/install (contains a
version of WF 2.6.4
with changes for 11g)
Workflow install files
in
[OWB_HOME]/owb/wf/
install
DB 10g R2
+
To start the installation and configuration on Linux:
Run wfinstall.csh located in the [ORACLE_HOME]/owb/wf/install
directory.
1 - 17
Copyright © 2009, Oracle. All rights reserved.
Oracle Warehouse Builder Documentation and
Resources
•
Oracle Warehouse Builder on OTN
– http://www.oracle.com/technology/products/warehouse/index
.html
•
Reference Guides:
– Oracle Warehouse Builder Installation and Administration
Guide
– Oracle Warehouse Builder Concepts
– Oracle Warehouse Builder Sources and Targets Guide
– Oracle Warehouse Builder ETL and Data Quality Guide
•
1 - 18
Oracle Warehouse Builder Release Notes
Copyright © 2009, Oracle. All rights reserved.
Installation OWB
1 - 19
Copyright © 2009, Oracle. All rights reserved.
Di Installation in einzelnen Schritten
1 - 20
Copyright © 2009, Oracle. All rights reserved.
1 - 21
Copyright © 2009, Oracle. All rights reserved.
1 - 22
Copyright © 2009, Oracle. All rights reserved.
1 - 23
Copyright © 2009, Oracle. All rights reserved.
1 - 24
Copyright © 2009, Oracle. All rights reserved.
1 - 25
Copyright © 2009, Oracle. All rights reserved.
1 - 26
Copyright © 2009, Oracle. All rights reserved.
1 - 27
Copyright © 2009, Oracle. All rights reserved.
1 - 28
Copyright © 2009, Oracle. All rights reserved.
1 - 29
Copyright © 2009, Oracle. All rights reserved.
1 - 30
Copyright © 2009, Oracle. All rights reserved.
In SQLPLUS den User owbsys freischalten
Alter user owbsys identified by owbsys;
1 - 31
Copyright © 2009, Oracle. All rights reserved.
1 - 32
Copyright © 2009, Oracle. All rights reserved.
1 - 33
Copyright © 2009, Oracle. All rights reserved.
1 - 34
Copyright © 2009, Oracle. All rights reserved.
1 - 35
Copyright © 2009, Oracle. All rights reserved.
1 - 36
Copyright © 2009, Oracle. All rights reserved.
1 - 37
Copyright © 2009, Oracle. All rights reserved.
1 - 38
Copyright © 2009, Oracle. All rights reserved.
1 - 39
Copyright © 2009, Oracle. All rights reserved.
1 - 40
Copyright © 2009, Oracle. All rights reserved.
Project -Navigator: hier
orientiert man sich und
selektiert Objekte zum
bearbeiten
Locations: sie
sind die Verbindung
zur Umwelt
Globale Einstellungen
wie z. B. Security
Editor-Fläche
Hier erscheinen später unterschiedliche
Editoren
Projekt-Tree
Hier orientiert man sich
Property Inspector
Hier lassen sich Kontext-abhängig
Einstellungen der jeweiligen
Objekte in den einzelnen Editoren
vornehmen
Birds-View
Nur im Mapping-Editor interessant
Log-Bereich
OMBPlus Editor
Jetzt noch nicht sichtbar erscheinen
hier später Log-Fenster zu Aktionen,
die im Hintergrund ausgeführt werden
Nur im Mapping-Editor interessant
1 - 41
Copyright © 2009, Oracle. All rights reserved.
1 - 42
Copyright © 2009, Oracle. All rights reserved.
Deploy
+ Start
Auto
Layout
Zooming
Springen in
Untermappings
Gruppieren
von Objekten
Aktives Fenster
Projekt-Tree
1
Debugger
Menu
Komponenten-Auswahl
7
Drag +
Drop
Blick auf das
Gesamtmapping
4
Struktur des
Mappings
2
1 - 43
Editor-Fläche 3
Statusmeldungen
bzw. Datensicht
5
Copyright © 2009, Oracle. All rights reserved.
6
Eigenschaften
1 - 44
Copyright © 2009, Oracle. All rights reserved.
Aggregation (z. B. Sum/Group By)
Umwandeln von Object Types
Distinct Option
Expressions (z. B. 3+4)
Filter (Where - Klausel)
Join-Bedingung
Lookup – Tabellen einlesen
Fuzzy-Match, String-Abgleich
Adress-Daten-Bereinigung
Pivotieren von Sätzen + Spalten
Union / Set / Minus
Order By
Multiple Inserts
Erstellen von Table Functions
PL/SQL Functions
Pivotieren von Sätzen + Spalten
1 - 45
Copyright © 2009, Oracle. All rights reserved.
Aufrufen des Browsers
1 - 46
Copyright © 2009, Oracle. All rights reserved.
1 - 47
Copyright © 2009, Oracle. All rights reserved.
1 - 48
Copyright © 2009, Oracle. All rights reserved.
1 - 49
Copyright © 2009, Oracle. All rights reserved.
1 - 50
Copyright © 2009, Oracle. All rights reserved.
1 - 51
Copyright © 2009, Oracle. All rights reserved.
1 - 52
Copyright © 2009, Oracle. All rights reserved.
1 - 53
Copyright © 2009, Oracle. All rights reserved.