What tables are used in my view?

Metadata is an interesting thing. Views are also interesting, however they typically break the metadata story as they are code. No one typically knows what exactly is in the view and you struggle therefore understanding the impact of a change in tables to all the views you have. This post is the first as to how to resolve this using Warehouse Builder.

I have a simple example, a view called REFERENCE_EXAMPLE and it (at least in theory) contains a query that has 2 tables used in it. They are EMPLOYEES and CITIES. What I will do is to link them together using the OWB repository and then look at the Lineage and Impact assessments.

In OMB Plus go to the module that has the objects (OMBCC) then type the following:

OMB+> OMBALTER VIEW 'REFERENCE_EXAMPLE' ADD REF TABLE 'EMPLOYEES'
View REFERENCE_EXAMPLE altered.
OMB+> OMBSAVE
Save complete.

OMB+> OMBALTER VIEW 'REFERENCE_EXAMPLE' ADD REF TABLE 'CITIES'
View REFERENCE_EXAMPLE altered.
OMB+> OMBSAVE
Save complete.

Now launch Lineage from the view and you get:

ViewLineageToTables:

All of a sudden you can show graphically what objects are related to views. Now if you incorporate the view into a mapping, you can drill either way and the view is no longer that dreaded black box object. This is a very small piece of code, but it can be very usefull especially when the views are already created and are just imported into OWB. Next post I'll show a nice way of creating views in OWB and getting complete metadata...

Oh, and should you want to go cross module, make sure you either do a full path to the object to be referenced or a relative path, for example:

OMB+> OMBALTER VIEW 'REFERENCE_EXAMPLE' ADD REF TABLE '../DQ_SRC/JOBS'
View REFERENCE_EXAMPLE altered.
OMB+>

Comments:

Can you also do this from the UI? If so, could you point me in the direction where that can be found? Thanks in advance, Eric.

Posted by Eric on June 11, 2007 at 06:31 AM PDT #

Hi Eric, Sorry, this only works from scripting... JP

Posted by jean-pierre dijcks on July 20, 2007 at 06:41 AM PDT #

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