CeDeT: [Teknik: Oracle: Enterprise Data Warehousing] Oracle Warehouse Builder

@{ Home >> Teknik >> Oracle >> Enterprise Data Warehousing >> Oracle Warehouse Builder << }
→[ Indhold \| Links \| Nyheder \| Oracle \| Programmering \| Referencer \| Teknik ]

Oracle Warehouse Builder (OWB)
with Oracle Workflow (OWF) Standalone

Oracle Data Integrator (ODI)
another ETL (or is that E-LT) offer from Oracle!

OMB+ example

# CONNECT to REPOSITORY
OMBCONNECT <username>/<password>@<host>:<port>:<service_name> \
   USE REPOSITORY '<repository>'

# LIST Projects
OMBLIST PROJECTS

# Change Context (to Project)
OMBCC '<project>'

# LIST Oracle Modules
OMBLIST ORACLE_MODULES

# Change Context (to Oracle Module)
OMBCC '<module>'

# Display Current Context
OMBDCC

# LIST Mappings
OMBLIST MAPPINGS

# VALIDATE MAPPING
OMBVALIDATE MAPPING '<mapping>'

# LIST Tables
OMBLIST TABLES

# puts TABLES
foreach tN [OMBLIST TABLES] { \
   puts "$tN" \
}

# puts TABLES starting with DW
foreach tN [OMBLIST TABLES 'DW.*'] { \
   puts "$tN" \
}

# DISCONNECT
OMBDISCONNECT

Oracle Data Integrator links

Oracle Data Integrator (ODI) (Previously known as Sunopsis Data Conductor)
- Product Tour: 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8
- Oracle Data Integrator 10.1.3.2.0 Information
Mark Rittman » Oracle Data Integrator
The Jython Project (JPython = Python and Java: The Best of Both Worlds)

Oracle Workflow links

Oracle Workflow (OWF)
-- Discussion Forums: Oracle Workflow
TS Fifteen - WorkflowFAQ.com *** NEW ***

OWB10gR2 options

Enterprise ETL Option	Data Quality Option	ERP/CRM Connectors
ETL Core Features

FAQ: Four Key Features
OWB 10.2 Production deployment article and Licensing caveats!
Paying is (not) an option!

... and some of the stuff doesn't even work properly:
Implementing SCD type-2 with dimension object?
SCD type 2 in OWB 10g R2
IT-eye » OWB SCD Type 2 considerations, continued
Rittman Mead Consulting » Working Through Some SCD 2 and 3 Examples Using OWB10gR2
...and see More OWB links below!

Enable Constraints false!

In OWB version 9.x you can set Enable Constraints to false for a Targets in the Configuration Properties of a mapping:

Be warned though that this does NOT mean that constraints will not be enabled (in fact the opposite is true) - quoting from Help: "Enable: setting this parameter to false disables all referential constraints to and from the target table. This speeds loading by omitting constraint checking by the database. After all the data is loaded, the constraints are automatically enabled by Warehouse Builder. This ..." (my emphasis, view image of text from Help)

A classic case that calls for DIY?!

= null in WB_TRUNCATE_TABLE

The procedure WB_TRUNCATE_TABLE contains a "= null" comparison which may cause it to behave differently from what was intended - more info here (under Sightings in Three-valued logic comes from Nothing (and gets you nowhere)).

More OWB links

Usernames and passwords are "out on the street"

The issue of usernames and passwords being easily obtained using OWB supplied functionality was raised in the Warehouse Builder discussion forum at OTN (see These are all happening in OWB...) - but seemingly without any response. A query to do the unveilding of usernames and passwords (can someone out there explain to me the deeper meaning of 'kp;3jd2o'?):

SELECT sp.parameter_name,
       DECODE(stp.access_restricted,
              0,sp.parameter_value,
              wb_rt_service_control.decrypt(
                sp.parameter_value,
                'kp;3jd2o')) AS parameter_value
 FROM  wb_rt_stores s,
       wb_rt_store_parameters sp,
       wb_rt_def_store_types st,
       wb_rt_def_store_type_params stp
 WHERE sp.store_id        = s.store_id
 AND   s.store_type_id    = st.store_type_id
 AND   st.store_type_id   = stp.store_type_id
 AND   stp.parameter_name = sp.parameter_name;

OWB Links

Oracle Warehouse Builder (OWB)
- OWB Case Studies
  -- Complete Set of Case Studies [PDF]
