Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   

Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines

 


Scripting And Refactoring Using OWB OMB*Plus


July 28, 2004
Mark Rittman

 

I've been doing some research for a paper on OWB10g, and because of this I've had a chance to look at the scripting support that was originally introduced with OWB9.0.4. If, like me, you didn't really pay much attention to this new feature when it first came out, I think you'll be pleasantly surprised about what is actually a very powerful new capability.

One of the criticisms leveled at graphical ETL tools such as OWB is that, whilst they might make a novice Oracle developer more productive, they actually slow down more experienced developers who are more used to working with the command line. Whilst tools such as OWB are great for novice users who need visual prompts when putting together objects and mappings in a warehouse, DBAs and Unix sysadmins are more used to defining objects using scripts and often write programs to generate batches of objects to make the process more efficient. A good example of this is Arup Nanda's "A Multi-Source, Time Variant Data Warehouse Case Study" which describes a data warehouse that had 7,200 tablespaces, 10 tables, 360 partitions and 100 indexes, and loaded data from source to target using a number of very similar CREATE TABLE AS SELECT statements that were programmatically generated using a Unix shell script. Clearly, in this example, generating all these objects and mappings by hand using the OWB GUI would be far less efficient than writing a series of reusable scripts.

Another problem that faces developers when working with a GUI application like OWB is there's no way to record and replay a series of development steps so that you can apply them in one go to your project. For example, you might want to add a number of columns (modified date, modified by etc) to all your tables, and also make these modifications to your test and production environments. Using the OWB GUI tool you'd have to go into each project and make the changes manually, which takes time and opens up the possibility of introducing user error. Also, not having an automation process precludes the ability to carry out agile or test driven development, where changes can be made to requirements at any stage of the project as long as these changes are automatically refactored  into the development model .

To address these issues, starting with OWB 9.0.4 a scripting capability was introduced which allows us to create, alter, generate or drop both warehouse objects and mappings using the command line. OWB scripting consists of two components:

  • OMB Scripting, a programming language based on Tcl that allows us to manipulate objects in the OWB repository, and
  • OMB*Plus, a 'SQL*Plus' lookalike used for interactively running OMB Scripting commands

OMB stands for 'Oracle MetaBase', which is the product name for the repository that stores the business logic and object definitions for our OWB project. OMB Scripting acts as an interface to the OWB Java API, translating scripting commands into API calls, allowing us to work with the OWB repository without using the GUI interface. Like SQL, OMB commands are divided into MDL (metadata definition language) commands which allow us to create metadata objects, and MML (metadata manipulation language) commands which allow us to change the contents of metadata objects.

For the purposes of the rest of the article, I'll be using OWB10g on Windows 2000, which can be downloaded from OTN if you've not got this current version. To start up the OWB*Plus environment, using the Start Menu navigate to your OWB program group, go to the Warehouse Builder subgroup and select 'OWB OMB Plus'. Then, to connect to your repository, use the OMBCONNECT command (changing the connection details as required)

OMBCONNECT username/password@hostname:port:service_name

Then, to change context to a project, use the OMBCC command:

OMBCC 'MY_PROJECT'

Note the use of CAPITALS with the OMB commands - this is mandatory and you'll get errors if you use lower case. Now, you can create a new module using the OMBCREATE MODULE command:

OMBCREATE ORACLE_MODULE 'STAGING_TABLES' \
    SET PROPERTIES (DESCRIPTION) VALUES ('Warehouse staging tables')

Note the '\' which indicates that a command carries on to the next line, and the lack of ';' compared to SQL*Plus. Now, change context to the new module and create a table in the repository using the OMBCREATE TABLE command

OMBCC 'STAGING_TABLES'

OMBCREATE TABLE 'STG_CUSTOMERS' \
  SET PROPERTIES (DESCRIPTION, BUSINESS_NAME) VALUES ('Customer staging table','Staging Customers') \
  ADD COLUMN 'CUST_ID' \
  SET PROPERTIES (DATATYPE, PRECISION, SCALE, NOT_NULL) VALUES ('NUMBER',7,0,1) \
  ADD COLUMN 'CUST_NAME' \

You can then add further columns to the table using the OMBALTER command:

OMBALTER TABLE 'STG_CUSTOMERS' \
  ADD COLUMN 'CUST_FORENAME' \
  SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2',30)

Finally, commit your work with the OMBCOMMIT command. 

If you open up the OWB GUI, your project should look something like:

with the STAGING_TABLES module being the module you've just created using OMB*Plus.

The OMB scripting feature is based on Tcl with extensions for dealing with the OWB repository. Therefore, you can use all of the Tcl programming constructs to programatically create or maintain objects in the OWB repository. For example, you might wish to loop through all of the tables within a module and add a MODIFIED DATE column, which you could do as follows;

foreach tableName [OMBLIST TABLES] {

OMBALTER TABLE '$tableName' \
ADD COLUMN 'MODIFIED DATE' \
SET PROPERTIES (DATATYPE) \
VALUES ('DATE') \

puts "Table $tableName altered."
}

You can also build and maintain transformations using OMB scripting. For example, we we might have COUNTRIES and REGIONS tables which we want to join and load into a GEOGRAPHIES table. 

