The typical Oracle house is Internet-driven, focused on accessing the rest of the world via the Web. Such a company has as much (or more) need of high-efficiency performance as any conventional client-server transactional environment, and has a potentially strategic advantage in the means by which customer/user system usage can be monitored and fine-tuned.
Companies go with Oracle because of its unparalleled Internet friendliness. The benefits of data warehousing, which can be deployed in the service of high-performance system tuning, are obvious. And enabling the kind of data-intensive applications that are typical of data warehousing for use over the Web can be a major strategic advantage. Bringing Oracle and data warehousing together is almost always a great idea, and it isn't the tactical challenge that it might appear to be.
Oracle 9i loves data warehousing
The reasons for this perfect match are three-fold. First, the architectural enhancements to Oracle Database that were introduced with 9i are ideally suited to data warehouse structures, storage requirements, and I/O patterns. Second, the suite of products available for data warehouse implementation and management build on Oracle technologies that you, as an Oracle user, are already likely to know. Third, Oracle's Enterprise enhancements specifically enable efficient data mining and other business intelligence functions that you'll want to use once you have a data warehouse in place.
What's the difference between an Oracle database and a conventional relational database? And why is it best for data warehouse implementation? It's important to have a very solid grasp of this as you design your data warehouse environment:
- Partitioning must be much more refined in data warehouse database design than in conventional RDBMS design in order to maintain efficiency. Oracle 9i employs a partitioning method that specifically facilitates administrative management of data storage to partition by selected values.
- Conventional online transaction processing (OLTP) systems do many reads of small amounts of data; data warehouses do occasional reads of large amounts of data. Oracle 9i permits table-joining via bitmapped indexing to create complex arrays that may be called up with minimal I/O.
- In a conventional RDBMS, transformation of data loading is generally done internally and is often a custom-programming proposition; in a data warehouse environment, there is no time for this. Oracle 9i permits you to define transformations externally, greatly facilitating the manipulation of inbound data for integration.
The point here is that you're ahead of the game before you've even begun writing up your data warehouse specs, because Oracle's database technology is specifically geared to the kind of architecture your warehouse must have to be efficient.
Here are the Oracle tools that will enable your warehouse implementation. Note that not all of them are warehouse-specific; this speaks to the integrated architectural advantages of an Oracle database and Oracle's concept of warehouse management:
This is one of the biggest, baddest wizards you've ever used. It's hard to imagine that something as big and complex as a data warehouse could be built by a wizard, but since Oracle is building on top of structures already available and well understood, it's almost insultingly painless.
Warehouse Builder allows you to define your data sources; implement data flow between sources and destinations (Extract-Transform-Load, or ETL); design and deploy the appropriate schema; define all of your tables with easy dimensioning and importable definitions; and design and generate your query environment, including OLAP. Do you lose anything by following this "canned" approach? The GUI gives you every option you could want, and using Warehouse Builder results in a metadata repository that you'll find very convenient later on (e.g., storing your entire warehouse design in one place, where it can be easily referenced).
You can set up and test reporting with this combination administrative/viewing tool. The power of this tool is that it works with browsers and is integrated with Oracle Portal. With that, both internal and external users have an instantly accessible Web reporting mechanism that taps the warehouse directly.
Oracle's data warehouse database architecture is so well integrated that Enterprise Manager, which can be used on any database, is an ideal management tool. You can handle data transport, backup and recovery, resource management, system monitoring, and every other administrative task from the OEM Console.
Oracle's super-powered Internet app suite is your new data warehouse's ace in the hole. Internet and internal security, caching, portal setup/management, site usage intelligence, messaging, and J2EE are all handled through 9iAS (Discoverer and Enterprise Manager are actually part of it). Again, because of the deep integration of these services with Oracle database technology in general, with which your new warehouse is fully compatible, you have all these goodies available to you right out of the box.
And that's not all
Oracle Server facilitates transformations of inbound data via a staging process, which is particularly useful in the data warehouse ETL step. Migration of data from tables in your conventional OLTP database into your data warehouse is particularly convenient and can be accomplished with Java-stored procedures or SQL (or PL/SQL).
Oracle also has automatic memory management, which is extremely useful in data warehouse usage. Because of the huge amounts of data typically used in data warehouse analytics and the constant fluctuations in table size, keeping the system tuned would be a full-time job for your DBA if it wasn't handled automatically; so Oracle built it in.
Oracle's Internet strategy is second to none. The suite of service-based products (in Oracle Application Server) is staggering and basically is designed to facilitate an "in-house" Internet architecture (via Oracle HTTP Server) that can be opened up via portal to the outside world. That is, you can configure with relative ease a company-wide Internet for data-gathering, analysis, and reporting purposes; then you can define services that are useful to customers and external users and make those services available on a public Web site. Oracle is nothing if not Internet-friendly. And all of this Internet power is fully compatible with the data warehouse you'll deploy next to it.
Finally, we're left with a vision of Oracle 9i and Oracle data warehousing, in partnership, extending the general Oracle technological mission of Web friendliness into the sometimes intimidating domain of data warehousing, where data flows like the Mississippi. If this is where you live, you no longer need to fear data warehousing: Oracle saw you headed in that direction to begin with.
Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence and social informatics, primarily in the health care and HR industries.