- OWB Collateral
- OWB Sample Code
  -- Slowly Changing Dimensions [zip]
  -- Analytic Functions [PDF]
OWB Training
- Oracle Warehouse Builder Self-service Education
  -- Process Flow Basics
- Oracle by Example (OBE) - Oracle Warehouse Builder
  -- Designing Process Flows
Discussion Forums: Warehouse Builder
- Discussion Forums: Oracle Workflow

ALL_RT_AUDIT_EXECUTIONS gives "invalid number"

If selecting from the OWB Public View ALL_RT_AUDIT_EXECUTIONS results in an "invalid number" error, then you can extract the view definition from the database (the view is owned by the Runtime Repository owner) and add single quotes ('') around the zeros (0s) in the DECODE-statements of store_type_version thus preventing it from doing an implicit varchar2 to number conversion - here's an example (OWB Design Repository 9.2.0.2.0):

CREATE OR REPLACE VIEW all_rt_audit_executions(
  execution_audit_id, 
  parent_execution_audit_id, 
  top_level_execution_audit_id, 
  execution_name, 
  task_name, 
  task_type, 
  task_input, 
  exec_location_uoid, 
  exec_location_name, 
  exec_location_type, 
  exec_location_type_version, 
  object_uoid, 
  object_name, 
  object_type, 
  object_location_uoid, 
  object_location_name, 
  object_location_type, 
  object_location_type_version, 
  return_result, 
  return_code, 
  execution_audit_status, 
  elapse_time, 
  number_task_errors, 
  number_task_warnings, 
  created_on, 
  created_by, 
  updated_on, 
  updated_by)
AS 
SELECT
  e.audit_execution_id           AS execution_audit_id,
  e.parent_audit_execution_id    AS parent_execution_audit_id,
  e.top_level_audit_execution_id AS top_level_execution_audit_id,
  e.execution_name               AS execution_name,
  e.task_name                    AS task_name,
  eo.operator_name               AS task_type,
  e.task_input                   AS task_input,
  e.execution_store_uoid         AS exec_location_uoid,
  e.execution_store_name         AS exec_location_name,
  eost.store_type_name           AS exec_location_type,
  DECODE(
    eost.store_type_version,
      '0', NULL, -- single quotes ('') added around 0
      eost.store_type_version)   AS exec_location_type_version, 
  e.execution_object_uoid        AS object_uoid,
  e.execution_object_name        AS object_name,
  eot.object_type_name           AS object_type,
  e.execution_object_store_uoid  AS object_location_uoid,
  e.execution_object_store_name  AS object_location_name,
  est.store_type_name            AS object_location_type,
  DECODE(
    est.store_type_version, 
      '0', NULL, -- single quotes ('') added around 0 
      est.store_type_version)    AS object_location_type_version,
  e.return_result                AS return_result,
  e.return_code                  AS return_code,
  wb_rt_constants.to_string(
    e.audit_status)              AS execution_audit_status,
  e.elapse                       AS elapse_time,
  e.number_of_task_errors        AS number_task_errors,
  e.number_of_task_warnings      AS number_task_warnings,
  e.creation_date                AS created_on,
  e.created_by                   AS created_by,
  e.last_update_date             AS updated_on,
  e.last_updated_by              AS updated_by
 FROM
  wb_rt_audit_executions e,
  wb_rt_def_object_types tot,
  wb_rt_def_store_types tst,
  wb_rt_def_execution_operators eo,
  wb_rt_def_execution_adapters ea,
  wb_rt_def_store_types eost,
  wb_rt_def_object_types eot,
  wb_rt_def_store_types est
 WHERE
  e.task_object_type_id            = tot.object_type_id(+)       AND
  e.task_object_store_type_id      = tst.store_type_id(+)        AND
  e.execution_operator_id          = eo.execution_operator_id(+) AND
  eo.execution_adapter_id          = ea.execution_adapter_id(+)  AND
  ea.store_type_id                 = eost.store_type_id(+)       AND
  e.execution_object_type_id       = eot.object_type_id(+)       AND
  e.execution_object_store_type_id = est.store_type_id(+)
WITH READ ONLY;

GRANT SELECT ON all_rt_audit_executions TO PUBLIC;

Unable to start OWB Client

If after the 27th of July 2005 the OWB Client hangs when you try to start it - then check MetaLink Note 329607.1 (Warehouse builder 9.0.4 and 9.2 client freeze/hang on startup from 28 July 2005, works fine before that date).

