CONNECT BY in OWB 10gR2 (Hierarchical queries)

A common question from OWB users is how to incorporate the CONNECT BY clause and build maps that handle parent child tables. The good news is now you can build such maps...so I have just found out!


To define a hierarchic query in OWB you can define the CONNECT BY clause in a FILTER operator (this is in the OWB 10.2.0.2 patch). For example in the mapping below the filter operator CONNECT_BY has the hierarchic CONNECT BY clause:


Connect By Map: This map uses the filter operator with a filter condition containing a CONNECT_BY clause.


The CONNECT BY clause can be used in a FILTER (the START WITH clause if used must be after the CONNECT BY, even though the Oracle SQL grammar states the other way) when bug 5233636 is fixed, the filter condition will conform to the grammar ie. START WITH INOUTGRP1.ENAME=�KING� CONNECT BY PRIOR�).


In the expression operator pseudo columns can be utilized,  for example an output expression value could be the LEVEL pseudo column. You can also use others such as the SYS_CONNECT_BY_PATH to get a fully qualified path of hierarchic information.


Connect By Pseudo Columns: The pseudo columns for CONNECT BY can be used in a map.


When the code is generated we get the CONNECT BY SQL we want!


Taking this further the 'create dimension' expert has been extended on the Exchange to cover 2 more scenarios; generating normalized level-based dimension and generating a single level dimension with a level indicator attribute. The expert prompts for minimal user input - for example below the parent and member columns are identified:


Connect By Expert: Create dimension expert for handling parent child tables.


After the columns are identified you are prompted for a starting condition, here we select the root of the hierarchy, where the MGR is NULL:


Connect By Expert Levels: Select the start with and max levels


The normalization option will create n levels and populate the dimension table based on the parent child source table. It will also push ragged dimension members from the actual leaf to the deepest level in the hierarchy. The single level option will create a dimension with a single level including a parent reference attribute and a level indicator for depth in the tree, along with the accompanying data loader.


In summary we can see how we can incorporate the CONNECT BY SQL clause into OWB mappings. Experts and scripting provide a way of accelerating the development of objects with common design patterns.


 

Comments:

Hi Great tool. I'm just starting to get into experts myself. Please note that once the CREATE_DIMENSION_FROM expert has been imported from the mdl that you set security permission for all applicable user or else the expert will not run properly. Thanks

Posted by usagi_b on April 15, 2008 at 03:06 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