OWB – Reuse Mapping for Different Data Sources

A common query over the years is how to do more dynamic changes at runtime, with the core OWB mappings, a common technique which has been used through the years is to use Oracle database synonyms (see forum entries such as this). This is useful for when you are doing cookie cutter style data movement from many replicated systems. The synonym always points to a common relational shape that is processed by a mapping, its just that in one execution it may point to schema X table T, and in another point to schema Y table T or even schema Z table T2 over a database link.

For example we may have an ORDERS table in schemas XWEEK and XWEEK_ANO, if we define a synonym ORDERS which points to the XWEEK.ORDERS table we will see how it is used in OWB.

image

In the database metadata import wizard you should check the ‘Use a synonym to look up objects’ option, this will show private synonyms from USER_SYNONYMS;

image

The synonyms are differentiated by icon in the tree, synonyms are shown with the image image and  regular tables with the image  image .

image

When a synonym is imported we get the table metadata imported for the object the synonym points to.

image

So we get all the columns and keys etc.

image

When the object is used in a mapping then the synonym name is used in the local schema, so for example we get SELECT … FROM ORDERS which is really using whatever the synonyms is pointing to.

image

At runtime we can change the synonym definition and point to any schema so long as the shape is the same and the mapping will execute and process the data. Below I have changed the synonym to point to XWEEK_ANO.ORDERS

image

Executing the mapping again will now consume the data from XWEEK_ANO.ORDERS. Check out the forum post for how to incorporate this into a mapping.

Comments:

I have a PERSON table in 5 different schema namely : A, B, C, D and E. It has exact same structure.

In OWB target schema, a mapping PERSON_MAP is defined, which uses A.PERSON table.

I want to reuse this mapping PERSON_MAP, to use with different source schema (B, C, D and E)

How can I do this in OWB UI and with OMB+ commands?

Posted by Meghana Randad on December 12, 2011 at 08:39 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today