Installing and Setting Up the Warehouse Builder Environment

1. Installing and Setting Up the Warehouse Builder Environment

2. Objectives • After completing this lesson, you should be able to do the following: • Describe Oracle Warehouse Builder (OWB) • Explain OWB In-Database and identify other licenses • Identify a variety of possible installation configurations • Identify the supported sources and targets • Create the workspace and the workspace users

3. Lesson Agenda • Oracle Warehouse Builder: Introduction • Oracle Warehouse Builder Installation: • OWBSYS schema • Server and stand-alone installation • Installing on Database 10g R2 or 11g R1 • Workspace, Workspace Owners, and Users • Using the Repository Assistant • Supported operating systems (OS), sources, targets, and optional components • Classroom configuration and documentation resources

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

5. Producing Quality Information • Warehouse Builder enables the extraction, transformation, and loading of data to produce quality information in the Oracle database. Businessintelligence Action Transformation Data Information Knowledge (qualityinformation)

6. Managing the Full Life Cycle of Metadata and Data

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

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

9. OWB In-Database • Integrated with Oracle Database • Design ETL mappings • View HTML Metadata reports • Perform basic data cleansing • Use GUI or scripting language Oracle Warehouse Builder Licensing Oracle Warehouse Builder Part 1 course ODI-EE license Application Adapters Data Profiling & Quality Data Watch & Repair Part 2 course Licensing is subject to change. For latest information, see http://www.oracle.com/technology/products/warehouse/index.html

10. OWB In-Database • Integrated with Oracle Database • Design ETL mappings • View HTML Metadata reports • Perform basic data cleansing • Use GUI or scripting language Data Watch & Repair ODI-EE license Application Adapters Data Profiling & Quality Data quality solution for Oracle MDM: • Siebel Universal Customer Master • Product Information Management Data Hub • Customer Data Hub Open connectivity using code templates SOA Integration Create new metadata types Multi Configuration Navigate lineage & impact Create advanced mappings and process flows Create pluggable mappings Apply Slowly changing dimensions type 2, 3 Deploy XML file targets Use Transportable modules Design in RAC environment • Access to data and metadata in: • SAP R/3 • eBusiness Suite • Peoplesoft • Siebel Profile tables, files, applications Detect or create data rules Audit incoming data Correct data Oracle Warehouse Builder Licensing Oracle Warehouse Builder Licensing is subject to change. For latest information, see http://www.oracle.com/technology/products/warehouse/index.html

11. Connectivity Options • Is integrated with Oracle Database • Provides rich file support for reading and writing • Can connect transparently using Database Gateways • Can connect applications using the Application adapters for: • Oracle E-Business Suite • Peoplesoft • Siebel • SAP • Can connect to Oracle Master Data Management through Data Watch and Repair for MDM Adapter • Provides open connectivity using open Code Template Framework

12. BI Environment from an OWB Perspective OracleWarehouseBuilder Datasources Oracle BI OBI EEServer Data SiebelCRM Data DataWarehouse OBI EEEnterpriseSemanticLayer(RPD) OracleEBS Dashboards ETL Data objectsData quality Othersources Distributemetadata SAP/R3 Ad hoc reports OBI SEEnd-user layer DatabaseRepository Data Metadata PeopleSoft

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

14. Lesson Agenda • Oracle Warehouse Builder: Introduction • Oracle Warehouse Builder Installation: • OWBSYS schema • Server and stand-alone installation • Installing on Database 10g R2 or 11g R1 • Workspace, Workspace Owners, and Users • Using the Repository Assistant • Supported operating systems (OS), sources, targets, and optional components • Classroom configuration and documentation resources

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

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

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

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

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

20. Using OWB 11.2 with Database 10g R2 • OWB 11.2 assumes the use of Oracle Database 11g Release 2. • To use OWB 11.2 with Database 10g R2 or 11g R1: • Run a SQL script to create OWBSYS (in 11g R1, first run clean_owbsys.sql to clean the existing OWBSYS schema). • Run a SQL script to identify the OWB hometo Database 10g R2. • Unlock OWBSYS and OWBSYS_AUDIT. OWB 11gstand-alone OWBhome DBhome

