COMMENT: In The Field

Driving Down Costs
By Ian Abramson

Use “new” functionality within Oracle Database to save money and simplify operations.

In today’s challenging business climate, we must maximize the value that our organizations get from technology investments. Typically, the initial value of our business software solutions is measurable against the initial business requirement, but some software can also provide solutions and value beyond the original purpose.

As an Oracle technology user, I find that Oracle Database has many valuable features beyond its core information management competencies that make it a tremendous option in IT solution toolkits. When used to their fullest extent, these features can eliminate the need for third-party products, thus saving money and reducing system complexity. From data integration, data mining, and online analytical processing (OLAP) to database security and Oracle Application Express, Oracle Database includes many features that are often overlooked. Using just one of these features can reap huge benefits.

For example, as director of the Enterprise Data Group for Thoughtcorp, I recently became involved in a telecommunications project that demonstrates how to increase the value that can be derived from Oracle Database. We’re implementing an integrated data solution that provides information to the financial and marketing departments. Oracle Database is standard technology for the company, but loading and integration tools are not.

In the past, I would have recommended that the telecom look at third-party extract, transform, and load (ETL) tools. Today, I’m not so quick to do that. In this case, Oracle Warehouse Builder was the right choice to maximize the company’s existing database investments while minimizing the cost of new software licenses.

Next Steps


Oracle Database
Oracle Warehouse Builder

On our project, we loaded millions of rows of source data into the database using Oracle Warehouse Builder to leverage external tables. Oracle Warehouse Builder optimized the transforming and loading of information, and it provided a standard approach for reading, transforming, integrating, deploying, and moving the source data through to the integration layer. Within Oracle Warehouse Builder, we created standard dimension and error handling, which significantly reduced the complexity of the project.

Another Oracle Database capability that added value to the project and simplified the process was md5. Part of the dbms_obfuscation_toolkit package, md5 can create a repeatable hash value. We used this value to determine if a record had changed, so that we processed only those records that needed processing—new and changed—to the next layer of the system architecture.

During the project, we gathered best practices collected during previous engagements and from Oracle Warehouse Builder experts and used this information to create a framework that addressed many of the challenges that our solution required. For instance, we established a loading methodology that leverages Oracle ETL technology found in Oracle Database. (The work is done completely in Oracle Database; no other data integration server is used.) We used a staging area, from which the data moves into an integration area, and we provided a historical repository for additional data. Finally, we set up a customized data mart to provide data to end users. Each step in this project reflects planning, organization, and efficiencies that could be used on other projects.

As cost-conscious companies adopt Oracle Warehouse Builder and other built-in features of Oracle Database, developers and architects—members of the user community—need to develop standards and define best practices to ensure that people can take advantage of the functionality. The user community is also helping to spread the news. Oracle user community members consistently share their experiences and approaches with others and allow for the organic growth of standards and best practices for building solutions.

Ian Abramson ( ) is president of IOUG and an Oracle data warehousing expert with more than 20 years of experience. Based in Toronto, Canada, he is the director of the Enterprise Data Group for Thoughtcorp, a technology consulting company. Abramson has written numerous books on both Oracle and data warehousing and is a frequent presenter at Oracle and industry conferences and seminars. He is coauthor of Oracle Database 11 g Beginner’s Guide (Oracle Press/McGraw-Hill, 2008).

Send us your comments