The note explains that this is due to the fact that the Java Cryptography Extension (JCE) 1.2.1 Package (Signing Certificate) Expires on July 27, 2005 (as alerted by Sun).

Java Security

The solution is to:

1. Download the JCE 1.2.2 package from
   http://java.sun.com/products/jce/index-122.html

2. Stop all Oracle software

3. Rename the following files:

   $OWB_CLIENT_HOME/jdk/jre/lib/ext/US_export_policy.jar
   $OWB_CLIENT_HOME/jdk/jre/lib/ext/jce1_2_1.jar
   $OWB_CLIENT_HOME/jdk/jre/lib/ext/local_policy.jar
   $OWB_CLIENT_HOME/jdk/jre/lib/ext/sunjce_provider.jar

   as

   $OWB_CLIENT_HOME/jdk/jre/lib/ext/US_export_policy.jar.org
   $OWB_CLIENT_HOME/jdk/jre/lib/ext/jce1_2_1.jar.org
   $OWB_CLIENT_HOME/jdk/jre/lib/ext/local_policy.jar.org
   $OWB_CLIENT_HOME/jdk/jre/lib/ext/sunjce_provider.jar.org

4. Copy the following files from the downloaded JCE 1.2.2 package 
   to the directory $OWB_CLIENT_HOME/jdk/jre/lib/ext/:

   jce1.2.2/lib/US_export_policy.jar
   jce1.2.2/lib/jce1_2_2.jar
   jce1.2.2/lib/local_policy.jar
   jce1.2.2/lib/sunjce_provider.jar

5. Rename jce1_2_2.jar as jce1_2_1.jar

Thanks to Henrik Verup from Sigma Danmark (tidligere RKS) for alerting me regarding this issue!

Runtime Service unavailable

From time to time when trying to invoke the OWB Deployment Manager from within the OWB Client an alert box appears saying:

OWB Error
RTC-5260: Failed to connect to runtime
platform, Please check you have provided the
correct Host, user, password and Service
Name.

RTC-5301: The Runtime Service is not
currently available

Often this is due to the fact that the Runtime Platform Service simply is not running (it happens). Appendix D of the Oracle9i Warehouse Builder Installation and Configuration Guide offers the following advice:

To manually start, stop, and check the Runtime Platform Service:
1. Log on to the Runtime Platform as the Runtime Repository owner.
2. Run the required script:
   
   To start the Runtime Platform Service: Run the
   ORACLE_HOME\owb\rtp\sql\start_service.sql script.
   
   To stop the Runtime Platform Service: Run the
   ORACLE_HOME\owb\rtp\sql\stop_service.sql script.
   
   To check whether the Runtime Platform Service is available:
   Run the ORACLE_HOME\owb\rtp\sql\show_service.sql script.
   
   To check the install status of the Runtime Platform Service
   components: Run the ORACLE_HOME\owb\rtp\sql\service_doctor.sql
   script.

If the OWB Runtime Service is installed on the same UNIX server (host) as the database this becomes (for a check):

