1 / 37

Understanding the Warehouse Builder Architecture

Understanding the Warehouse Builder Architecture. Objectives. After completing this lesson, you should be able to do the following: Define the OWB architecture components and their location on a system

jeanne
Download Presentation

Understanding the Warehouse Builder Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Understanding the Warehouse Builder Architecture

  2. Objectives • After completing this lesson, you should be able to do the following: • Define the OWB architecture components and their location on a system • Explain how locations, control centers, and default or named configurations relate to each other • Create target schemas or users and modules • Using the Security Node in the Global Explorer panel • Examining roles and privileges • Registering an Oracle Workflow user • Associate the Oracle target modules with their respective data and metadata locations

  3. Design repository user schemas Ctrl. Ctr. Svce. Designmetadata(tables,dimensions,cubes, ETLmappings) Configuration 1 Deployed tables, dimensions, PL/SQLpackages Target schema Configuration 2 3. Execute process flows or individual mappings to load tables in target schemas. 1. Design source and target metadata, ETL mappings, process flows. Architecture for Design, Deployment, Execution Machine hosting Design repository Server hosting run-time repository Ctrl. Ctr. Svce.(“execution agent”outside of DB) Design repositoryowner schema Run-timerepository(run-time anddeploymentmetadata) Control center Configurationpoints to a control center ControlCtr. Mgr. Data to be loaded DesignCenter ControlCtr. Mgr. 2. Deploy PL/SQL packages to target schema.

  4. Design repository user schemas Ctrl. Ctr. Svce. Designmetadata(tables,dimensions,cubes, ETLmappings) Configuration 1 Deployed tables, dimensions, PL/SQLpackages Target schema Configuration 2 3. Execute process flows or individual mappings to load tables in target schemas. 1. Design source and target metadata, ETL mappings, process flows. Architecture for Design, Deployment, Execution Machine hosting Design repository Server hosting run-time repository Ctrl. Ctr. Svce.(“execution agent”outside of DB) Design repositoryowner schema 2 Run-timerepository(run-time anddeploymentmetadata) Control center Configurationpoints to a control center 2 1 ControlCtr. Mgr. Data to be loaded DesignCenter ControlCtr. Mgr. 2. Deploy PL/SQL packages to target schema.

  5. Design repository user schemas Ctrl. Ctr. Svce. Designmetadata(tables,dimensions,cubes, ETLmappings) Configuration 1 Deployed tables, dimensions, PL/SQLpackages Target schema Configuration 2 3. Execute process flows or individual mappings to load tables in target schemas. 1. Design source and target metadata, ETL mappings, process flows. Architecture for Design, Deployment, Execution Machine hosting Design repository Server hosting run-time repository 4 Ctrl. Ctr. Svce.(“execution agent”outside of DB) Design repositoryowner schema 3 Run-timerepository(run-time anddeploymentmetadata) Control center Configurationpoints to a Control Center 5 5 ControlCtr. Mgr. Data to be loaded DesignCenter ControlCtr. Mgr. 2. Deploy PL/SQL packages to target schema.

  6. Design repository user schemas Ctrl. Ctr. Svce. Designmetadata(tables,dimensions,cubes, ETLmappings) Configuration 1 Deployed Tables, Dimensions, PL/SQLpackages Target schema Configuration 2 3. Execute process flows or individual mappings to load tables in target schemas. 1. Design source and target metadata, ETL mappings, process flows. Architecture for Design, Deployment, Execution Machine hosting Design repository Server hosting run-time repository 6 Ctrl. Ctr. Svce.(“execution agent”outside of DB) Design repositoryowner schema Run-timerepository(run-time anddeploymentmetadata) Control center Configurationpoints to a control center ControlCtr. Mgr. Data to be loaded DesignCenter ControlCtr. Mgr. 2. Deploy PL/SQL packages to target schema.

  7. Design repository user schemas Ctrl. Ctr. Svce. Designmetadata(tables,dimensions,cubes, ETLmappings) Configuration 1 Deployed tables, dimensions, PL/SQLpackages Target schema Configuration 2 3. Execute process flows or individual mappings to load tables in target schemas. 1. Design source and target metadata, ETL mappings, process flows. Architecture for Design, Deployment, Execution Machine hosting Design repository Server hosting run-time repository 7 Ctrl. Ctr. Svce.(“execution agent”outside of DB) Repository Browser Design repositoryowner schema Run-timerepository(run-time &deploymentmetadata) Control center Configurationpoints to a control center ControlCtr. Mgr. Data to be loaded DesignCenter ControlCtr. Mgr. 2. Deploy PL/SQL packages to target schema.

  8. Design repository user schemas Designmetadata(tables,dimensions,cubes, ETLmappings) Configuration 1 Configuration 2 Scenarios for Implementing a Remote Run-Time Server • Control Center Service installed on the client Control Center Service Run-time repository Design repositoryowner schema Control center Target schema Deployed tables, dimensions, PL/SQLpackages Note: An Oracle database is not required on the client machine hosting the Control Center Service. You can deploy all types of mappings to the remote target without restriction.

  9. Scenarios for Implementing a Remote Run-Time • Control Center Service installed on a separate server Server Run-timerepository Local server Client OWB repository Design Center Control center Target schema Control Center Service Control Center Manager Deployed tables, dimensions, PL/SQLpackages

  10. Configurations, Control Centers, Locations • Design Center is for designing the logical model of your data warehouse. • Configurations, control centers, and locations are definitions for implementing the objects physically in the run-time environment. Modules point tometadata anddata locations. 3 • A control center manages a set of source and target locations. • A location can be managed only by a single control center. 1 One “active”configuration 2 Configurationpoints to onecontrol center.

  11. Locations Locations contain credentials and connect information for a single physical place. • Note the different types of locations • Databases • Files • Applications • Process flow and schedules • Business Intelligence

  12. Automatically Created Locations • Locations are automatically created: • For the repository owner • For any repository users that you create by using Repository Assistant or from within Design Center • When you create target users from within Design Center • When you define source or target modules from within Design Center • Default name for the repository owner location is OWB_REPOSITORY_LOCATION.

  13. Metadata and Data Locations • Each object has both a data location and a metadata location.

  14. Registering Locations

  15. Unregistering Locations

  16. Alternatives for Unregistering Locations • Use the OMBUNREGISTER LOCATION command to unregister locations. Or • Run the <OWB_HOME>\owb\misc\unregister_location.sql script. • Use this script when the control center becomes inaccessible for some reason.

  17. Connectors • Logical links between source and target location • Physically implemented as: • Database links • Directories • A location may have multiple connectors. 1 Connector Location n

  18. Control Centers • You cannot edit the DEFAULT_CONTROL_CENTER control center. • You can create additional control centers to deploy to different systems. • Only one control center can be used from Design Center at any one time. A Control Center Service is a Javaprogram running outside of thedatabase as an “execution agent.”

  19. Control Center and Locations • A control center manages many locations. • A target location can only be managed by a single control center. • Source locations, by contrast, can be used by multiple control centers. 1 Control center Locations n Grey means that the locations are registered.

  20. Configurations • Sets of physical properties for design objects • Based on the configurations, the code is generated. • Classification of configurations: • Object configuration • Example: a table configured for PARALLEL access • Named configuration • Example: PROD_CONFIGURATION owns a set of object configurations for production environment. • Active configuration • The configuration you are working in within Design Center and Control Center Manager. Only one can be active at a time. • Status bar reflects the current active configuration. • DEFAULT_CONFIGURATION – By default used by projects

  21. Multiple Named Configurations • Named configurations for multiple deployments • No need to write scripts to change tablespace names, and so on Single logicaldesign Physicalconfiguration:Production Physicalconfiguration: Development • No logging • Nonparallel • Tablespace: dev_data • Table EMP Location: loc1 • No logging • Parallel • Tablespace: prod_data • Table EMP Location: loc2

  22. Configurations and Control Centers • Each configuration maps to a control center. • Each control center is a repository. • To work in a particular control center, ensure that the configuration associated with that control center is set to Active. Default configuration DEFAULT_CONTROL_CENTER DEV_REPOSITORY DEFAULT_CONFIGURATION Active configuration PROD_CONFIGURATION PROD_CONTROL_CENTER PROD_REPOSITORY

  23. Setting the Active Configuration

  24. Assigning a Control Center to a Configuration 1 1 Namedconfiguration Locations Control center n 1 Although the user interface lets youeasily switch control center assignments fora given configuration,it is better to define one configuration for each control center.

  25. Ctrl. Ctr. Svce. Deployed tables, dimensions, PL/SQLpackages Target schema Creating Target Schemas • The target schema is the target to which you load your data and data objects such as cubes, dimensions, tables, and mappings. Control center Data to be loaded

  26. Creating Target Schemas • Implement the logical design as physical database objects by deploying the objects to a target schema. • Load target tables by executing deployed mappings. • You may have multiple target schemas associated with one Warehouse Builder repository. Logical design in Design Center Test physical implementation Deploy Target schema SALES_WH Production physical implementation Deploy Target schema SALES_WH

  27. Target schemas • Create DB objects: • - Tables - Dimensions, cubes - Mappings - Packages… Registering Target Schemas • Only a repository owner or a user with Warehouse Builder administrative privileges can register target schemas. • To register a target schema, use either of the following: • Security > Users node in Design Center • OMBINSTALL OWB_TARGET_USER OMB Plus command • You can also register an existing repository user to be a target, by using the Security node of the Global Explorer or using the Repository Assistant (see note). Created by repository owner: Target users

  28. Using the Security Node to Register a Target User 1 3 2

  29. Target Schema Location • For every target user you register, Warehouse Builder creates an Oracle database location. • Locations contain the connection details of a target user. Target schema: EXPENSE_WH 1 1 Target schema location: EXPENSE_WH_LOCATION • Username • Password • Host • Port • Service Name

  30. Viewing the Target Schema Location

  31. Roles and Privileges of Warehouse Builder Users Roles • Users • EVERYONE • ADMINISTRATOR • Repository Owner • Yes • Yes • Repository Users • Yes • No • Target Users • Yes • No

  32. Registering an Oracle Workflow User • To deploy and execute process flows in a control center • Warehouse Builder grants specific roles: ALTER USER "OWF_MGR" DEFAULT ROLE WF_PLSQL_UI, SELECT_CATALOG_ROLE,AQ_ADMINISTRATOR_ROLE;

  33. Associating Target Modules with Locations • Each Oracle target module is associated with one (or more) location that is in turn associated with a target user. • This association is required to enable all the designed objects in the module to be deployed to the correct target location. Location Module Target schema location: SALES_LOCAL • Username • Password • Host • Port • Service Name Target module: SALES If you design a dimension in the SALES target module, Warehouse Builder uses the location details to deploy to the SALES target schema. Target user Target schema: SALES

  34. Associating Target Modules with Locations: Step 1 You can select many data locations per module, but only one location is used depending on the active configuration.

  35. Associating Target Modules with Locations:Step 2

  36. Summary • In this lesson, you should have learned how to: • Describe the various architectural components of Warehouse Builder • Explain how locations, control centers, and default or named configurations relate to each other • Describe the process of creating target schemas • Describe the process of registering the Oracle Workflow user • Identify the steps to associate a target module with its respective location

  37. Practice 3-1 Overview: Setting Up a Pre-Populated Project • This practice covers the following topics: • Importing an OWB_PROJECT.mdl file • Registering a target user (SALES) • Associating the target module with the location and the target user • Registering the Oracle Workflow user

More Related