OWB

1. OWB

2. What Is Oracle Warehouse Builder? • 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)

3. Basic Process Flow of Design and Deployment Design target object metadata. 1 OWB client Map source to targetwith transformations. 3 2 Validate, generate, and deploy the code. 4 Extract source metadata: • Relational databases (Oracle, Non-Oracle) • Flat files • Mainframe (COBOL Copybooks) • Applications • XML Derive and deploy BI reportinginfrastructure. 5 OWB Workspace Sources Target warehouse and data marts 6 Extract, transform, and load data.

4. 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: Service-Oriented Architectures" in Part 2 of the course.

5. Installing Oracle Warehouse Builder 11.2 • 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.

6. OWB 11gstand-alone OWB Home Simpler OWB 11.2 Installation Server installation Stand-alone installation DB home DB home OWBSYSpreseeded OWB 11gserver SYSDBA-freesetup OWBSYSpreseeded Stand-alone installation with DB 10g R2 or 11g R1 Workflow installation files SYSDBA-freesetup Workflow installation files OWB 11gstand-alone Special scripts (Requires a few extra steps)

7. 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. • 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!

8. OWB 11.2 Server Installation DB home OWB 11gserver • The directory structure looks like this: • [ORACLE_HOME]/owb • [ORACLE_HOME]/owb/wf OWBSYSpreseeded SYSDBA-freesetup Workflow installation files

9. OWB 11gstand alone OWB Home 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 OWBSYSpreseeded SYSDBA-freesetup Workflow installation files

10. 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;

11. 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 OWB Workspace #2 Many workspace users One workspace owner

12. Workspace users’ possible job functions • Metadata design • Deploying code • Executing code • - Possible target schema Workspace Installation Use the Repository Assistant to create a workspace, its owner, and its users. Highly privileged workspace owner • - Audit tables - Run-time and deployment statistics - Physical location info Users associated with one workspace Workspace owner(schema with usernamein the database) Workspace users(schemas with usernamesin the database)

13. Job Functions Workspace Users WorkspaceOwner Design metadata (using Design Center) Yes Yes Deploy and execute code Yes Yes Administrative tasks (access to run-time/deployment statistics, physical location information, audit details) No Yes Enabled as target schema Yes Yes Security, users, and role privileges management No Yes Workspace Owners and Users: Job Functions 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.

14. 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)

15. 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 • Oracle has a built-in DBMS Scheduler that can be used to deploy Warehouse Builder schedules or to create new scheduled jobs. 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)

16. Simplified Installation of Oracle Workflow In Database 11g environment In a DB 10g environment DB 11g OWB 11g stand alone DB 10g R2 OWB 11gserver install Workflow install files in [OWB_HOME]/owb/wf/install + Workflow installation files in [ORACLE_HOME]/owb/wf/install (contains a version of WF 2.6.4 with changes for 11g) To start the installation and configuration on Linux: Run wfinstall.csh located in the [ORACLE_HOME]/owb/wf/install directory.

17. 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 • Oracle Warehouse Builder Release Notes

18. Installation OWB

19. Di Installation in einzelnen Schritten

30. In SQLPLUS den User owbsys freischalten Alter user owbsys identified by owbsys;

40. Project -Navigator: hierorientiert man sich undselektiert Objekte zumbearbeiten Locations: siesind die Verbindungzur Umwelt Globale Einstellungen wie z. B. Security Editor-FlächeHier erscheinen später unterschiedlicheEditoren Projekt-TreeHier orientiert man sich Property InspectorHier lassen sich Kontext-abhängigEinstellungen der jeweiligenObjekte in den einzelnen Editorenvornehmen Birds-View Nur im Mapping-Editor interessant Log-BereichJetzt noch nicht sichtbar erscheinen hier später Log-Fenster zu Aktionen,die im Hintergrund ausgeführt werden OMBPlus Editor Nur im Mapping-Editor interessant

42. Deploy+ Start Zooming Springen inUntermappings Gruppieren von Objekten AutoLayout Aktives Fenster Komponenten-Auswahl DebuggerMenu Projekt-Tree 7 1 Drag + Drop Drag +Drop Editor-Fläche 3 Blick auf dasGesamtmapping 4 Statusmeldungen bzw. Datensicht Struktur desMappings 6 2 Eigenschaften 5

44. 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

45. Aufrufen des Browsers