[telnet session:

   login as: oracle
   password: *******
   
   $ cd $OWB_HOME/owb/rtp/sql
   $ sqlplus owbrt_sys/*********          
   SQL> @show_service.sql
   Available
   
   PL/SQL procedure successfully completed.

]

SQL*Plus scripts

OWB comes with number of scripts located in $OWB_HOME/owb/rtp/sql (for OWB 9.2.0.2.8):

$ ls $OWB_HOME/owb/rtp/sql
abort_exec_request.sql
abort_unit_request.sql
deactivate_deployment.sql
deactivate_execution.sql
display_platform_property.sql
list_requests.sql
oem_exec_template.sql
service_doctor.sql
set_oem_home.sql
set_platform_property.sql
set_repository_password.sql
show_service.sql
sqlplus_exec_template.sql
start_service.sql
stop_service.sql
upgrade_9_0_4_runtime_repos_to_9_0_5.sql
upgrade_calais_to_from_ids.sql

As an alternative to sqlplus_exec_template.sql or oem_exec_template.sql Oracle has provided a run_my_owb_stuff.sql file on OTN.

ALL_IV_INDEXES

OWB comes with numerous Public Views but I have personally missed an ALL_IV_INDEXES public view. So here is an attempt at providing such a view (for OWB 9.2.0.2.8):

CREATE VIEW all_iv_def_ind_columns AS
SELECT m.project_name project_name,
       m.schema_name  module_name,
       t.table_name   table_name,
       i.object_name  index_name,
       it.value       index_type,
       ir.position    index_position,
       c.object_name  column_name
 FROM  all_iv_modules              m,  -- modules
       all_iv_tables               t,  -- tables
       cmpreferencepropertyvalue_v ir, -- index (column) references
       all_iv_objects              i,  -- index objects
       cmpstringpropertyvalue_v    it, -- index types
       all_iv_objects              c   -- column objects
 WHERE t.schema_id         = m.schema_id
 AND   ir.firstclassobject = t.table_id
 AND   ir.logicalname      = '8i.INDEXCOLUMNS'
 AND   i.object_id         = ir.propertyowner
 AND   i.business_name     = '8i.INDEXES'
 AND   it.propertyowner    = i.object_id
 AND   it.logicalname      = '8i.INDEXES.INDEXTYPE'
 AND   c.object_id         = ir.referencedelement
 AND   c.object_type       = 'COLUMN'
WITH READ ONLY;

CREATE VIEW all_iv_def_key_columns AS
SELECT m.project_name                   project_name,
       m.schema_name                    module_name,
       k.entity_name                    table_name,
       k.key_name                       key_name,
       DECODE(u.primarykey,1,'PK','UK') key_type,
       c.position                       key_position,
       c.column_name                    column_name
 FROM  all_iv_keys            k,
       all_iv_key_column_uses c,
       all_iv_modules         m,
       cmpuniquekey_v         u
 WHERE k.key_id           = c.key_id
 AND   u.elementid        = k.key_id
 AND   u.firstclassobject = k.entity_id
 AND   c.key_type         = 'KEY'
 AND   m.schema_id        = k.schema_id
WITH READ ONLY;

CREATE VIEW all_iv_ind_columns AS
SELECT m.project_name project_name,
       m.schema_name  module_name,
       t.table_name   table_name,
       i.object_name  index_name,
       it.value       index_type,
       '--'           key_type,
       ir.position+1  column_position, -- index_position+1
       c.object_name  column_name
 FROM  all_iv_modules              m,  -- modules
       all_iv_tables               t,  -- tables
       cmpreferencepropertyvalue_v ir, -- index (column) references
       all_iv_objects              i,  -- index objects
       cmpstringpropertyvalue_v    it, -- index types
       all_iv_objects              c   -- column objects
 WHERE t.schema_id         = m.schema_id
 AND   ir.firstclassobject = t.table_id
 AND   ir.logicalname      = '8i.INDEXCOLUMNS'
 AND   i.object_id         = ir.propertyowner
 AND   i.business_name     = '8i.INDEXES'
 AND   it.propertyowner    = i.object_id
 AND   it.logicalname      = '8i.INDEXES.INDEXTYPE'
 AND   c.object_id         = ir.referencedelement
 AND   c.object_type       = 'COLUMN'
UNION ALL
SELECT m.project_name                   project_name,
       m.schema_name                    module_name,
       k.entity_name                    table_name,
       k.key_name                       index_name, -- key_name
       'UNIQUE'                         index_type,
       DECODE(u.primarykey,1,'PK','UK') key_type,
       c.position                       column_position, -- key_position
       c.column_name                    column_name
 FROM  all_iv_keys            k,
       all_iv_key_column_uses c,
       all_iv_modules         m,
       cmpuniquekey_v         u
 WHERE k.key_id           = c.key_id
 AND   u.elementid        = k.key_id
 AND   u.firstclassobject = k.entity_id
 AND   c.key_type         = 'KEY'
 AND   m.schema_id        = k.schema_id
WITH READ ONLY;
-- how about unique indexes that are also primary or unique keys?

CREATE VIEW all_iv_indexes AS
SELECT project_name,
       module_name,
       table_name,
       index_name,
       index_type,
       key_type,
       MAX(column_position) last_column_position
 FROM  all_iv_ind_columns
 GROUP BY project_name,
          module_name,
          table_name,
          index_name,
          index_type,
          key_type
WITH READ ONLY;
-- how about (unique) indexes that are also primary or unique keys?

GRANT SELECT ON all_iv_indexes TO PUBLIC;
GRANT SELECT ON all_iv_ind_columns TO PUBLIC;