OMBCREATE MAPPING 'LOAD_COUNTRIES_MAP' \
SET PROPERTIES (BUSINESS_NAME, DESCRIPTION) \
VALUES ('Load Countries from HR Mapping', 'Mapping to load the countries table from HR schema') \
ADD TABLE OPERATOR 'COUNTRIES' BOUND TO TABLE '../HR_SRC/COUNTRIES'\
ADD TABLE OPERATOR 'REGIONS' BOUND TO TABLE '../HR_SRC/REGIONS'\
ADD TABLE OPERATOR 'GEOGRAPHIES' BOUND TO TABLE 'GEOGRAPHIES' \
ADD JOINER OPERATOR 'JOIN' SET PROPERTIES (JOIN_CONDITION) \
VALUES ('INGRP1.REGION_ID = INGRP2.REGION_ID') \
ADD CONNECTION FROM ATTRIBUTE 'COUNTRY_ID' OF GROUP 'INOUTGRP1' OF OPERATOR 'COUNTRIES' TO \
GROUP 'INGRP1' OF OPERATOR 'JOIN' \
ADD CONNECTION FROM ATTRIBUTE 'COUNTRY_NAME' OF GROUP 'INOUTGRP1' OF OPERATOR 'COUNTRIES' TO \
GROUP 'INGRP1' OF OPERATOR 'JOIN' \
ADD CONNECTION FROM ATTRIBUTE 'REGION_ID' OF GROUP 'INOUTGRP1' OF OPERATOR 'COUNTRIES' TO \
GROUP 'INGRP1' OF OPERATOR 'JOIN' \
ADD CONNECTION FROM ATTRIBUTE 'REGION_ID' OF GROUP 'INOUTGRP1' OF OPERATOR 'REGIONS' TO \
GROUP 'INGRP2' OF OPERATOR 'JOIN' \
ADD CONNECTION FROM ATTRIBUTE 'REGION_NAME' OF GROUP 'INOUTGRP1' OF OPERATOR 'REGIONS' TO \
GROUP 'INGRP2' OF OPERATOR 'JOIN' \
ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'JOIN' TO \
GROUP 'INOUTGRP1' OF OPERATOR 'GEOGRAPHIES' BY NAME

Then, we can generate and deploy mappings using OMB:.

OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN \
'DEPLOY_PLAN' ADD ACTION 'TABLE_DEPLOY' \
SET PROPERTIES (OPERATION) \
VALUES ('CREATE') SET REFERENCE TABLE \
/MY_PROJECT/STAGING_TABLES/STG_CUSTOMERS'

OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN'

OMB scripting can carry out maintenance tasks such as installing runtime schemas, creating deployment locations, create runtime repository connections, and so on.

OMBCREATE RUNTIME_REPOSITORY_CONNECTION \ 'NEW_RR_CONNECTION' \
SET PROPERTIES (DESCRIPTION, BUSINESS_NAME, \
HOST, PORT, SERVICE_NAME, \
CONNECT_AS_USER, RUNTIME_REPOSITORY_OWNER) \
VALUES ('Runtime Repository', \
'MY_RUNTIME', 'acgdev03', \
1521, 'plusdev1', 'OWBRU', 'OWBRR')

Finally, OMB scripts can be saved to the filesystem, loaded up whilst in the OMB*Plus environment or executed directly from DOS or Unix by supplying the script name after the OMB*Plus executable.

ombplus.bat c:\owb\refactoring_script.tcl

Scripting within Oracle Warehouse Builder has three main advantages. Firstly, it allows us to bulk create objects and mappings, and make mass changes to them without having to go into the OWB GUI and make each change manually. This addresses the main reason that Arup Nanda used shell scripts in his technical paper, and gives power back to the experienced developer who wants to generate scripts according to a template.

Secondly, by allowing us to script changes to objects, and more importantly, be able to repeat these changes without fear of operator error, it allows us to take a more agile approach to development and we can refactor development changes reliably and repeatedly.

Lastly, and this is more of a theoretical rather than practical benefit at the moment, it allows us the possibility of using the command line to build objects and mappings, but in an abstract form that isn't directly tied to the particular implementation details for our particular Oracle platform. For instance, our developer might put together an OMB script to merge a staging table into a dimension table, which in Oracle 8i would have been implemented by OWB as two separate INSERT and UPDATE commands. Once OWB was pointed at an Oracle 9i database however, it would in this case implement the merge using the MERGE command. The advantage of using OWB is that the logical transformation (merging into a dimension table) is kept constant, whilst the actual implementation varies according to the platform. What OMB brings to this is that you can write scripts that are now at a more abstract level, building objects and mappings in a logical way whilst OWB deals with the implementation. Before OMB was around, the only way you could build things in this abstract way was to use the OWB GUI, but now you can actually write scripts that have a degree of abstraction from the actual implementation details for your particular platform.

More details on OMB scripting can be found at:

  • Oracle Warehouse Builder 10g Scripting Language - Home Page
  • OWB Scripting White Paper
  • Warehouse Builder Scripting Reference Guide

 

 

   
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational