The Case for OLAP

Online analytical processing (OLAP) refers to a category of business applications that are used to store and analyze large, relatively complex data sources in business disciplines such as sales and marketing analysis, budgeting, resource planning, performance measurement, and other types of business reporting. Although OLAP is not a new concept, it is still not widely understood.

Many organizations have anywhere from hundreds of megabytes to upward of several terabytes of related data, yet these organizations use software applications that are not optimized for analyzing this amount of data, or they don’t use the data analysis software they have to their full benefit. It is impossible to load several hundred thousand records into a single Excel workbook or tens of gigabytes of data into a single Access database. Likewise, organizations might have dozens or even hundreds of employees entering data into a single database at the same time. When very large databases are being used by dozens or hundreds of both data-entry workers and data-analysis workers at the same time, the database systems’ resources can be stretched to their operational limits, bringing entire systems to a standstill.

So what is the answer from a software perspective? Currently, many businesses use limited and inefficient approaches. For smaller data sources, several Excel workbooks might exist on a network computer, with each workbook containing only a portion of the data, typed in by a single employee. The problem with this approach is that there is no straightforward way to combine or aggregate the data from the separate workbooks into a single workbook, where analysis of the complete set of data can occur. Also, subtotals and PivotTable reports work very slowly when a lot of data is being analyzed because Excel has to calculate summarizations every time new figures are needed. And, if any single workbook is compromised for some reason, it is difficult if not impossible to recover the data.

For larger data sources, several Access databases might exist on a network computer, with each database containing a portion of the data, and several employees entering data into each database. This approach is better than using Excel workbooks because you don’t have to require users to stop working on the data when you want to analyze data or build reports, you have built-in features to handle relational data, and you have more options for data disaster recovery. However, Access limits the number of simultaneous users per database and imposes a limit of two gigabytes of data per database.

For very large data sources of several gigabytes or more—as well as for databases that require several dozens, hundreds, or thousands or users—organizations often use server-based database management systems such as Microsoft SQL Server. SQL Server databases have a lot of headroom; they can handle terabytes of data and thousands of simultaneous users, and they have a high degree of data disaster recovery, including the ability to recover individual data records that have been compromised.

However, even with SQL Server, there can be scenarios in which a database designed primarily to record business transactions responds very slowly to simultaneous data entry and data analysis requests. For instance, some travel agents might be accessing a database to book flights and reserve hotel rooms at the same time that a marketing specialist is using the database to analyze sales for the last quarter. The travel agents have to put customers on hold because the database server is responding too slowly. Likewise, the marketing specialist may complain that her tasks take too long. The travel agents cannot tell the marketing specialist to do her work only during off-peak hours, and the marketing specialist obviously cannot tell the travel agents to stop helping customers..

The solution for analyzing data in very large databases, databases with many simultaneous users, or databases with several relational data tables, is to use an OLAP software application such as Microsoft SQL Server 2000 Analysis Services. Analysis Services can take a copy of an organization’s data, efficiently organize the data, and calculate summarizations of the data ahead of time. This allows both data-entry and data-analysis workers to benefit from huge performance gains because data analysts are working with a copy of the data, using summarizations that have been calculated ahead of time in categories that data analysts need (fiscal quarters, net revenue, and so on). Even smaller databases can benefit from the data storage, data compression, and resource optimization features of Analysis Services.