Oracle Warehouse Builder (OWB) Weblog

Regarding reverse engineering OWB maps from SQL (see earlier post here), I've uploaded the expert as stands, it is a demo remember with no support contract:) It will be useful for people to see a range of OMB related to map construction as well as illustrating some of the SQL to OWB potential. Some of the demo SQL below requires the OWB 10.2.0.3 patch since there were improvements and fixes in the aggregator operator that allow you to use the CUBE/ROLLUP functions and more.

Installation Notes:
To run the expert you'll have to do the following steps;

1. import MDL (build_mapping_from_sql.mdl)
   into your repository. This will create a global expert in Global
   Explorer under Public Experts/SQL_ACCELERATORS/BUILD_MAP_FROM_SQL
2. Download the SQL parser from
   here. There is a little bit of work here...unzip the archive,
   change into the Zql/classes directory. Create a java archive of the Zql
   classes for the expert  by executing the following (on Windows) 'jar cvf
   zql.jar Zql\*.class' or (on UNIX the following) 'jar cvf zql.jar Zql/*.class' You must have a JDK bin  directory on your
   path (OWB software home has one under the oracle home under jdk/bin).
   ZQL claims 'Zql is not a commercial product: feel free to use it, but we
   provide no warranty.'. For this demo it was really useful for illustrating these capabilities, the API is simple to use and provided an easy way to extend the function support.
3. Copy the JAR file (zql.jar)  to your OWB client install under
   owb/lib/ext    You will have to restart your OWB client.
   
4. In the main tree add the expert as a shortcut to a map. Right click
   map, select Add/Remove experts enable check box for build map from SQL,
   click OK. The expert should now appear on the menu.
   

Notes:

You are now ready to go. You can use any tables and SQL you have. Here are some adhoc notes:

• SQL Terminator: SQL must be terminated by a semi-colon (;)
• Name Resolution: Names are resolved either
  from the context in which the map is being created or if the table
  name is prefixed with the schema the table must exist in an OWB module
  with that name. So if I create a map for select ENAME from MYSCOTT.EMP;
  then the table EMP will be bound to the table EMP in OWB Oracle module
  MYSCOTT.
• Function support is restricted to ANSI SQL and Oracle functions, if you have custom functions you will have to add an entry in the expert - see the tcl function addOracleFunctions (in the expert code) for examples, this is in the procedure declaration property of the expert.
  
• Some Limitations:

1. INSERT and SELECT statements only..on tables (for now, it is a
   demo).
2. ANSI SQL so no Oracle specific syntax supported (join syntax etc..)
   
3. Limited subquery support
   
   
4. The Oracle function library has been added so it can be parsed,
   but not all functions tested.
5. Literals must be named (ie select 1 a, 'hello' b from dual; will
   work,
   select 1 from dual will fail)
6. Datatype analysis of literals is limited (NUMBER/VARCHAR2
   supported)
7.  +++ TBD

Demo SQL

Some demonstration SQL that illustrates what has been covered. Import the tables from the sample SH schema into an OWB module named
SH and HR schema into an OWB module named HR.



1. Now run the expert from the mappings node in SH module, enter the
following SQL. If you add a target table and map from EXPR1.OUTGRP1 to
the new tables input group you can generate the OWB code. This example
illustrates the OWB aggregator operator setting up the group by clause
and the output expressions.


SQL:

SELECT
  DECODE(GROUPING(channel_desc), 1, 'Multi-channel sum',
channel_desc) AS Channel,
  DECODE (GROUPING (country_iso_code), 1,
'Multi-country sum', country_iso_code) AS Country,
  TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales,
customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id AND customers.country_id =
countries.country_id AND sales.channel_id= channels.channel_id AND
channels.channel_desc IN ('Direct Sales', 'Internet') AND
times.calendar_month_desc= '2000-09' AND country_iso_code IN ('GB',
'US')
GROUP BY CUBE(channel_desc, country_iso_code);

Generated Map:


2. This example illustrates using a target table TGT (it will be
defined by the expert) and again it uses aggregation. It depends on the
SCOTT EMP table (and must be run from SCOTT module);


SQL:

INSERT INTO TGT (sal,deptno)
 SELECT sum(sal) salary, deptno deptno
 from
emp p
group by deptno
having comm>1;


Generated Map:




3. This example illustrates using the Oracle UPPER function and simple
datatype analysis of inputs;


SQL:

select 'MY_VAR' stringcol, 1 num_col, SYSDATE today, upper(ename) uname
from emp;


Generated Map:


4. Set operations are also supported, the example below illustrates a
compound statement with aliases for tables, sorting, deduplication,
filtering (run from SCOTT mappings);


SQL:

INSERT INTO TGT (ename, deptno, code )
SELECT  distinct e.ename en, 5
dn, upper('ggg') x FROM emp e, dual d where e.deptno>1
union
select   v.ename en, 999 dnx, '45000' xx FROM emp v  where
v.deptno>10000
minus
select  p.ename pn,111 fff, '3333' yy from emp p
order by en DESC;


Generated Map:


5. Illustrating some Oracle functions let's see XML extractValue (I
added an alias for the column for it to work, it should be run from HR
depending on PURCHASEORDER table);



SELECT extractValue(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[Part/@Id="715515011020"]/Description')
ev from purchaseorder;



6. Illustrating some more XML (I
changed alias for the column and removed the double quotes around
"Reference" for it to work, it should be run from HR
depending on PURCHASEORDER/EMPLOYEES table);



SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') Reference
FROM purchaseorder, hr.employees e WHERE extractValue(OBJECT_VALUE,
'/PurchaseOrder/User') = e.email AND e.employee_id = 100;




7. An aggregation operator with a subquery (this should be run from HR
mappings node), the subquery is embedded in having expression;


SQL:

SELECT department_id, manager_id
FROM employees
GROUP BY department_id,
manager_id
HAVING (department_id, manager_id) IN   (SELECT
department_id, manager_id FROM employees x WHERE x.department_id =
employees.department_id) ORDER BY department_id;

Generated Map:


Summary
The experts and demo SQL provide a real taster for the accelerated map construction from SQL as mentioned this is not a polished product but an example of expert technology that can be a real time saver.