Oracle Automates the Data Warehouse

In the past, deploying a data warehouse has been a timely, meticulous process: Information had to be hand-coded and stitched together. Oracle Corp. recently unveiled a new tool to help eliminate the mistakes and guesswork common with many data warehouse building projects.

Oracle Warehouse Builder (OWB) is an extensible deployment framework that automates much of the work needed to create a data warehouse or data mart. OWB retrieves information from multiple sources and puts it all together for a full view of the data. Its primary focus is on obtaining e-commerce data, so OWB can help large businesses look at customer habits and consumer needs.

"The problem is that customer information is very scattered," says Jagdish Mirani, senior director of the data warehouse program at Oracle (www.oracle.com). "Inventory systems, shipments systems, e-commerce data: All the information is fragmented so businesses don't have a complete understanding of what their customers are about."

OWB helps businesses put the pieces together and form a composite. This helps businesses cater to the needs of its customers. "This technology allows you to piece the data together so you can get a full view of customers," Mirani says.

Essentially an extraction, transformation, and loading (ETL) tool, OWB uses a multistep process. Since data often comes from so many sources -- such as ERP, Web logs, e-commerce systems, and home grown applications -- the biggest task is to extract the data from the different sources, and transform the info so it makes sense. Before the data can be relocated to a central warehouse it must be transformed. OWB ports the data to Oracle8i, the company's signature database, where it is transformed and consolidated.

Oracle is working with the Object Management Group Inc. (OMG, www.omg.org), a consortium that works to develop standards and specifications, and other vendors to establish metadata standards. This is an important aspect of transformation because a common metadata standard would make data exchange easier between components from multiple vendors, allowing for tighter integration between different products.

Usually a company would have to write the transformation application in-house. With OWB, the process is automated. And since the process is automated, it opens the door for a lot of people to deploy a data warehouse. A user no longer needs to be overly proficient in ETL coding to import data. "The intuitive graphical user interface is supported by wizards," Mirani says. By using OWB's graphical drag-and-drop schema, a user can see where the data is coming from and then drag it over to where they want it. Once that is done, the ETL code is written automatically. The user provides the schema, but OWB writes the code. But, Mirani says, "It is possible to write your own code and bring that into the warehouse builder environment."

To do this OWB must be working in conjunction with Oracle8i. The integration of the database and OWB allows Oracle8i to act as a transformation server, so that an additional transformation server is not needed. Despite the recent announcement of Oracle8i Release 2, OWB was built with the first release in mind. Each subsequent release of Oracle8i, however, will have features added that enhance the capability of OWB.

It is also important for users to note that other ETL tools will work with Oracle8i. "Oracle just built the automatic bridges for use with their own analytic tools, but other ETL tools are compatible," Mirani says. "Integration and openness don't have to come at the cost of one another. You can be integrated, but yet open."