Crystal Reports: Use an OLAP Grid

You can insert an OLAP grid object into your Crystal Report. This is like a super crosstab with multiple levels and summaries. On-line analytical processing is usually performed on OLAP structured data rather than the transactional data common in our relational database tables.
If your report data don't have too many blank spaces, this is an excellent way to search your data. Crystal Reports 9 and 10 include an OLAP grid Analyzer. You can activate this with a right click on the OLAP object in the preview. The “Cube View” then appears. This opens a new tab inside Crystal reports that includes a high degree of Crystal analysis functionality.

So, what exacly is OLAP? The acronym stands for Online Analytical Processing. Most of us are used to relational databases that allow us to run queries and generate reports against a collection of two-dimensional (flat) tables (essentially spreadsheets) that are related in some manner (typically by a join). This is amazingly useful, but it's not ideal for computational operations or for the kinds of analysis that some people would really like to do but relational databases don't permit.

OLAP also goes beyond flat tables, but in a different way. What it does is arrange the data into cubes. But take care with this idea; an OLAP system may have more than the three dimensions you see in a cube. If you've heard of hypercubes, that's an OLAP programmer talking about more than three dimensions in an OLAP system.

An OLAP system, if correctly implemented, allows users to:

• Access information from multidimensional data warehouses (with greatly shortened computational times, vs. relational databases).

• Reduce erroneous interpretations of data.

• Specify and perform complex calculations in a more clean and straightforward manner than they could with a relational database system.

• View information per their own mental models of how they should view it.

We found a great explanation of OLAP, with excellent graphics, on Wikipedia. If you want to explore OLAP to understand how it works, we suggest you go there. This gives you an overview. It won't make you an expert in OLAP (and it's not intended to).

If you are thinking of dabbling in OLAP, we suggest you don't.

It would be better to buy a book on OLAP and take the time to understand the subject. It's a complex technology, and frankly many people are just not capable of understanding it. If you've worked with relational databases, you've already seen this issue play out. With OLAP, the hurdle is even higher. But if you master the technology, you'll be part of a "worker shortage" pool of talent. That could be very advantageous to you.

Once you understand OLAP, you can take advantage of the several commercial OLAP tools (and related products) now on the market. And if you're going to do that, then you should join the OLAP Council. Here's an excerpt from their site, and it helps explain why there's a demand for OLAP:

OLAP has evolved as users' needs for data analysis have grown. It provides executives, analysts and managers with valuable information via a " slice, dice and rotate" method of end user data access, augmenting or replacing the more complicated relational query. This slice and dice method gives the user consistently fast access to a wide variety of views of data organized by key selection criteria that match the real dimensions of the modern enterprise. OLAP performs multidimensional analysis of enterprise data including complex calculations, trend analysis and modeling. Derived from end-user requirements, OLAP enables end-users to perform ad hoc analysis of data in multiple dimensions, thereby giving them the insight and understanding they need for better decision making.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.