Comparing OLAP Cubes Pivot Tables to Excel Data

Comparing OLAP Cubes' Pivot Tables to Excel Data

You've seen the basic idea of what an OLAP cube is. You've also seen that connecting to a cube is fairly simple, and that using one is very much like using pivot tables to work with raw data in an Excel worksheet. So what are the differences between using pivot tables on Excel-stored data and using pivot tables on OLAP data?

OLAP Handles More Data, Faster

The biggest difference is in the amount of data an OLAP cube can handle. As mentioned earlier, in a typical data warehouse there will be millions and more likely billions of records. Organizing aggregations of all that data into a usable structure is beyond even the amazing capabilities of Excel.

Now, not only can you access mountains of data, you can also sift through that data in the blink of an eye. There is no processing time to generate any of the aggregations because they have all been preprocessed for you. As you slice the cube from any point of view of interest to you, the preaggregated results appear nearly instantaneously.

Dimensions or Measures

In an OLAP cube, each field is either marked as a "measure" or a "dimension." Measures are generally the numeric fields such as Revenue, Cost, and Quantity. Unlike using Excel data, you can only drop measures in the data area. You cannot drop a measure in the row, column, or page area.

Similarly, the remaining dimension fields cannot be dropped in the data area. This is different from using regular Excel data, where it would be possible to drop the Region field in the data area (for example, to get a count of records).

OLAP Measures Are Already Grouped

It is likely that your DBA has defined groups in the data cube. For example, a year dimension may already contain quarters, months, and days. Simply double-click on the year to expand the group to show quarters.

This is a great benefit of OLAP data. It is possible to set up similar groups in regular Excel data, but this requires a fair amount of steps, as described in the section "Grouping Pivot Fields" in Chapter 5, "Controlling the Way You View Your Pivot Data."

If your DBA did not set up groups, it is possible to add groups to OLAP pivot tables using the techniques described in Chapter 5.

Drill-Through of OLAP Data

With regular Excel data, you can double-click any number in the pivot table to see all the records used to calculate that number.

The double-click method does not natively work with OLAP datasets. For Excel 2002 or newer, you can drill through by using an Excel add-in provided by Microsoft. It's called the Excel 2002/2003 add-in for SQL Server Analysis Services and is located here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

Other Excel-oriented OLAP tools offer add-ins of their own.

CAUTION

You cannot drill through a local cube. With OLAP, the drill-through capability requires a server to query the original transactional data. A local cube is the resultant data file, without the server capable of conducting the back-end query.

Calculated Fields with OLAP

As you've seen in previous chapters, you can have calculated cells and members. You can do this locally, or you can ask your DBA to make those kinds of changes in the cube as needed. Calculations do not work in local cubes.

Other Pivot Table Features Operate the Same

Any pivot table feature not described thus far will work with OLAP cubes just like it would work with local data. Specifically, you can easily format a table using the AutoFormat feature described in Chapter 4, "Formatting Your Pivot Table Report." You can create pivot charts as described in Chapter 7, "Creating and Using Pivot Charts." You can use AutoSort to sequence the pivot table as described in Chapter 5. And, you can display the top or bottom records using the Top 10 AutoShow feature, also described in Chapter 5.