CONNECT BY in OWB 10gR2 (Hierarchical queries)
By David Allan on Apr 25, 2007
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:
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.
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:
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:
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.