OWB Related Routine Work , Related to Desing and Run time repository

There are various Oracle Warehouse Builder related routine tasks which I came across in my projects. So I feel that a write up on this will help Warehouse Builder Developer and Administrator across the word. Let handle OWB related task or questions one by one.

Automatic Back Up of OWB Project or Module or Mapping

I often come across the question like how can I back my work (mappings or oracle module or a project as a whole) daily? And automate the same. Like daily around 6:00pm i.e. before leaving from the office for the day, I should be having back up of my work.

With knowledge of TCL , OWB Scripting and little bit of batch programming one can easily get this done.
In the listing below , listing 1 go to the directory where OMBPLUS has been installed.
And then call OMBPlus.bat with first argument as TCL file location and second argument as the login credentials required to connect the OWB repository.

OMBCONNECT username/password@hostname:port:service_name

export.bat

c:
rem : go to the path where ombplus has been installed
cd C:\OWB_HOME\owb\bin\win32\
rem : export.tcl contains the logic and commands to take mdl export 
call OMBPlus.bat c:/mdl/export.tcl username/password@hostname:port: service_name

Listing 2 below actually contain the OMB command to take the back up. Here first argument is the OWB login credentials being passed from Listing 1.
OMBEXPORT is the actual command which take the back up of Oracle Module called POS_WHSE to c:\mdl

export.tcl

set connectstring [lindex $argv 0]
OMBCONNECT $connectstring
# CREATES BACK UP BASE ON DATE..clean up older backups manually
# It creates back up base on the date. So at time one can have 31 
# back up max in folder.
set fileName [clock format [clock seconds]  -format "%e"]
set fileName 'c:/mdl/$fileName.mdl'
OMBEXPORT MDL_FILE $fileName PROJECT 'RETAIL_SALES' \
COMPONENTS (ORACLE_MODULE 'POS_WHSE') \
OUTPUT LOG 'c:/mdl/$fileName.log'

Once this is done the only task remains is to schedule the batch file. Once can easily schedule the export.bat file in windows scheduler or any other third party scheduler.
And automatic scheduler is all set.

Finding out the impact of a change AND mass deployment

Often we come across the scenario where business user wants some more data in the existing report and that data can not be derived from the existing data available. In such situation we need to make a change in data model to accommodate change and which impacts a lots mapping across the project and modules.

Let say we have to add 2 columns to the table called PRE_SALES and this table is being used in many mapping and we want to know exact mappings name , oracle module and projects.

Listing 3 below shows how to achieve this using TCL.

proc check_impact {table opType} {
    OMBCC '/'
    set fp [open "c:/$table _ $opType.htm" w]
    
    set projList [OMBLIST PROJECTS]
    
    foreach projName $projList {
    OMBCC '$projName' 
    puts $fp <b>$projName</b><hr><hr>
    set modList [ OMBLIST ORACLE_MODULES ]
    set i 1
   
         
    foreach ModName $modList {

        OMBCC '/'
       OMBCC 'SALES ADHOC PHASE II'
       

        OMBCC '$ModName'
         
        puts $fp $ModName<hr> 
        set mapList [ OMBLIST MAPPINGS   ]
        set J 1
        set seq 1
        foreach mapName $mapList {
             
       puts "Working on mapping  $mapName "    
	 set opList [ OMBRETRIEVE MAPPING '$mapName' GET $opType OPERATORS]
       set o 1
   

    foreach opName $opList {
       

# in 10g R1 GET BOUND_OBJECT is bug for source module
       # set opBoundName [OMBRETRIEVE MAPPING '$mapName'  OPERATOR '$opName' GET BOUND_OBJECT]
       # set tbl [lindex [split [lindex $opBoundName 1] "/"] 3 ]
        
       # set result [string compare $opName $table]
        if {[string compare $opName $table] == 0 }   {
         puts "Found a match...."
         puts $fp $seq...............................$mapName-->$opName<br>  
         incr seq
         continue
        }
       
        incr o
		}
             
            
             
         incr J
        } 

        incr i

    }
} 
close $fp 
}

Let say you want to check the impact using above script.
Then call the script from OMBPlus
e.g check_impact PRE_SALES TABLE
check_impact BUDGET EXTERNAL_TABLE
check_impact PRODUCT DIMENSION

and if this html report looks fine then once write below command to reconcile and deploy.

Make the changes to the script at
if {[string compare $opName $table] == 0 } {
Write reconcile and deployment command in the procedure and call it after if condition…..

OMBRECONCILE TABLE '$table'\
             TO MAPPING '$mapName'\
             OPERATOR  '$opName' \
             USE(RECONCILE_STRATEGY 'REPLACE',MATCHING_STRATEGY 'MATCH_BY_OBJECT_ID')

OMBCOMMIT

OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN' \
            ADD ACTION 'DEPLOY_MAP' SET PROPERTIES (OPERATION) \
            VALUES ('CREATE') SET REFERENCE MAPPING \
            '$mapName'

            OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN'

            # Make sure you drop the Deployment plan. 
            OMBDROP DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN'

            OMBCOMMIT

Check the mapping’s last month’s run average v/s latest run?

This information is required if you feel that OWB process flow which was taking around 20-22 minutes before suddenly takes around 40 minutes or so. And you are wondering which mapping takes this long time then expected. And compare it with its average execution time.

SELECT   ELAPSE_TIME EXECUTION_TIME_SEC, OBJECT_NAME  MAP_NAME, CREATED_ON RUN_ON_DATE ,AVG(ELAPSE_TIME)  
OVER ( PARTITION BY OBJECT_NAME )
FROM ALL_RT_AUDIT_EXECUTIONS
WHERE OBJECT_NAME IN ( 'MAP_FCT_SALES' ,’MAP_DIM_LOCATION’,’MAP_DIM_PRODUCTS’)
AND RETURN_CODE=0 --  SUCCESSFUL EXECUTION ONLY   
AND CREATED_ON > SYSDATE-31—Checking aginst 1 months avg    
GROUP BY ELAPSE_TIME, OBJECT_NAME , CREATED_ON

Listing 4

Above query need to be executed from the RUN time repository.

Some OWB design repository related Questions:

-- Maps in Oracle module ?
select * from ALL_IV_XFORM_MAPS  where INFORMATION_SYSTEM_NAME=’Oracle_Module_Name'

-- Tables in the mapping ?
select DISTINCT MAP_NAME,MAP_COMPONENT_NAME  from ALL_IV_XFORM_MAP_COMPONENTS 
WHERE   OPERATOR_TYPE='Table'
order by MAP_NAME

--Particular table being used either as source or target in which mappings ?
select *  from ALL_IV_XFORM_MAP_COMPONENTS 
WHERE
MAP_COMPONENT_NAME='SHIPMENT'
and OPERATOR_TYPE='Table'

-- To find the mapping and underlying table of oracle module ?
SELECT  DISTINCT A.MAP_NAME , MAP_COMPONENT_NAME TABLE_IN_MAP, B.BUSINESS_NAME TABLE_NAME ,OPERATOR_TYPE 
FROM ALL_IV_XFORM_MAPS A , ALL_IV_XFORM_MAP_COMPONENTS B  
WHERE
A.MAP_NAME=B.MAP_NAME
AND INFORMATION_SYSTEM_NAME=’Oracle_Module_Name'
AND OPERATOR_TYPE='TABLE'