MetaBase scripting for the Oracle data warehouse DBA

One of the most challenging areas for any Oracle DBA is the management of complex job streams. Fortunately, Oracle has come to the rescue with the dbms_scheduler package and the Oracle Warehouse Builder MetaBase Plus (OMB) scripting language.

For Oracle data warehouse administration, the Oracle Warehouse Builder product offers OMB as a way to integrate all Extract, Transform and Load (ETL) activities by providing built-in tools that provide error-checking, validation and data mappings. It's sort of like a TP monitor (CICS) for the OWB environment.

While Oracle has made great headway with the GUI interfaces in Oracle 10g Enterprise Manager, senior Oracle professionals still feel the need to use more-robust scripting environments. A GUI can't do it all, and advanced operations require more flexibility and sophisticated job control logic:

• Scheduled execution -- Fire-off job streams at pre-determined times, with pre-defined prerequisite conditions. Ensure that a "missed" job is re-scheduled.
• Conditional execution -- execute this task based on the status of completed tasks.
• Error alerts and job stream validation -- The Oracle DBA needs to define the scope of everything that might go-wrong and create user-exits to pause execution until important issues are resolved.

The Oracle data warehouse professional has two choices for managing the ETL for their data warehouse:

• Do It Yourself (DIY) -- If the Oracle DBA already understands how to create batch streams, do error-checking and enforce conditional execution, writing your own ETL is an option. The Oracle10g dbms_scheduler package can be used to schedule job-streams, or the traditional method of OS shell scripts is sometimes used. The DIY approach has more flexibility and you can choose best-of-breed approaches and techniques, rather than relying on the OWB offerings. Dr. Tim Hall has an excellent book on the jobstream subject titled "Oracle Job Scheduling", a comprehensive reference for performing complex job executions in an Oracle environment.

• Use Oracle MetaBase -- OMB is a extension of the Tcl language (pronounced "tickle") and it has all of the robust features of any programming language, including complex Boolean operators, variable support, and loops (FOR, WHILE, REPEAT-UNTIL). The Oracle warehouse Tcl extensions are customized into OMB, so you can perform complex data mappings and program job streams. Like Java, OMB is platform independent and it requires no changes to run MetaBase on a Mainframe or a Macintosh.

Of course, MetaBase requires you to use the extra-cost Oracle Warehouse Builder to manage your ETL, but the OWB OMB scripting language is an attractive alternative to the do-it-yourself approach, depending upon your level of ETL experience.

Inside OMB syntax

Let's take a closer look at the OMB environment. OMB is very much like SQL*Plus and it has a list of built-in commands that can be invoked to manage OWB data mappings. These data mappings can be nested (a hierarchical structure) and OMB offers commands to allow you to invoke these mappings from a Tcl program. There are two types of OMB commands:

• Metadata Definition Language (MDL) -- Like DDL, it allows you to create and drop OMB objects. MDL object types include projects, modules, tables, mappings, and workflow processes.
  • OMBALTER -- This is used to alter the metadata for a Warehouse Builder component. In the example below, note the UNIX-like backslash commend continuation characters:

    ombalter mapping 'MAP_NAME' modify operator 'JOIN' 
    set properties join_condition) 
    values ('fact.empno = emp.empno) 
    

  • OMBCREATE -- use this command to create a component in OWB, which might be a project, module, table, mapping, or workflow processes.
  • OMBDROP -- Used to drop named objects from the repository.

• Metadata Manipulation Language (MML) -- Analogous to Oracle DML, the MML commands allow us to alter (add, update, delete) named objects. Some of the MML commands include:
  • OMBCC -- This is the "change context" command and it is used like the UNIX and DOS "cd" command to change OWB directories.
  • OMBCOMMIT -- Commits a transaction
  • OMBCOPY -- This is a handy command for cloning a section of use this command to copy one or more objects of the same type.

    ombcopy 'AN_EXTSTING_MAP' to 'A_NW_MAP' replacement
    

  • OMBDEPLOY -- This deploys an object to a specified database via the OWB runtime tables.
  • OMBLIST -- the OMBLIST command is like a directory listing command (ls or dir) and it lists all OWB objects under a specific hierarchical tree.
  • OMBRECONCILE -- This is used to reconcile the target OWB metadata definition with the target database metadata definition.

proc owb_deploy_workflows_all {} {
 OMBCONNECT OWB_DEV/XXX@SERVER:1521:DBPROD
 OMBCC 'STUDENT_YEAR_4'
 OMBCONNECT RUNTIME 'SASI_CURR_RT' USE PASSWORD 'XXX'
 set ModList [ OMBLIST PROCESS_FLOW_MODULES 'CURR_W.*.']
 set i 1
 foreach ModName $ModList {
    puts "Accessing WF package: $ModName"
    OMBCC '$ModName'
    set mapList [ OMBLIST PROCESS_FLOW_PACKAGES 'LD_ACNR.*.' ]
    set J 1
    foreach mapName $mapList {
       puts " deploying: $mapName"
       OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN
       'DEPLOY_PLAN' ADD ACTION 'WORK_FLOW_DEPLOY'
       SET PROPERTIES (OPERATION)
       VALUES ('CREATE') SET REFERENCE PROCESS_FLOW_PACKAGE
       '$mapName'
       OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN'
       OMBDROP DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN'
       OMBCOMMIT
       incr J
       }
    OMBCC '..'
    incr i
    }
 OMBCC '..'
 OMBDISCONNECT
 }

Conclusion

Will OMB take-off as the ETL scripting language of choice for the Oracle warehouse DBA? Since OWB usually comes with a data warehouse license, it's essentially a free tool, and any data warehouse DBA will want the robust features of being able to manage OWB from a non-GUI environment. The only downside, of course, is the requirement to learn the Tcl language.

References

• "Using Oracle Metabase Plus Language To Build And Deploy Mappings And Workflows"
  Presented at ODTUG 2005 by Allison Lobato and Nghia Phu.
• Oracle Warehouse Builder 10g Scripting Language
• OWB Scripting White Paper
• Warehouse Builder Scripting Reference Guide

About the Author