Microsoft Excel Pivot Tables
Advantages and disadvantages of using MS EXCEL pivot tables:
Step by step instructions on how to create a simple Excel crosstab
- 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
- Go through the following options:
- 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
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.
And the sample Microsoft Excel crosstab generated from a Star-Schema Data Warehouse design looks like depicted below.
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.
- 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.