INTRODUCTION

One of the common tasks you are faced with in IT is the loading of data. Most organizations have numerous jobs running that move data from one system to another system or from files into databases or vice versa. In many of these cases PL/SQL is custom coded to take care of all of these jobs, but it is the question if this is the most effective and efficient way of doing data movement. Many reasons exist as to why organizations need to move data around. Let's look at one of the more common ones to understand some of the challenges involved in this operation. Our scenario is the case where you are the IT manager in an organization selling widgets, and your management is requesting various reports of the monthly sales revenues and forecasts out of the OLTP order system. For this question to be answered you typically move data from the OLTP system into a separate system allowing you to optimize query performance and incorporate the advanced forecasting functionality. What you need to move the data is some code that extracts the data from the various OLTP tables and combines it to get the desired results. You will have to aggregate your orders to report on month level and then store the data. To get that code you have some options: you can either write the code for the extractions by hand, or you use a tool like Oracle Warehouse Builder to generate this code.

DEFINING WAREHOUSE BUILDER'S SCOPE

While Oracle Warehouse Builder is most typically characterized as an ETL1 or dataintegration tool, it does many more things than just moving data around. Essentially it is a design tool for data warehouses, allowing you to design the target schema (both relational and multi-dimensional) to hold the data, the ETL processes to get the data into the target schema, and the end user application foundation to analyze the data, thus providing a one-stop-shop for data warehouse infrastructure.
Oracle Warehouse Builder is built on top of a metadata repository that captures your design. Once you have created your design in the repository, you generate the code and deploy it to a database. Schema and object definitions are generated usingSQL. For data integration routines, the tool generates a combination of SQL and PL/SQL, thus allowing you to perform complex transformations on the data you are moving.
It is quite possible that you did not model your schemas, both for your original source systems and your target in Oracle Warehouse Builder but in tools like Oracle Designer or Computer Associates ERWin. In this case you can exchange metadata between these tools and Oracle Warehouse Builder allowing you to use both tools for your preferred activities. Extraction can be done from various sources. Of course Oracle databases can act as a source, but also the other common databases such as IBM DB2, Microsoft SQL Server and others. Flat files are part of the source and target systems for Oracle Warehouse Builder, and you can both extract from and write to flat files. Once you have designed you data store or schemas and loaded data, Oracle Warehouse Builder allows you to design and move extra metadata to common query tools in the market such as Oracle Discoverer or Business Objects. You can also create specialized OLAP objects to cater for the advanced analytics such as the forecasting we discussed in the example. Most tools stop here with respect to utilizing the metadata - not Oracle Warehouse Builder. One of the strongest points of Oracle Warehouse Builder is that it continues to support your design cycle by easily allowing additions and changes to the metadata. Oracle Warehouse Builder allows you to understand upfront what impact a change has on your system and then assists you in implementing this change, both in the ETL and in the schema design. In other words, Oracle Warehouse Builder assists you during the entire life cycle of your system, not just with your initial design.

ORACLE WAREHOUSE BUILDER SAVES YOU TIME AND MONEY

By now you are probably wondering how the overhead of a metadata repository, a tool, and some generated code can save you time or money So let's approach this from two sides: the technical side – can the tool solve my problem and if possible with less time and effort – and the business side – can the tool solve my problem and save me money. Oracle Warehouse Builder has Some unique characteristics that help you achieve just that for your organization. Contrary to engine based ETL tools, which extract data and then work in their own engine on transformations before loading it into the database, Oracle Warehouse Builder extracts the data and then performs any transformation directly within the database where the data is eventually stored.This means with the engine based technology, you have two machines (see Figure 2) to do the same workload that Oracle Warehouse Builder does on one machine. So while the data warehouse machine sits idle during the night, the ETL machine is churning away at your jobs. When your users are discovering their monthly sales, the data warehouse machine is working and the ETL machine is doing nothing. This is not an efficient use of resources.
So running the ETL jobs during the night and doing querying during the day maximizes the machine utilization. An extra benefit from the simpler architecture of Oracle Warehouse Builder is that the administration becomes simpler. Now your Oracle DBA can do the administration for all work being done, instead of needing a DBA and an ETL administrator. Another benefit Oracle Warehouse Builder gives you is the close alignment with functionality in the Oracle database. Due to enhancements and new features in different releases of the Oracle database, the same logical data move can be implemented in different ways, leveraging new functionality.For example, changing an 'INSERT' statement and an 'UPDATE' statement into one combined 'MERGE' statement3 makes your code perform better without adding any additional hardware. If you wrote your own code you will have to invest significant time to change all the routines and verify they are correct. This is a costly operation. Oracle Warehouse Builder does not only use these new statements with new code. The tool also allows you to upgrade all your existing designs with the flip of a switch. In this particular example, you would simply change the generated code version to 9i, and you instantly have the new statement generated in all applicable places. Of course you are also utilizing the database better by using the functionality you paid for (instead of paying extra for a separate ETL engine).

CONCLUSION

As many customers have found, Oracle Warehouse Builder is a powerful assistant in the data warehouse environment. Technically the tool has many advantages that can make your development faster and of higher quality, while still giving you scalability and performance. With its database-centric architecture it gives you better leverage of your database and the hardware Supporting the warehouse. Business-wise the tool absolutely makes sense. If you compare it with other tools in the market the price positions almost certainly gives Oracle Warehouse Builder a higher ROI than comparable tools. If you compare it with custom coding efforts, Oracle Warehouse Builder can let you do more with less effort, again increasing the ROI for Oracle Warehouse Builder and outperforming your custom code. So if you are planning your data warehouse (or a next phase for it) make sure to take a look at Oracle Warehouse Builder - it might just make you even more successful!