SELECT
n AS time_id,
TO_CHAR(to_date(n,'SSSSS'),'HH24') AS hour,
TO_CHAR(to_date(n,'SSSSS'),'MI') AS minute,
TO_CHAR(to_date(n,'SSSSS'),'SS') AS second
FROM (
SELECT
level-1 n
FROM
DUAL
CONNECT BY LEVEL <= 86400
)
One of the questions that regularly comes up in the forums, or during presentations on Oracle data integration products, is on the future of Oracle Warehouse Builder and Oracle Data Integrator. People wonder which tool to use for new projects? What should be done with existing implementations? In this article we will first compare the features and functionalities of both products and then I will answer these questions.
Oracle Data Integrator Enterprise Edition
In January 2009, Oracle released a statement of direction for ODI and OWB: “Today Oracle includes Oracle Data Integrator (ODI) and Oracle Warehouse Builder (OWB) as the two components of ODI-EE and will merge them into a single unified data integration technology platform. This strategy fully preserves any existing development investments of all Oracle data integration customers and will provide a seamless, easy upgrade path from the current components to the unified platform and beyond.”
Both data integration tools will be licensed as one product, i.e. you don’t have to buy a separate license for Oracle Data Integrator and the OWB Enterprise ETL features. The Oracle Warehouse Builder core ETL features are installed with the Oracle RDBMS and remain free to use (as long as you are licensed for the RDBMS). From what I hear from Oracle they will also offer a free data integration component going forward even when the two products have merged into a unified platform.
Google Trends
Apparently Google Trends can predict the outbreak of a flu virus more accurately than traditional methods. Why not let it have a go at predicting the future of ODI and OWB. As you can see from the Google Trends chart below, we are dealing with a slow but steady increase in searches for Oracle Data Integrator. On the other hand we are seeing a slow and steady decline in queries for Oracle Warehouse Builder. Not really unexpected.
Declarative Design vs. Traditional ETL
ODI follows a declarative design approach. This is the key differentiator between the two products and the one that gives Oracle Data Integrator a clear advantage over Oracle Warehouse Builder. Declarative design expresses the logic of a computation without describing its control. What exactly does this mean? In data integration projects loading and integration strategies are very similar, e.g. in a data warehouse project 80%-90% of fact tables follow the same loading strategy: first we disable foreign key constraints and indexes, then we look up surrogate keys and finally we load the data into the fact table. In a traditional ETL approach we would create a mapping module for each individual fact table. This is very time consuming. In ODI we simply define the loading strategy in a template (this is called a knowledge module in ODI) and with the click of a mouse apply it to every fact table load. Knowledge modules are created using a mix of programming languages, such as SQL, PL/SQL, Jython, the ODI substition API, OS commands, or Java. This data integration approach is very quick to implement and extremely flexible. It essentially gives you the flexibilty of manual scripting in combination with the advantages of an ETL tool (process flows, data lineage, scheduling, impact analysis, metadata etc.).
In a project where most data flows follow a similar pattern, the declarative design approach of ODI will be able to cut down the development time for your transformations by a factor of 30-50%. In a project where each transformation is unique you will not benefit too much from the declarative design approach in terms of productivity. However, from my experience this situation is extremely rare.
Data flow in Oracle Warehouse Builder
Data flow in Oracle Data Integrator
Data servers
One of the reasons why Oracle Warehouse Builder never made it into the top spot in the Gartner Magic Quadrant for ETL tools was its limitation on Oracle as the target server. You don’t have this limitation with ODI. In ODI you can use any technology that can be accessed via JDBC as either your data source or your target.
The ELT approach
Both products are ELT tools. They don’t need a separate transformation server but use the power of the target RDBMS server for transformations. This is a key differentiator to traditional products such as Business Objects Data Integrator.
Change data capture (CDC)
Oracle Data Integrator ships with change data capture knowledge modules. It supports near real time data warehousing out of the box.
Even though ODI allows you to quickly set up CDC, you will not get far with your project without a sound understanding of the underlying change data capture technology. In an Oracle world this is Oracle streams. Oracle Warehouse Builder has no interfaces to CDC. Of course, you can use it on top of Oracle CDC as this article by Mark Rittman shows . However, most of the work needs to be done at database level.
Data Modeler
Both products ship with a basic data modeller. The ODI Common Format Designer (CDF) is ideal for prototyping as you can quickly click together your target model from your source model. This then can be forward engineered to your target data server, e.g. a data warehouse. This saves you from creating a separate source to target map document as all the relationships are stored in the repository. Once you have established the relationship between the source and target attributes, ODI automatically generates data flow interfaces for you. Another brilliant feature. The functionality of the CDF for data modelling purposes, however, is rather limited. While you can define basic functionality and logical models (attributes, constraints, table relationships etc.) ODI won’t allow you to define the physical attributes of your tables, e.g. tablespaces, partitions etc. All of this functionality is available in OWB.
OLAP
In OWB you can create multidimensional OLAP cubes directly from a relational star schema. This is done through analytic workspaces and Oracle OLAP. You can’t load directly into Essbase cubes. ODI on the other hand can directly load your data into Essbase cubes. Various knowledge modules are provided out of the box for Essbase. ODI also allows you load into Oracle OLAP cubes.
Scripting Objects
Oracle Warehouse Builder relies on OMB+ and TCL for scripting objects. This is useful for updating a lot of objects in one go, e.g. if you want to set the tablespace for a group of tables. One drawback here is that you can’t create TCL scripts from your objects. It would be a nice feature to create a script from the mappings you create in OWB. ODI relies on XML to import and export objects. In theory you can script your objects via XML and then import into ODI. Unfortunately, no documentation exists on this so this will not be straight forward. One of the new features in Oracle Data Integrator 11 will be a proper API for scripting objects. Looking forward to this.
Misc.
ODI allows you to encrypt your objects. This could be useful for third party vendors of Knowledge Modules. With ODI you also have proper version and source control built into the product. OWB allows you to take snapshots of your objects. However, this feature does not allow for version control in the traditional sense. Personally, I have found the snapshot feature awkward to use.
Available resources
When I started out on ODI in January 2008 there was very little documentation available. Available documents I found poor. This has changed completely. In my opinion the best starting point to learn about ODI is the ODI Best Practices in a Data Warehouse guide. A quick search on LinkedIn revealed that there are currently 1,469 people registered with Oracle Warehouse Builder skills. On the other hand we just have 334 users with ODI skills. On Metalink there are now three times more documents on ODI than on OWB. There is also a lot of ODI related activity on the Oracle forums. While there is more OWB knowledge and skills out there, ODI is catching up fast.
What to do then?
Over the next two years Oracle Data Integrator and Oracle Warehouse Builder will merge into one unified product. In calendar year 2011 we will have a unified platform. The comparison above has shown that out of the two products ODI has more to offer. You can expect that a lot of the functionality for the unified platform will come from ODI.
In my opinion, only tactical data integration projects that solely rely on the Oracle Warehouse Builder core ETL functionality (the stuff you get for free with the RDBMS) should be implemented using Oracle Warehouse Builder. If you are licensed for Oracle Data Integrator EE, you should use Oracle Data Integrator for any new data integration projects you start. While Oracle will offer upgrade paths for both products you can use third party tools to start your migration from OWB to ODI today. Don’t expect that this tool will completely automate a migration. It also seems that migration of process flows is not supported currently.
Any Oracle data integration consultant will need to focus and upskill on ODI functionality going forward. Why not start this exercise with my ODI Knowledge Module article on OTN.
In order to master scripting in ODI I recommend the following books.
Java BeanShell
Scripting in Java: Languages, Frameworks, and Patterns
Jython
The Definitive Guide to Jython: Python for the Java Platform.
Jython Essentials (O’Reilly Scripting)
My article Developing a Knowledge Module in Oracle Data Integrator was published on OTN yesterday.
In the article I demonstrate how to implement best practices for loading a fact table in an Integration Knowledge Module.
I will present the article at the OUG Ireland BI Special Interest Group on 1 July in Dublin.
View the agenda and register for this very interesting event focusing exclusively on Oracle Data Integrator. Besides my presentation we will have an ODI case study from the Irish market. Highlight of the event will be Craig Stewart’s (ex Sunopsis) presentation on integration of ODI with the BI apps package. He will also reveal some of the future plans for ODI 11G. Personally I am really looking forward to this.
Don’t miss this event if you are involved in data integration projects.
The script below will create a date dimension in just one SQL statement. We don’t use performance-killer nonsense such as cursors, functions etc.
We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do the whole thing in 14 lines of code.
SQL> CREATE TABLE d_date AS
2 SELECT
3 n AS Date_ID,
4 TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
5 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
6 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
7 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
8 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
9 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
10 FROM (
11 select level n
12 from dual
13 connect by level <= 2000
14 );
Table created.
Pretty much self-explanatory. The above will create a date dimension starting at 01/01/2008 and ending at 01/01/2008+2000 days = 22/06/2013.
If you want to read up on NUMTODSINTERVAL have a look at the documentation. Intervals are also very well explained in Tom Kyte's Expert Oracle Database Architecture. It is worth buying just for this chapter.
The other day I came across a useful new feature in the Merge statement for SQL Server 2008. This new feature outputs merged rows for further processing, something which up until now (Oracle 11.1.0.7) is not possible in Oracle. This extra functionality can be used to load a Slowly Changing Dimension Type 2 in one SQL statement. As a result you have only one pass over the data, less logical I/O, and as a result improved performance. If you are interested how exactly the Merge statement can be used in SQL Server 2008 to load an SCD2 have a look at this article. Recently, this has also featured in Kimball’s design tip 107. The whole thing was wrecking my head a bit: how can we achieve the same thing in Oracle? Finally, I came up with a solution. I have to admit it is not perfect, as this will only work if you can identify a changed record at source as either an Insert or an Update. So you need to know if the record is a new one or was just modified. In the vast majority of cases you should be able to have this information available at extract time. So if you extract your information from source via change data capture, timestamp created/timestamp modified, audit records , or Oracle Total Recall you should be good to go. Still, SQL Server 2008 has more powerful functionality here. I can see an enhancement request coming …
For this post we will rely on the good old SH schema (Oracle 11.1.0.7).
Let’s first create a sequence that we will be using later on for the surrogate key of our SCD2.
SQL> CREATE SEQUENCE SH.SEQ_PRODUCTS 2 START WITH 10100 3 MAXVALUE 999999999999999999999999999 4 MINVALUE 1 5 NOCYCLE 6 CACHE 20 7 NOORDER; Sequence created.
Next we will create an SCD2 based on the existing sh.products dimension. We will also add a valid_ind indicator and populate the effective to date with ‘end of time’. To refresh your memory, an SCD2 as defined by Monsieur Kimball is used to track history in a DW. Each time an attribute in the underlying source of the dimension is modified, a new record with the updated attribute is created in the dimension. An SCD2 typically has three helper columns. The effective from and effective to date of the record, and a flag to indicate the most recent record.
SQL> create table products_scd as select * from products;
Table created.
SQL> alter table products_scd drop column prod_valid;
Table altered.
SQL> alter table products_scd add valid_ind number default 1;
Table altered.
SQL> update products_scd set prod_eff_to = TO_DATE('31/12/9999','DD/MM/YYYY');
71 rows updated.
Now we will prepare and populate our staging table for the SCD2 load. I have called this products_source. We will be creating 6 records in this staging table. 5 records represent updates to existing records, and 1 record is a new insert.
SQL> create table products_source as select a.*,'U' dml_type from products a where rownum < 6; Table created. SQL> alter table products_source drop column prod_id; Table altered. SQL> alter table products_source drop column prod_eff_from; Table altered. SQL> alter table products_source drop column prod_eff_to; Table altered. SQL> alter table products_source drop column prod_valid; Table altered.
The ‘U’ attribute in the dml_type column marks these records as updates.
Next we update the prod_min_price to simulate a reduction in price and we also create a brand new record.
SQL> update products_source set prod_min_price = prod_min_price*.9
2 ;
5 rows updated.
SQL> INSERT INTO products_source VALUES ('yPhone','Mobile','Mobile',-1,'Mobile','Phone',-1,'Phone',1,'U','P',1,'-',200,200,'-',1,NULL,'I');
1 row created.
SQL> commit;
Commit complete.
We should now have the following records in our products_source staging table.
SQL> select prod_name,prod_min_price,dml_type from products_source; PROD_NAME PROD_MIN_PRICE D -------------------------------------------------- -------------- - 5MP Telephoto Digital Camera 728.99 U 17" LCD w/built-in HDTV Tuner 809.99 U Envoy 256MB - 40GB 809.99 U Y Box 242.99 U Mini DV Camcorder with 3.5" Swivel LCD 890.99 U yPhone 200 I 6 rows selected.
Finally, we merge our staging table into our prodcuts_scd dimension table.
SQL> MERGE INTO products_scd a USING (
2 SELECT
3 CASE WHEN valid_ind = 1 THEN '1'||a.prod_name||'1' ELSE prod_name END as prod_name,
4 a.prod_desc,
5 a.prod_subcategory,
6 a.prod_subcategory_id,
7 a.prod_subcategory_desc,
8 a.prod_category,
9 a.prod_category_id,
10 a.prod_category_desc,
11 a.prod_weight_class,
12 a.prod_unit_of_measure,
13 a.prod_pack_size,
14 a.supplier_id,
15 a.prod_status,
16 a.prod_list_price,
17 a.prod_min_price,
18 a.prod_total,
19 a.prod_total_id,
20 a.prod_src_id,
21 a.dml_type,
22 NVL(b.valid_ind,-1) AS valid_ind
23 FROM
24 products_source a left outer join (
25 SELECT
26 level as valid_ind,
27 'U' as dml_type
28 FROM
29 dual CONNECT BY level <= 2) b on (a.dml_type = b.dml_type)
30 ) b ON (a.prod_name = b.prod_name )
31 WHEN MATCHED THEN UPDATE SET
32 a.valid_ind = 0,
33 a.prod_eff_to = SYSDATE
34 WHEN NOT MATCHED THEN INSERT (
35 a.prod_id,
36 a.prod_name,
37 a.prod_desc,
38 a.prod_subcategory,
39 a.prod_subcategory_id,
40 a.prod_subcategory_desc,
41 a.prod_category,
42 a.prod_category_id,
43 a.prod_category_desc,
44 a.prod_weight_class,
45 a.prod_unit_of_measure,
46 a.prod_pack_size,
47 a.supplier_id,
48 a.prod_status,
49 a.prod_list_price,
50 a.prod_min_price,
51 a.prod_total,
52 a.prod_total_id,
53 a.prod_src_id,
54 a.prod_eff_from,
55 a.prod_eff_to,
56 a.valid_ind
57 )
58 VALUES (
59 seq_products.nextval,
60 CASE WHEN b.valid_ind = 1 THEN SUBSTR(b.prod_name,2,LENGTH(b.prod_name)) ELSE b.prod_name END,
61 b.prod_desc,
62 b.prod_subcategory,
63 b.prod_subcategory_id,
64 b.prod_subcategory_desc,
65 b.prod_category,
66 b.prod_category_id,
67 b.prod_category_desc,
68 b.prod_weight_class,
69 b.prod_unit_of_measure,
70 b.prod_pack_size,
71 b.supplier_id,
72 b.prod_status,
73 b.prod_list_price,
74 b.prod_min_price,
75 b.prod_total,
76 b.prod_total_id,
77 b.prod_src_id,
78 SYSDATE,
79 TO_DATE('31/12/9999','DD/MM/YYYY'),
80 1
81 ) ;
11 rows merged.
SQL> COMMIT;
Commit complete.
For the records updated at source we need to both create a new record for the updated attributes and update the existing record to reflect the changes in effective dates and the valid indicator. For the new records at source (in our case there is just one) we need to create new records in the SCD2.
On lines 24-29 we effectively duplicate the records that were update at source
On line 3 we prefix the prod_name for the first set of updates with the number 1, while the second set of updates does not undergo this treatment. The resultset we will now merge should look as follows:
SQL> SELECT 2 CASE WHEN valid_ind = 1 THEN '1'||a.prod_name||'1' ELSE prod_name END as prod_name, 3 a.dml_type, 4 NVL(b.valid_ind,-1) AS valid_ind 5 FROM 6 products_source a left outer join ( 7 SELECT 8 level as valid_ind, 9 'U' as dml_type 10 FROM 11 dual CONNECT BY level <= 2) b on (a.dml_type = b.dml_type) ; PROD_NAME D VALID_IND ---------------------------------------------------- - ---------- 1Mini DV Camcorder with 3.5" Swivel LCD1 U 1 1Y Box1 U 1 1Envoy 256MB - 40GB1 U 1 117" LCD w/built-in HDTV Tuner1 U 1 15MP Telephoto Digital Camera1 U 1 Mini DV Camcorder with 3.5" Swivel LCD U 2 Y Box U 2 Envoy 256MB - 40GB U 2 17" LCD w/built-in HDTV Tuner U 2 5MP Telephoto Digital Camera U 2 yPhone I -1 11 rows selected.
In the Merge statement we merge the source and target resultset on the prod_name column. This matches now for the first set of the updated records. For these we update the valid_ind and the product effective to date on lines 32-33.
The other records are created as new records in our target dimension. As part of the insert, we strip the prefixed records of their attachment.
The resultset in our SCD2 should look as follows:
SQL> select * from ( 2 select prod_name, prod_min_price,prod_eff_from,prod_eff_to,valid_ind from products_scd order by prod_eff_from desc 3 ) where rownum < 12; PROD_NAME PROD_MIN_PRICE PROD_EFF_ PROD_EFF_ VALID_IND -------------------------------------------------- -------------- --------- --------- ---------- Mini DV Camcorder with 3.5" Swivel LCD1 890.99 01-FEB-09 31-DEC-99 1 Y Box1 242.99 01-FEB-09 31-DEC-99 1 17" LCD w/built-in HDTV Tuner1 809.99 01-FEB-09 31-DEC-99 1 yPhone 200 01-FEB-09 31-DEC-99 1 Envoy 256MB - 40GB1 809.99 01-FEB-09 31-DEC-99 1 5MP Telephoto Digital Camera1 728.99 01-FEB-09 31-DEC-99 1 5MP Telephoto Digital Camera 899.99 01-JAN-98 01-FEB-09 0 17" LCD w/built-in HDTV Tuner 999.99 01-JAN-98 01-FEB-09 0 Envoy 256MB - 40GB 999.99 01-JAN-98 01-FEB-09 0 Y Box 299.99 01-JAN-98 01-FEB-09 0 Mini DV Camcorder with 3.5" Swivel LCD 1099.99 01-JAN-98 01-FEB-09 0 11 rows selected.
One of the tasks during the ETL design process is to create a Source to Target Map. This maps fields from the source system to fields in the target data warehouse. Very often this is done in Excel.
This approach has two disadvantages. First of all the definitions in the source to target map need to be transfered to your target data warehouse. At best this is a script based effort that takes the definitions in your map and automatically creates target tables, columns, indexes, comments/transformation rules etc. in your data warehouse. The other disadvantage is that all the source to target meta data is stored externally to the warehouse in Excel. Typically the source to target map is not updated once the target tables have been created in the DW.
User Defined Properties (UDP) in Warehouse Builder come to the rescue. UDPs allow you to extend the OWB repository, i.e. you can define additional properties on the base objects in Warehouse Builder.
As per documentation: “To define custom objects and properties in Warehouse Builder, you must use the scripting utility Oracle Metabase (OMB) Plus. After you specify the UDOs and UDPs through scripting, you can then create instances of those objects either through scripting or using the graphical user interface (GUI).”
For the source to target map you can create custom properties on the column object, e.g. the source of the column, the source data type, transformation rules etc..
This is done via OMB+, e.g. to define a new property on the column object named Source Table you issue the following command
OMBSWITCHMODE SINGLE_USER_MODE
to switch to single user mode
OMBREDEFINE CLASS_DEFINITION 'COLUMN' \
ADD PROPERTY_DEFINITION 'UDP_b_SRC_TBL' SET PROPERTIES \
(TYPE, BUSINESS_NAME) VALUES \
('STRING', 'Source Table')
OMBSAVE
to persist your changes in the OWB repository
OMBSWITCHMODE MULTIPLE_USER_MODE
to go back to multi user mode.
You can now enter values for your source to target map through the Design Center: Right click on a table > Properties > User Defined tab > Expand Columns > Click on Column… et voila, enter your values. The values you enter are stored in the OWB repos and can also be retrieved from there.
In order to retrieve the Source to Target Map from the OWB repository I have written some SQL that you can adapt to your needs.
SELECT entity_name, column_name, position , MIN(CASE WHEN property_name = 'UDP_a_AREA' THEN property_value END) AS AREA, MIN(CASE WHEN property_name = 'UDP_b_SRC_TBL' THEN property_value END) AS SRC_TBL, MIN(CASE WHEN property_name = 'UDP_c_SRC_TBL_COMMENT' THEN property_value END) AS SRC_TBL_COMMENT, MIN(CASE WHEN property_name = 'UDP_d_SRC_COL' THEN property_value END) AS SRC_COL, MIN(CASE WHEN property_name = 'UDP_e_SRC_COL_COMMENT' THEN property_value END) AS SRC_COL_COMMENT, MIN(CASE WHEN property_name = 'UDP_f_JOINS' THEN property_value END) AS JOINS, MIN(CASE WHEN property_name = 'UDP_g_SRC_COL_DATATYPE' THEN property_value END) AS SRC_COL_DATATYPE, MIN(CASE WHEN property_name = 'UDP_h_SRC_COL_LENGTH' THEN property_value END) AS SRC_COL_LENGTH, MIN(CASE WHEN property_name = 'UDP_i_TRANSFORMATION_RULE' THEN property_value END) AS TRANSFORMATION_RULE, MIN(CASE WHEN property_name = 'UDP_j_NULL_REPLACEMENT' THEN property_value END) AS NULL_REPLACEMENT, MIN(CASE WHEN property_name = 'UDP_k_TRG_TBL' THEN property_value END) AS TRG_TBL, MIN(CASE WHEN property_name = 'UDP_l_TRG_COL' THEN property_value END) AS TRG_COL, MIN(CASE WHEN property_name = 'UDP_m_TRG_COL_DATATYPE' THEN property_value END) AS TRG_COL_DATATYPE, MIN(CASE WHEN property_name = 'UDP_n_TRG_COL_LENGTH' THEN property_value END) AS TRG_COL_LENGTH, MIN(CASE WHEN property_name = 'UDP_o_ISSUES' THEN property_value END) AS ISSUES FROM ALL_IV_COLUMNS a JOIN ALL_IV_OBJECT_PROPERTIES b ON (a.column_id = b.object_id) WHERE a.entity_name LIKE 'D_%' OR a.entity_name LIKE 'F_%' GROUP BY entity_name, column_name, position ORDER BY entity_name, position
You could use this to create a derived table in a Business Objects Universe to create your source to target map via Web Intelligence.
