Oracle Warehouse Builder (OWB) Weblog

For those folk who know SQL even a little SQL and then come to OWB, this would be a great helper; build your map from SQL. How do I build a map that looks like this, how do you support this etc. The reality is that although there are a percentage of SQL statements that fit nicely into the OWB mapping paradigm, there are times when you must rethink the SQL to fit into the capabilities of OWB.

Imagine the possibilities, you could use it to learn how to build maps, to create maps quickly, to construct maps from SQL generators such as summary advisors etc. Before getting carried away though... this is a demo, a demo built using OWB experts and an ANSI SQL parser, like most generators there are various caveats, with this demo there are even more, but I'll do my best to maximize the demo appeal;)

A Simple Example
Let's take a simple example from the SQL reference manual, this is an interesting and small example:

With OWB this would be represented by a number of operators with various properties set. How long would it take to build this by hand? How many mistakes would you make? Would you forget the order in which the operators should be created?  Let's look at this in an accelerated way using copy-paste!

On the 'Mappings' node in the tree, select 'Build from SQL...'. With this we can copy-paste some SQL (ANSI SQL) and get the map automatically built. I did this as a demo there are a few caveats but with what is here you will see the great potential time saver plus resource of information that this can be.

Launch the dialog to create via SQL:


Below we copy-paste the SQL we are interested in



The dialog as well as prompting for the map name and SQL also included an option for the code generation. Since I generally build SQL maps, I defaulted the expert to use set based mode as the default (this set both the generation mode and default operating mode to set based).  After completing this dialog, the generator is off and running - it has conveniently generated the map including all of the operators with respective properties to represent the SQL entered:



So what did the generator do?

1. Added tables for all of the FROM tables (schema name is mapped to module, alias is operator name)
2. Added filter operator for WHERE clause if a single table
3. Added joiner operator for WHERE clause if multiple tables
4. Added expression operator for any expressions in select list or
5. Added aggregation operator if using an aggregated function (including group by clause, having clause)
6. Added constant operator for literals in select list
7. Added set operator for any set operation such as UNION, MINUS etc
8. Added sorter for any ordering defined
9. Inserted a target table if an INSERT clause is used

This demo works nicely for INSERTs and SELECT statements, it breaks down on the richness of the SQL parsing support (I used one that I googled that had a nice API and I could quickly build a demonstration from). It would be better to have rich support for all the Oracle specifics including specific grammar, hints etc. Later I'll cover DELETEs and UPDATEs.

Taking this Further
OK, taking this further it would be great to then click on a view or materialized view and construct the map from the view/materialized view query - then you can get really rich lineage/impact analysis information.


This example gets fairly complex data warehouse SQL reverse engineered into an OWB mapping all with an effortless copy/paste;


The SQL happens to be straight from the Oracle Data Warehousing guide (Example 20-8 GROUPING combined with HAVING), I made it slightly more complex by union the query with itself to illustrate the union operator in the map (just for fun);

Constructing this would have involved quite a number of mouse clicks I bet. As you see this demos really nicely for a few examples, even as an accelerator for building a percentage of the map it is quite nice ie. quickly get a bunch of operators on the mapping canvas with basic joiners, sorters etc..

Deletes and Updates
I guess this is where it gets tough. How can something so simple in SQL be tricky for me to understand in OWB? Well with any tool it will have a few nuances, a few rough edges that you can forgive for a while. How can the following queries be so tricky to model;

This partly falls down to modeling for the complex scenario and not making some simple ones simple. Back to generating the maps, you can  imagine parsing something like this and generating a map becomes a little trickier task.

To build the OWB mapping for the above involves a little more work than you might think, and there are a few ways to do it, here is the SQL for (3) implemented in 2 ways with OWB;



As well as the table operator which has the DELETE loading type, you will need the same table as a source. There are 2 options to the SQL where clause for the date, you can either include a FILTER operator between the tables or define the filter (option 2 above) on the target table using the 'Target Filter for Delete' property (option 1 above). You must also either select match by constraints or set the match column when delete property for each column to be matched. The other SQL statements above are variations on the above either with different filters or no filter at all (1).

The UPDATE SQL statement is another one similar in style to DELETE because of the matching columns.


Add the table also as a source, and ensure that the match column when updating and load column when updating properties are set appropriately.  In this example I made sure SALARY was match when updating was false and load when updating was true. For EMPLOYEE_ID I set as match when updating to true and load when updating to false.



Even with some of these simple DELETE/UPDATE statements there are some nuances, so you can imagine the complexity when you get some complex scenarios, generation of the map from SQL needs some more thought. The earlier map generations were a good fit for the OWB mapping paradigm, and were easy to build in a generator, the UPDATE/DELETE statements need some more thought, but there is hope. The table operator when used for update/delete has a lot of properties from the table operator down to each attribute - all of which must be tweaked to ensure you get what you want. These kind of issues will be resolved in OWB to make the
SQL to OWB transition much simpler. When this happens it will not only be simpler to construct such maps but there will be a much simpler model for generating the maps.

In Summary
For some statements at times manual construction of the map is a fine art, so automation via a generator is a little tricky, tricky that is until we resolve some of the ease of use issues, which will make the generator a reality for more than INSERT and SELECT statements.. I thought I'd share some of these ideas anyway and see what people thought.