Microsoft Excel Pivot Tables

Advantages and disadvantages of using MS EXCEL pivot tables:

the easiest way to learn principles of multidimensional data analysis and how an OLAP cube looks like

easy to generate and ditribute within organization, can be used offline

the data is stored in MS Excel internal format (in an .xls file) and it’s not accessible from other applications and is hard to browse.

when a crosstab is created, the only way to see (and change) its data source and connection string is from VBA code.

Step by step instructions on how to create a simple Excel crosstab

In MS Excel go to Tools -> Pivot Table and Pivot Chart Report...

Step 1.Choose data type:
- MS Excel List or database
- External data source (this option will be used in the tutorial)
- Multiple consolidation ranges
- Another Pivot Table or PivotChart report

MS Excel crosstab wizard:

Step2. Get data...

a. choose data source (any ODBC source)
b. choose columns
c. filter data (WHERE statements)
d. choose a sort order
e. finish – where the data should be returned to:
- Return Data to Microsoft Excel
- View Data or Edit Query in Microsoft Query
- Create an OLAP cube from this query
Save query option

Step3. Where do you want to put the PivotTable report?
Choose desired options in ‘Layout...’ and ‘Options...’ buttons.

Click Finish and you can start using the newly generated crosstab by dragging and dropping columns to the crosstab area.

MS Excel crosstab generation options:

And the sample Microsoft Excel crosstab generated from a Star-Schema Data Warehouse design looks like depicted below.

Excel crosstab generated from a fact table

Excel crosstab generated with a 'create OLAP Cube from this query'

When this option is selected, the data will be stored externally in a file and the funcionality of a crosstab would be extended. That type of excel pivot table is called an excel cube or pivot table cube.
An excel cube is created by selecting 'create OLAP Cube from this query' in the crosstab generation dialog window. Selecting the option will open an OLAP Cube Wizard which has three steps.

Step 1. Select the source fields you want to make available as summarized data fields

Excel cube summarized fields:

Step 2. Drag source fields to the Dimensions box to define dimensions. Please note that the dimensions can be selected in multiple levels (as the time dimension in the example)

Excel cube dimensions window:

Step 3. What kind of cube do you want to create?
- Rebuild the cube every time the report is opened, and retrieve the data for the cube only when needed.
- Rebuild the cube every time the report is opened, and retrieve all data for the cube at once.
- Save a cube file containing all data for the cube. - we will use that option in the example. This will create a .cub file with the data and .oqy file with the OLAP cube definition

The cube pivot table is shown below. It looks very similiar to a normal excel pivot table at a first glance but there are a few differences between excel cube and crosstab:
- The greatest advantage of an excel cube over a simple crosstab is that the data is stored externally in a .cub file and the connection parameters in a .ogy file. It makes the solution more flexible and enables users to share the data across multiple spreadsheets - for example by placing the .cub file on a share drive.
- The 'PivotTable Field List' window shows the dimensions hierarchy and the columns are distinguished between facts and dimensions by different icons on the left beside the labels
- Users can select multiple values one by one in the crosstab area dimension fields (both rows and columns)
- Hierarchy is implemented in the crosstab area dimension fields (both in rows and columns)
- Some VBA functions and methods apply only to cubes and other apply only to simple pivot tables.

Excel cube generated from a star schema

For more detailed information on Excel BI and OLAP solutions please have a look at the following Excel BI tutorials