21. Script for Creating OWBSYS in Database 10g R2 • Run the SQL command: • @[OWB_HOME]/owb/UnifiedRepos/cat_owb <tablespace name>; • Specify a dedicated tablespace instead of the USERS tablespace (recommended).

22. Making OWB Oracle Home Known to Database 10g R2 • Run the SQL command: • @[OWB_HOME]/owb/UnifiedRepos/reset_owbcc_home <OWB_HOME>; • Specify the full path to the OWB Oracle home. • For example: /u01/app/oracle/product/owb112

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

24. Quiz • The OWBSYS schema stores the OWB repository objects and is created as part of every new Oracle Database 11g installation. • True • False

25. Lesson Agenda • Oracle Warehouse Builder: Introduction • Oracle Warehouse Builder Installation: • OWBSYS schema • Server and stand-alone installation • Installing on Database 10g R2 or 11g R1 • Workspace, Workspace Owners, and Users • Using the Repository Assistant • Supported operating systems (OS), sources, targets, and optional components • Classroom configuration and documentation resources

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

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

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

29. Starting the Repository Assistant • On Windows, select Start > Programs > ORACLE_HOME > Warehouse Builder > Administration > Repository Assistant. • On Linux, run [ORACLE_HOME]/owb/bin/unix/reposinst.sh.

30. Using the Repository Assistant to Manage Workspaces • Each workspace has one workspace owner. • Each workspace can have zero or more workspace users.

31. Using the Repository Assistant to Manage Workspaces • DBA credentials are needed if creating a new database user as a workspace owner.

32. Dropping a Workspace Unregister the associated workspace users before dropping the workspace (recommended).

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

34. Lesson Agenda • Oracle Warehouse Builder: Introduction • Oracle Warehouse Builder Installation • OWBSYS schema • Server and stand-alone installation • Installing on Database 10g R2 or 11g R1 • Workspace, Workspace Owners, and Users • Using the Repository Assistant • Supported operating systems (OS), sources, targets, and optional components • Classroom Configuration and Documentation Resources

35. Supported Operating Systems • Oracle Warehouse Builder 11g Release 2 (11.2) is available on the same platforms supported by Oracle Database 11g Release 2 • The Design Center client is supported on both Windows(32 bit) and Linux (32 bit). • For an updated list of certified hardware platforms and operating-system versions, review the certification matrix on My Oracle Support.

36. Oracle Database Support: Source, Target, Repository • Oracle Database 8.1.7.4 is the earliest Oracle version to which Warehouse Builder 11g Release 2 can connect as a source. • Oracle Database 11.2 is recommended for the Warehouse Builder 11g Release 2 repository schema, OWBSYS. • If you have to use Oracle Database 10g R2 or 11g R1, perform the extra steps discussed in previous slides. • Oracle Database XE is not supported (no Java in the database).

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

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

39. Lesson Agenda • Oracle Warehouse Builder: Introduction • Oracle Warehouse Builder Installation: • OWBSYS schema • Server and stand-alone installation • Installing on Database 10g R2 or 11g R1 • Workspace, Workspace Owners, and Users • Using the Repository Assistant • Supported operating systems (OS), sources, targets, and optional components • Classroom configuration and Documentation Resources

40. Classroom Configuration • Gnome Linux in an Oracle VM • Access the environment using NOMachine • Oracle Database 11g Release 2 • Service name (SID) is orcl.oracle.com • Workflow Server 2.6.4 is installed and configured • owf_mgr is the workflow schema. • You will create the following in the practice: • Workspace Name: workspace1 • Workspace Owner: owb1 (used to login in the client) • Workspace User: owb1_user

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

42. Quiz • Which of the following tasks does the Repository Assistant wizard perform? • Creates a new workspace, workspace owner, and users • Creates the OWBSYS schema if the database is 10g • Drops workspace • Unregisters workspace users

43. Summary • In this lesson, you should have learned how to: • Describe Oracle Warehouse Builder (OWB) • Explain the OWB In-Database and identify other licenses • Identify a variety of possible installation configurations • Identify the supported sources and targets • Create the workspace and the workspace users

44. Practice 1-1 Overview: Creating a Workspace, Workspace Owner, and a Workspace User • This practice covers creating a workspace, workspace owner and user by using the Repository Assistant.