 |
|
| Oracle Tips: |
|
|
|
ORACLE DATA WAREHOUSING
MetaBase scripting for the Oracle data warehousing DBA
Donald K. Burleson
07.06.2005
Rating: -3.38- (out of 5)
|
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:
The Oracle data warehouse professional has two choices for managing the ETL for their data warehouse:
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 progr
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
am. There are two types of OMB commands:
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:
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.
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.
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
