The example below is using table functions (can be other transformations such as SQL unpivot) in the Oracle database – table functions are functions that can be queried like a table and support parallelization capabilities along with piping of data for improving performance and leveraging capabilities of the server. The flow can be constructed by piping SQL and transformations (like table functions) in an assembly like manner by constructing interfaces in a modular manner.
Below is an illustration of a number of interfaces assembled in a pipeline which can leverage the code generation capabilities of ODI.
This shows how interfaces can be organized into flows which are resolved into a single SQL with sub-queries. ODI capability to define the SQL subquery generator within a KM opens the door to this flexibility. Table functions themselves are useful for many things i.e.
- Parallelizing arbitrarily complex (PLSQL, SQL PL) code in a SQL manner
- Eliminating intermediate storage
- SQL-izing the stored procedure
In the illustration below we have a pipelined table function FX which is a temporary target data store to process data from datastores T1 and T2. The output of the table function is then joined with T3 and further consumed in target FX_TAB. The temporary datastore FX (representing the table function) has user defined properties set which indicate the inputs and outputs for FX (A and B are inputs and have expressions, X and Y are outputs). The image has the two interfaces side by side, on the left we have a temporary interface for T1 and T2 joined and providing data for table function FX. On the right, the result of the table function is joined with T3 and written to FX_TAB.
The above illustrates how table functions can be used in pipelined manner with query inputs. The meaning of the columns in the FX temporary target datastore is inferred from the user defined property settings – this lets us distinguish the inputs from the outputs. Below in the KM definition (SQL_TableFunction) we can see how the SQL for the transformation type is generated.
The KM supports both cursor and scalar definitions (so a query can be the input to the table function or alternatively you can provide scalar values such as ODI variables, the option CURSOR_INPUT provides the switch between cursor and scalar). So, importantly table functions can be used as a data source, as transformation pipes and and targets. A good use case for a target table function is when the results of the SQL are being written in parallel to file for example. Below we used the cursor approach, so the CURSOR_INPUT option for the SQL_TableFunction KM assignment is set to true (the default);
As you can see we can assemble a flow of ODI interfaces into a series of pipes that interconnect leveraging ODIs declarative description for the capabilities that are supported well out of the box and extend to encompass table functions for incorporating pipelining or other transformation capabilities that you can think of.