Author Archive

Using OMBPlus to collectively change schemas for tables in mappings

Ideally all configuration properties for OWB mappings need not be changed half way into a project. With a large number of mappings, this can be a time consuming job. Once again, OMBPlus can be a big help here.

The example below shows how to collectively change the schema name for a set of mappings to a new one. This example assumes that mapping names are named after their target table, and that the mappings are preceded by a 8 letter prefix (e.g. “map_stg_” for a staging mapping).


OMBCONNECT user/password@fully-qualified-connect-string USE REPOS 'repository'
OMBCC 'your_project'
OMBCC 'your_module'
set mapList [OMBLIST MAPPINGS 'your_mappings']
foreach mapName $mapList {
set operName [string range '$mapName' 9 end]

if {$Val == "old_schema"} {
OMBALTER MAPPING '$mapName' \
MODIFY OPERATOR '$operName' SET PROPERTIES (SCHEMA) VALUES ('new_schema')
puts [concat $mapName ": changed SCHEMA from [old_schema] to [new_schema]" ]
}
}
OMBCOMMIT
OMBDISCONNECT


Warehouse Builder and the use of PL/SQL

When I first began developing data warehouses, tools such as Oracle Warehouse Builder or Power Center where not (widely) used. Development took place in Oracle PL/SQL at the project I worked at.

Later on, projects I was part of used Oracle Warehouse Builder to build the data warehouses. Quite a progress this was: implementation went faster, mappings were more self explanatory then PL/SQL to IT and non-IT personnel alike and maintenance is easier, especially when transformations are either large or complex.

I’ve noticed lately that a percentage of OWB developers lack intimate knowledge of and experience with PL/SQL. In my opinion that’s a shame, as I’ve seen good use for it on each and every project I’ve worked on. The use of triggers and generic functions that can be used on multiple mappings (to provide a less error prone solution) are two examples that come to mind.

So for those who don’t have the experience with PL/SQL: why not give it a try? It broadens your horizon and gives you more options when developing.

Using OMBPlus to deploy a series of mappings

After working with Oracle Warehouse Builder 10g R2 for several months, I’m now back to working with OWB 9.2. One sometimes frustrating feature of version 9.2 is the inability to deploy objects and edit objects at the same time (in one instance of OWB). As deploying mappings takes too long due to temporary performance issues, I looked into OMBPlus scripts to improve my efficiency, as OMBPlus scripts run independent of OWB.

Below you see a OMBPlus script that will deploy mappings in a given project and module. It is assumed that all mappings are in one and the same module of one and the same project. A parameter needs to be entered to select which mappings will be deployed. Either sum up the mappings using a space as a separator (eg. MAP1 MAP2) or state one name that includes a wildcard (e.g. MAP&). Of course, the script can be altered to deploy any type of objects.

proc deploy_mappings {} {
# Connect to the repository
OMBCONNECT user/password@DB USE REPOS 'repos'

puts " "
puts [concat "Deployment finished succesfully at" [clock format [clock seconds] ] "." ]
puts [concat [expr $J - 1] "mapping(s) have been deployed." ]
OMBDISCONNECT
}

I hope this example shows it’s relatively easy to use the OMBPLus scripting language. For more information, see Oracle Warehouse Builder 10g Scripting Language – Home Page for more information. Please note that the information is also valid for OWB 9.2.