MetaBase scripting for the Oracle data warehousing DBA

The OMB scripting language is a extension of TCL and is an attractive alternative to the do-it-yourself approach to ETL management for the Oracle data warehousing 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.
According to the ODTUG paper "Using Oracle Metabase Plus Language To Build And Deploy Mappings And Workflows" using Oracle's MetaBase is an attractive alternative to traditional job stream applications and it shows working examples of Tcl with the MetaBase extensions: Similarly, the example below shows the deployment of OWB process flows to the Oracle Workflow engine. First, it connects to the repository and it changes the context to STUDENT_YEAR_4 module. It then retrieves all the workflow packages starting with a particular naming pattern. The inner loop also controls as to what group of workflow should be deployed.
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

About the Author

Donald K. Burleson has been a DBA for more than 20 years and specialized in Oracle performance tuning. The author of more than 30 Oracle books, Burleson provides Oracle consulting at www.dba-oracle.com and remote Oracle support at www.remote-dba.net.
This was first published in July 2005

Dig deeper on Oracle business intelligence and analytics

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close