Business Intelligence - Data warehousing - ETL
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
MS Excel crosstab wizard:
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 with a 'create OLAP Cube from this query'
Excel cube summarized fields:
Excel cube dimensions window:
- 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.
All Rights Reserved