Introduction to OLAP Concepts and OLAP Reporting

OLAP is an analysis-oriented technology that enables rapid analysis of large sets of aggregated data. Instead of representing information in the common two-dimensional row and column format of traditional relational databases, OLAP databases store their aggregated data in logical structures called hypercubes more commonly referenced simply as cubes. These OLAP cubes are created around specific business areas or problems and contain an appropriate number of dimensions to satisfy analysis in that particular area of interest or for a specific business issue. OLAP is a technology that facilitates data viewing, analysis, and navigation. More than a particular storage technology, OLAP is a conceptual model for viewing and analyzing data. Table 20.1 highlights some common business areas and typical sets of related dimensions.

Table 20.1. Business Areas and Commonly Associated OLAP Dimensions

Business Area	Associated Business and Common OLAP Dimensions
Sales	Sales Employees, Products, Regions, Sales Channels, Time, Customers, Measures
Finance	Company Divisions, Regions, Products, Time, Measures
Manufacturing	Suppliers, Product Parts, Plants, Products, Time, Measures

OLAP cubes pre-aggregate data at the intersection points of all of their associated dimension's members. A member is a valid field value for a dimension. (For example: Members of a time dimension could be 2000, 2001, Q1, or Q2; and members of a product dimension could be Gadget1, Gizmo2, DooDah1, and so on.) This pre-aggregation facilitates the speed-of-thought analysis associated with OLAP.

Precalculating all the numbers at the intersection points of all of an OLAP cube's associated dimension members enables rapid high-level analysis of large volumes of underlying data that would not be practical with traditional relational databases. Considering the example of analysis on several years of sales data by year, quarter, and month and by region, sales manager, and product, the preaggregated nature of OLAP facilitates quick speed-of-thought analysis on this data that otherwise would not be practical working with the phenomenal amount of storage space required in a traditional relational (OLTP) database system.

When a Crystal Report uses an OLAP cube as a data source, it will present the multidimensional data in a two-dimensional OLAP Grid that resembles a spreadsheet or cross-tab. The focus of Crystal Reports when reporting against OLAP cubes is to present professionally formatted two-dimensional (or flat) views of the multidimensional data that will be of particular business use for report consuming end users and not necessarily analysts.

The concepts of OLAP usually become more understandable once they are actually explored. To that end, later sections in this hour will step you through a Crystal Reports report creation example against an OLAP cube.