1 / 34

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 Explain how locations, control centers, and default configuration relate to each other

linus-tyler
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 • Explain how locations, control centers, and default configuration relate to each other • Create target schemas or users and modules • Use the Security Node in the Globals Navigator panel • Examine roles and privileges • Register an Oracle Workflow user

  3. Lesson Agenda • Overview of the OWB Architecture • Locations and Connectors: Concepts and Usage • Control centers and locations • Configurations and control centers • Target schemas • Register OWB Users

  4. Warehouse Builder Development Cycle Design Time Runtime Reverse Engineering Design Data Flows Test Data Flows Promote in Production • Design Client • Metadata locations • Modules • Design Client • Dimensional modeling • Mappings • Process Flows • Control Center • Manager • Database Objects • Mappings • Process Flows • Control Center • Manager • Configurations • Configuration Templates Tool • Create relational and dimensional objects • Create Mappings • Create Process Flows • Deploy all objects • Deploy mappings and process flows • Execute mappings and process flows • Re-deploy all objects, and re-run Mappings, process flows • Create locations • Create Modules • Import metadata • Sample Flat files Actions

  5. 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 Design Server Run-time Server OWBSYS (Repository) OWBSYS (Repository) Workspace Design Workspace Audit Tables Audit Tables Configurationpoints to a control center Control center Configuration Template Control Center Service “execution agent”outside of DB Execute Deploy Data to be loaded ControlCenter Manager DesignCenter 2. Deploy PL/SQL packages to target schema.

  6. 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 Design Server Run-time Server 1 OWBSYS (Repository) OWBSYS (Repository) 1 Workspace 2 Design Workspace Audit Tables Audit Tables Configurationpoints to a control center Control center Configuration Template 2 Control Center Service “execution agent”outside of DB Execute Deploy Data to be loaded ControlCenter Manager DesignCenter 2. Deploy PL/SQL packages to target schema.

  7. 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 Design Server Run-time Server OWBSYS (Repository) OWBSYS (Repository) Workspace Design Workspace Audit Tables Audit Tables Configurationpoints to a control center Control center 4 5 Configuration Template Control Center Service 3 “execution agent”outside of DB Execute Deploy Data to be loaded ControlCenter Manager DesignCenter 2. Deploy PL/SQL packages to target schema.

  8. 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 Design Server Run-time Server Repository Browser OWBSYS (Repository) 6 OWBSYS (Repository) Workspace Design Reports Audit Reports Design Workspace Audit Tables Audit Tables Configurationpoints to a control center Control center Configuration Template Control Center Service “execution agent”outside of DB Execute Deploy Data to be loaded ControlCenter Manager DesignCenter 2. Deploy PL/SQL packages to target schema.

  9. OWB Topology with CCA on Oracle Target Server C$ = prefix for load table for staging DS = data source credentials (location details) ExU = execution unit Client OWB ControlCenter Service Control Center Agent Deploy Deploy DS1 DS3 DesignClient Map1 Execute Execute DS2 Oracle DB ExU1 ExU2 DB2 Local OC4J Repository Remote SQL OWB Code Source Tables Design Workspace1 AuditTables Execute Local OC4J SQL Server BrowserServer Data Schema Source Tables C$EXU1 TGT1 DesignReports AuditReports ExU3 C$EXU2 TGT2 OWBBrowser Stage Loads data from staging to target Load CT Integrate CT

  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. 3 Modules point tometadata anddata locations. • A control center manages a set of source and target locations. A location can be a target for only one control center, but a source for many control centers. 1 2 Configurationpoints to onecontrol center. One “active”configuration

  11. Lesson Agenda • Overview of the OWB Architecture • Locations and Connectors: Concepts and Usage • Control centers and locations • Configurations and control centers • Target schemas • Register OWB Users

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

  13. Creating Locations • Locations are created: • For the OWBSYS repository by default • When you define source or target modules from within Design Center • When you register a user (optional) • Default name for the OWBSYS repository location is OWB_REPOSITORY_LOCATION.

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

  15. Registering Locations After you have deployed the objects for a location or explicitly registered the location, you cannot edit or delete it. You can only update its password. You must unregister the location if you want to edit or delete it.

  16. Unregistering Locations

  17. Alternatives for Unregistering Locations • Use the OMBUNREGISTER LOCATION command to unregister locations. Or • Run the [ORACLE_HOME]/owb/misc/unregister_location.sql script. • Use this script when the control center becomes inaccessible for some reason.

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

  19. Quiz • Connectors link the source and the target locations and are implemented as database links or directories. • True • False

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

  21. Control Center and Locations • A control center manages many locations. • A target location can be managed only 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 location is registered.

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

  23. Lesson Agenda • Overview of the OWB Architecture • Locations and Connectors: Concepts and Usage • Control Centers and Locations • Target schemas • Register OWB Users

  24. Deployed tables, dimensions, PL/SQLpackages 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. Target schema Data to be loaded In OWB 11g, any OWB user that you define can serve as either a source or target user.

  25. 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 workspace. Logical design in Design Center Test physical implementation Deploy Target schema SALES Production physical implementation Deploy Target schema SALES

  26. Registering DB User as an OWB User • Only a workspace owner or a user with Warehouse Builder administrative privileges can register users. • To register a user, use either of the following: • Security > Users node in Design Center • OMBINSTALL OWB_TARGET_USER OMB Plus command • You cannot register a user with multiple workspaces • If you want to deploy to a schema from a different workspace, you would first have to unregister the location from the original workspace.

  27. Using the Security Node to Register an OWB User 1 3 2 4 No automatic creationof the target location

  28. Roles and Privileges of Warehouse Builder Users • Two default roles: EVERYONE, ADMINISTRATOR

  29. Registering an Oracle Workflow User • To deploy and execute process flows in a control center, you need to register the Oracle Workflow user. • The Workflow location points to your workflow user schema.

  30. Course: Predefined and New Objects Flat file source module: FILE_SOURCE Target user:STAGING_USER Target module: STAGING_AREA External table:CUST_EXT CUSTOMERS.TXT FILE_GEOGRAPHY_MULTI.CSV MAP_STG_CUSTOMERS Mapping Staging tables:STG_COUNTRIES STG_REGIONS MAP_STG_GEOGRAPHY Mapping DB source module: XSALES You also create a mapping: LOAD_CONTACT that uses XSALES source tables Channels, Promotions,Products, Addresses,Categories, Cities,Countries, Customers,Regions, Promo-subcategories, Promo-categories, Orders,Order_items ORDERS_SRC (imported by you) Target user: SALES_WH (You create/register this) Target module: SALES • Dimensions: CHANNELS, PRODUCTS, TIMES, CUSTOMERS, PROMOTIONS • Cube:SALES • Tables: CHANNELS_TAB, PRODUCTS_TAB, TIMES_TAB, CUSTOMERS_TAB, PROMOTIONS_TAB, ORDERS_TGT, T_IND_PART, T_PART • Mappings: Loading Dimensions and Cube Mappings, DEBUG_TEST_MAPPING, LOAD_ORDERS, PIVOT_SALES, UNPIVOT_SALES DB module: DQ Tables: SOURCE, CLEANSED, CROSSREF, TARGET Mappings: MAP_NA, MAP_NA_MM = Items with the check mark are predefined.

  31. Import the CLASS_PROJECT1.mdl File • Select File > Import > Warehouse Builder Metadata • Browse to select the class_project1.mdlfile from the /home/oracle/labs/etl1/mdl/ folder.

  32. Quiz • In OWB 11.2, any OWB user that you define can serve as either a source or target user. • True • False

  33. Summary • In this lesson, you should have learned how to: • Describe the various architectural components of Warehouse Builder • Explain how locations, control centers, and configurations relate to each other • Describe the process of registering OWB users • Describe the process of registering the Oracle Workflow user

  34. Practice 3-1 Overview: Setting Up a Pre-Populated Project • This practice covers the following topics: • Importing a CLASS_PROJECT1.mdl file • Registering target users (SALES_WH, STAGING_USER) • Registering the Oracle Workflow user

More Related