Because of business needs for data retention and regulatory compliance, enterprises need to manage increasingly large databases ranging from hundreds of gigabytes to many terabytes, or even petabytes, in size. As data continues to grow at an exponential rate, DBAs and IT professionals in these organizations face daunting challenges when designing and operating such large databases. The data must be well organized to effectively cope with data growth and to meet service requirements. Challenges include how best to achieve the following objectives.

• Designing databases that can accommodate continuous data growth.
• Keeping database systems lean and high performing.
• Managing data lifecycles more efficiently and less intrusively to keep operational data highly available.
• Reducing the cost and impact of data maintenance operations, such as backup and restore operations, to keep mission-critical data ready when needed, and reorganization and runstats operations to maximize system performance.
• Satisfying near real-time requests for transactional data or the complex analytical query requirements for large data sets, often including historical data, while reducing the total cost of ownership (TCO)

The explosio

n in data, query complexity and low latency demands has come at a time when another trend has been at work in data warehousing. Tighter IT budgets and pressures to reduce costs are placing a strong emphasis on justifying expenditures through demonstrable returns on investment. How can companies balance these seemingly conflicting demands? Now more savvy about the total costs of data-warehouse operations and aware of the management and database administration challenges, IT buyers are demanding lower prices from vendors and more attention to their specific needs.

Budget pressure is endemic in an age of global competition, but the BI and data-warehousing community has been under special scrutiny from business-side executives. In our experience, as many as 75 percent of data warehouses have fallen short of producing business benefits commensurate with the expenditures. Although vendors spotlight on the smaller percentage that has experienced fabulous success, the vast majority are still looking for the big business upside.

Vendors, therefore, face a tall order: solve more difficult problems even as they respond to greater pressure on pricing. The most established data warehouse platform vendors--IBM, Microsoft, Oracle and NCR's Teradata Division--share a focus on four principal objectives:

• Increase the capability to handle scale and complexity

• Simplify operation, management and use

• Incrementally adjust software pricing downward

• Pass along hardware savings

Let's take a closer look at what's most interesting about how each of these vendors is attacking these objectives.

IBM. With DB2 version 9, the company is simplifying the buying process by introducing a preconfigured unit of data warehouse capacity: the balanced configuration unit. The BCU sets out what a customer would need in DB2 software, servers, storage and interconnect technology to meet specific objectives. Design wizards advise users on physical implementation techniques. To improve performance, scalability and manageability, version 9 also offers range partitioning and data compression; these technical features work in concert with existing multidimensional clustering to help customers reduce I/O workload and overall latency.

Microsoft. SQL Server 2005 was a major advance for Microsoft. The largest SQL Server data warehouse identified in the 2005 TopTen Survey contained 19.5 TB of data, which was more than 10 times larger than the biggest SQL Server data warehouse in 2003. The 2005 version introduced range partitioning for both tables and indexes. Microsoft also offers 64-bit support to improve data caching and reduce I/O, plus a new set of data integration services for extract, transformation and loading (ETL).

Oracle. In the latest version of its database, Oracle 10gR2, Oracle offers a grid architecture that uses high-speed interconnect networks to arrange storage and server resources. Oracle 10gR2 promises that applications, databases and workloads can be distributed and managed transparently: that is, without users needing to grapple with knowing about the underlying platforms. Oracle's long-term vision is that it should be possible and practical to expand, reconfigure and adapt the grid dynamically to meet changing requirements and ebbs and flows in traffic; respond to equipment, power and network failures; enable maintenance; and expedite or slow down work in response to changing priorities. While Oracle plans a series of releases to fully realize these goals, 10g's Real Application Clusters (RAC) technology is already in widespread use for data warehouses.

NCR/Teradata. As an extension to its pre-existing toolset, the Teradata Active System Management (TASM) facility is an important advance for Teradata customers trying to gain the upper hand in managing large workloads. In coming releases, the company's goal is to enable users to specify service levels and define priorities and policies--and let the system do almost everything else by itself. This includes supporting both the short, rapid queries necessary for "active," operational data warehousing as well as conventional, long-running, strategically oriented queries and large batch jobs. Advances in parallelism, query processing and optimization are also on Teradata's agenda.

Teradata, which did much to define data warehousing originally, remains the farthest ahead in addressing enterprise data-warehouse requirements: that is, multiple scalability challenges shown in the second figure that are driving growth toward monster systems. Microsoft has the furthest to go. However, all will be major players in the enterprise data warehouse game in the coming years.

 

Getting A Handle On Growth

What should you do to prevent data warehouse requirements from surpassing your ability to manage them? You don't have to be implementing a 500 TB warehouse to encounter challenges in performance, scalability or availability. The complexity of the problems often stumps organizations more than just sheer scale. A company might have less than a terabyte of user data but face such complex business rules that no database system could easily handle the benchmarking. With just 1 TB of data and a sufficiently complex schema and workload, you'll find yourself up against some tough problems.

The first step is to understand the business drivers. Make sure your business-level requirements and schedules reflect those drives accurately. Then be sure to define those requirements by year over a strategic time frame (typically three to five years). Third, develop a set of concrete usage scenarios that are characteristic of the workload that the business requirements will demand.

With the business drivers and requirements clear, you can focus more closely on the data-warehouse issues, including database size, structure, workload and service-level agreements. Build a margin of safety into your requirements; you'll never have complete certainty about how the system will be used and what might ultimately expand those requirements (such as success!). Then it's time to evaluate the long-term and near-term trade-offs:

• In the long term, the ability to leverage data from across the enterprise pays huge dividends, and is the key to most of the success stories you hear about. Clearly, the core of your enterprise data must be integrated to provide a platform for rapid and inexpensive implementation of analytical solutions.

• In the near term, you want to identify where you can gain significant cost or performance advantages in a specific application or sandbox with a data-mart or appliance approach. Remember that some benefits may come at the price of fragmenting decision support data and incurring greater overhead for replication, ETL and other data movement and integration.

Testing your intended solution against your detailed requirements is critical before committing to it. This advice applies to everything that will be important to handle scalaby: the platform, configuration, database design and more.

Remember, previous experience is not always a good predictor of future success. And don't simply wait and hope that scalability problems will work themselves out. Take the initiative to prove to yourself--not to mention all your stakeholders within and outside the enterprise--that the solution you've chosen will scale and be strong enough to withstand the challenges of many kinds of growth.