1 / 41

Administrative Tasks in Warehouse Builder

Administrative Tasks in Warehouse Builder. Objectives. After completing this lesson, you should be able to: Set up and manage multiple named configurations Use configuration templates Describe OWB configurations in RAC environments Schedule ETL jobs. Lesson Agenda.

teenie
Download Presentation

Administrative Tasks in Warehouse Builder

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. Administrative Tasks in Warehouse Builder

  2. Objectives • After completing this lesson, you should be able to: • Set up and manage multiple named configurations • Use configuration templates • Describe OWB configurations in RAC environments • Schedule ETL jobs

  3. Lesson Agenda • Set up and manage multiple named configurations • Explain the concept of multi-configuration • Relate locations, control centers, and named configurations • Describe how to apply multi-configuration to a Dev-QA-Prod scenario • Use OWB configuration user interface to easily inspect and manage multiple configurations • Use configuration templates • Describe OWB configurations in RAC environments • Schedule ETL jobs

  4. Enterprise ETL License Extends Core In-Database ETL Oracle Warehouse Builder OWB In-Database • Integrated with Oracle Database 11g R2 • Design ETL mappings • View HTML metadata reports • Perform data cleansing • Use GUI or scripting language Application Adapters Data Profiling & Quality Data Watch & Repair ODI-EE license • Access to data and metadata in: • SAP R/3 • E-Business Suite • PeopleSoft • Siebel • Profile tables, files, applications • Detect or create data rules • Audit incoming data • Correct data Integrate with Oracle MDM: • Siebel Universal Customer Master • Product Information Management Data Hub • Customer Data Hub • Create new metadata types • Promote from Dev>QA>PROD • Navigate lineage & impact • Propagate metadata changes • Create advanced mappings and process flows logic • Create pluggable mappings • Apply Slowly changing dimensions type 2 and 3 • Deploy XML file targets • Use transportable modules • Design in RAC environment

  5. Scenario Requiring Multiple Configurations • Problem: How do I configure and transition among development, QA, and production environments? Required development settings DevelopmentPhysical implementation • No logging • Nonparallel • Tablespace: dev_data • Table EMP Location: loc1 QAPhysical implementation Required production settings • No logging • Parallel • Tablespace: prod_data • Table EMP Location: loc2 Production Physicalimplementation

  6. Loose Coupling of Metadatawith Physical Implementations • OWB supports one logical model and multiple physical run times: DevelopmentPhysical implementation Configuration 1: Its control center points to the Development run-time location. QAPhysical implementation Configuration 2: Its control center points to the QA run-time location. Production Physicalimplementation Configuration 3: Its control center points to the Production run-time location.

  7. Physicalconfiguration:Production Each Configuration Records the Physical Settings of Its Target Environment • Named configurations for multiple deployments • No need to write scripts to change logging, tablespace names, and so on Single logicaldesign Physicalconfiguration:Development • No logging • Nonparallel • Tablespace: dev_data • Table EMP Location: loc1 • No logging • Parallel • Tablespace: prod_data • Table EMP Location: loc2

  8. Multiple Named Configurations: Why and How • Why should I use multiple named configurations? • To maintain a single design for objects • To change the generated code per target database • To deploy, without hard-coding and writing scripts, to different repositories in a controlled way • How do I use multiple named configurations? • Separate logical from physical with a loose coupling of logical design and physical implementation. • Define named configurations (holding physical information for objects) for each of your run times. • Active configuration substitutes physical properties into generated code. • Deploy code into its targeted database.

  9. Deploy Deploy Deploy Generate Generate Generate Deploying a Selected Configuration • A single logical design can be deployed to different repositories by applying different configurations. Select an activeconfiguration. DevelopmentPhysical implementation Code Apply Devconfiguration. QAPhysical implementation Singlerepository Code Apply QAconfiguration. Production New software release Apply Prodconfiguration. Code

  10. Configurations, Control Centers, and Locations 1 1 Namedconfiguration Locations Control center n 1 3 • Control center can point to multiple source and target locations. 1 One “active”configuration Configurationpoints to onecontrol center. 2

  11. Creating Additional Configurations and Control Centers 2 1 4 3 Control center agents are required only in code template-based mappings, taught in a later lesson.

  12. Only one configuration can be active at a time. There are two ways to set the active configuration. Setting the Active Configuration 1 2 To quickly check which configuration is active, examine the Design Center status bar.

  13. Highlightdifferences Configuring Objects Across All Configurations

  14. Cloning a Database to Create Identical Dev-QA-Prod Systems • In contrast to deploying different versions, if you want to rapidly roll out a set of identical systems, you should consider cloning the database. • The process of cloning typically changes host names and service names. • The host names and service names are stored in the control center repository in each target repository schema. You need to change their persisted values after cloning. Development Host name: Xsvce. Name: Xschema: A QA Host name: Ysvce. Name: Yschema: A Designrepository Production Clone and then change host and service names. Host name: Asvce. Name: Aschema: A Host name: Zsvce. Name: Zschema: A

  15. Practice 1-1 Overview: Using Multiple Named Configurations • This practice covers the following topics: • Defining multiple configurations • Comparing an object’s differing values across different configurations

  16. Lesson Agenda • Set up and manage multiple named configurations • Use configuration templates • Describe OWB configurations in RAC environments • Schedule ETL jobs

  17. Physicalsystem Location Controlcenter Associates Owns Configurationtemplate Location Implemented in Logicalsystem Configprops Object Module Owns Configprops Object Sets/overwrites Module Configprops Object Configuration Templates

  18. Creating a New Configuration Template Configuration template has a column called “Seeded Default” that contains the default repository values. The column to the right of the defaults contains the property values for a different configuration template.

  19. Associating a Configuration Template with a Configuration The Configurations drop-down list is now on the toolbar, and is therefore easily visible and accessible. Select Create New Configuration to create a new configuration. Create Configuration Wizard lets you select a different configuration template if you want to override the system’s default configuration values.

  20. Overriding Default Configuration Settings If DEFAULT_CONFIGURATION is active, note that the CHANNELS table configuration shows default settings. When PRD_CONFIG is active, the settings you applied for the PARALLEL property of the Tables object type is effective. So the CHANNELS table uses the new configuration settings.

  21. Practice 1-2 Overview: Using Configuration Templates • In the hands-on practice, you will perform the following: • Create a new configuration template • Associate the new template with a new configuration

  22. Lesson Agenda • Set up and manage multiple named configurations • Use configuration templates • Describe OWB configurations in RAC environments • Schedule ETL jobs

  23. Control Center Service Failover on RAC 1. Control Center Service (CCS) node fails. 2. CCS goes down. 3. CCS comes up on a different node of the RAC. 4. CCS resumes activities. Control CenterService Control center Single logical instance

  24. RAC Deployment Control center Locations are defined using net service names, not host:port:service. Single logical instance Designrepository Control center

  25. Supported and Unsupported RAC Features • Load balancing • Connection load balancing based on server (supported) • Client-side load balancing (not supported) • Failover types • Connect failover: node, listener, instance down (supported with properly configured tnsnames.ora) • Transparent Application Failover (not supported) • Multiple concurrent Control Center Services (not supported)

  26. Steps for Setting Up OWB in a RAC Environment Install OWB. 1. Decide whether to use OWB with shared storage or with nonshared local disks on each RAC node. Same path for each OWB install. 2. Choose Cluster or Local Installation; run root.sh on each node. Configure database and OWB. 3. Install OWB repository only once, to one node. If using a shared disk, install repository there. 4. Use Runtime Assistant on other nodes to register the repository. 5. On the database, set MAX_COMMIT_PROPAGATION_DELAY to 0. 6. Replicate RTREPOS.PROPERTIES and TNSNAMES.ORA on each node. 7. Define OWB locations with TNS names, not host:port:service. Monitor nodes. 8. Use OWB Browser to monitor, enable, or disable Control Center Service on a node. Troubleshoot. 9. Search logs on nodes, run helpful utilities, avoid common mistakes.

  27. Lesson Agenda • Set up and manage multiple named configurations • Use configuration templates • Describe OWB configurations in RAC environments • Schedule ETL jobs

  28. Scheduling Concepts Schedule_Module 1 EVERY_2_HOURS MONTHLY_SALES SALES_EVERY_30_MIN Schedule_Module 2 HOURLY_LOAD

  29. Creating a Schedule Module

  30. Creating a Schedule

  31. Create Schedule Wizard: Name and Description

  32. Create Schedule Wizard: Start and End Time

  33. Create Schedule Wizard: Frequency and Repeat Interval

  34. Monthly frequency Repeat interval Day of month Monthly Schedule

  35. Specificationsfrom the wizard Additionaladvancedspecificationsin the editor Schedule runs at 9 AM, 11 AM, 1 PM, 3 PM, 5 PM Schedulepreview Editing a Schedule

  36. Run One Week into the Next Quarter Runquarterly Run on the8th day aftereach quarter

  37. Associating an Executable Object with a Schedule Associate executable with a schedule. EXECUTABLE OBJECT • Mapping • Transformation • Process flow SCHEDULE • Start date and time • Schedule frequency • Repeat interval • End date and time Databasescheduler Deploy

  38. Assigning a Schedule to an Executable Object Sales_Quarter mapping configuration A scheduleassigned to theLOAD_SALESmapping

  39. Quiz • Which of the following statements are true? • OWB supports one logical model and multiple physical run times. • A single logical design can be deployed to different targets by applying different configurations. • Only one configuration can be active at a time. • The Create Configuration Wizard lets you select a different configuration template if you want to override the system’s default configuration values.

  40. Summary • In this lesson, you should have learned how to: • Set up and manage multiple named configurations • Use configuration templates • Describe OWB configurations in RAC environments • Schedule ETL jobs

  41. Practice 1-3 Overview: Creating a Schedule • This practice covers the following topics: • Using the scheduling wizard a schedule that activates every two hours • Editing the schedule to define more advanced scheduling capabilities • Attaching the schedule to a process flow

More Related