Chapter 19: Reporting from OLAP Cubes

Relational database systems provide the vast majority of the data analysis and reporting capabilities that most organizations need. However, a smaller segment of many organizations can benefit from more flexible and sophisticated analysis tools. In many cases, an organization s higher-level analysts are still using spreadsheet tools in combination with relational databases to make strategic company decisions.

Online analytical processing (OLAP) is a newer analysis technique that provides much more flexibility in analyzing company data. Crystal Reports supports most standard OLAP tools and enables you to create reports based on these database systems.

What Is OLAP?

Most typical company data is viewed in two dimensions, whether it is viewed by a reporting tool such as Crystal Reports or a spreadsheet program such as Microsoft Excel. In the case of a spreadsheet, the rows and columns constitute the two-dimensional analysis. You may be viewing product sales by state, salesperson volume by product category, or customer totals by demographics . In each of these cases, you typically can see only two dimensions at any one time, even though you actually may prefer to analyze a combination of three or more of these factors.

Online analytical processing is a leading-edge analysis approach that allows data to be analyzed and viewed in multiple dimensions in real time. With a typical OLAP tool, you may initially be presented with a spreadsheet-like view that shows information using two dimensions, such as product sales dollars by state (or perhaps by country or region, allowing drill-down to the state level). This initial two-dimensional view is natural ”despite the ability of OLAP tools to analyze multiple dimensions, most human beings still readily conceive data only in two dimensions.

Even though only two dimensions are visible, additional dimensions are available behind the scenes to control the two dimensions you are viewing; you can limit dollars by state by using other dimensions. Perhaps you want to see dollars by state limited to just certain sales reps, certain customer demographics, or certain product categories ”you still see the original Dollars and State dimensions, but the numbers are filtered by the other dimensions. You may also want to quickly see sales by product category, demographics by state, or some other combinations of dimensions. Using an OLAP analysis tool, slicing and dicing through the different dimensions is typically as easy as dragging and dropping one dimension on top of another. Figure 19-1 shows the multidimensional FoodMart database included with Microsoft SQL Server 7.0 OLAP Services or Microsoft SQL Server 2000 Analysis Services.

Figure 19-1: SQL Server Analysis tool viewing OLAP cube

Whereas relational databases represent data in a two-dimensional, row-and-column (or field-and-record) structure, OLAP databases utilize a multidimensional structure known as a cube (which, despite its name , is capable of storing more than three dimensions of data). Often, cubes are built on the basis of regular relational database systems ”the relational database is populated using typical data-entry or data-import techniques, and a cube is built that is based on the relational database. Cubes can be refreshed or repopulated on a regular basis to allow multidimensional, real-time analysis of the data in the relational database.