ODI 12c - Pivoting Data and Correlated subqueries
By David Allan on Dec 16, 2013
Here are more data transformation illustrations using the ODI 12c SDK demonstrating pivoting/unpivoting data and correlated subqueries. The SDK examples are on the shared java.net site and are in addition to the existing examples I posted on the blog entry on Mapping Ins and Outs here.
You can download these ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy and the images of the mappings within. There are examples from the SQL documentation on correlated subqueries and examples previously posted for OWB here on pivot/unpivot.
The mappings I have covered include (click on hyperlink for an image of each);
- unpivot data from a table with YEAR,Q1_sales,Q2_sales,Q3_sales and Q4_sales columns to YEAR,QTR,SALES (UNPIVOT component).
- pivot data from a table with YEAR,QTR,SALES columns to YEAR,Q1_sales,Q2_sales,Q3_sales and Q4_sales columns (PIVOT component).
- perform a subquery to get the employees who are in a department that exists in the subquery (SUBQUERY_FILTER component).
- perform a subquery to get the employees who have a salary greater than the average salary for employees in their department (SUBQUERY_FILTER component).
These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. You can see the mapping built from the SDK to perform unpivot of data as an illustration. There are now 14 examples of components in the download illustrating each one, they can operate on arbitrary data and have generic KMs as well as specific Oracle implementations.
Plenty more to show including some of the nitty gritty details on the physical deployment specification side to help illustrate how to build flows using the SDK and backup the javadoc. A lot of great transformation components and plenty more to explore and discover!