Data Storage Options

When organizations make the switch to a combination of OLTP and OLAP computer systems, they frequently do not plan their computer network architectures fully. Usually, these organizations go through a trial-and-error approach, in which they deploy their computer systems, realize that mistakes were made, and then deploy the same computer systems again. The results of redeployment are usually significant in both time and money. Planning a good data storage strategy is one of the most vital components to successfully deploying an efficient OLTP/OLAP system.

Storing large amounts of data on individual laptop and desktop computers, or on network computers without data recovery mechanisms such as backups in place, can lead to disaster if the data is ever compromised or lost. Products such as SQL Server provide for backups, transaction recovery and transaction rollbacks, advanced indexing for finding data more quickly, and automated database management wizards. (Chapter 12, “Maintaining Data Reporting and Analysis Systems,” covers data management in more detail.)

A business environment in which a lot of simultaneous OLTP and OLAP tasks are performed requires both dedicated OLTP database server computers and dedicated OLAP database server computers. The OLTP databases are used strictly for data entry, and the OLAP databases are used strictly for data analysis. During time periods when the OLTP databases are inactive (for instance, nights and holidays), the OLAP database server computer can make a copy of the OLTP data and store it in a separate database, called a data mart. In practical terms, for smaller organizations this means that a server computer running SQL Server will accept OLTP data in a single database during business hours, another SQL Server database on the same server will warehouse the OLTP data during off-business hours, and an instance of SQL Server 2000 Analysis Services will analyze the warehoused OLTP data. This, of course, assumes that the SQL Server 2000 server computer has enough resources to handle these workloads. Medium to large organizations might have as many as three sets of database server computers: one set of computers running SQL Server that accept OLTP data during business hours, one set of SQL Server 2000 computers that warehouse the OLTP data during off-business hours, and a third set of computers with SQL Server 2000 Analysis Services that interact with the warehoused OLTP data whenever it is needed, day or night. Depending on your organization’s budget and anticipated computer network traffic, you should deploy the most effective combination of OLTP and OLAP server computers.

There are three main data warehouse storage types: multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP). Understanding these data warehouse types will help you plan OLAP systems that are fast and efficient.

• MOLAP stores the data and the aggregations on the OLAP database server. If you want good overall performance and data compression, you should consider a MOLAP approach. MOLAP is good for OLAP databases that need to be queried frequently; the data and the aggregations are stored on the same database server, providing for the fastest performance.

• ROLAP stores the data and the aggregations in a relational database. If you already have a considerable investment in a relational database management system, if your OLAP data will potentially contain more than 10 million members, or if you have large databases that are infrequently queried, you should consider a ROLAP approach. Although ROLAP performance is generally the slowest of these three storage types, it in many cases can provide you with the ability to store larger amounts of data.

• HOLAP stores the data in a relational database, but it stores the aggregations on the OLAP database server. This approach provides the benefits of quick access to aggregations while minimizing duplicate data being stored. If you are concerned about database size, or you are frequently asking for summarizations from large amounts of data, you should consider a HOLAP approach.

For more information about MOLAP, ROLAP, and HOLAP, see Microsoft SQL Server 2000 Analysis Services Books Online, which is included with the product.