Excel PivotTables for Multi-Dimensional Reporting

• This tutorial focuses on multi-dimensional reporting with PivotTables using Microsoft Excel.
• The author's are Paul Hawking and Robert Jovanovic, with edits by Traci Hess.
• The tutorial version is 2.1.

Motivation for Using PivotTables

• PivotTables assist in making decisions related to ordering, promotions and managing store operations.
• Reports in the provided format do not provide the ability to make effective decisions.
• PivotTables help producing more useful reports due to it's ability to re-arrange data and summarise it.

PivotTable Definition

• PivotTables are data summarization tools found in data visualization programs like spreadsheets or business intelligence software.
• They are used for quick data manipulation and analysis.
• PivotTables are similar to crosstab queries.

Prerequisites and Hints

• There is no special hints or pre requisites.

Multidimensional Glossary

• Aggregate: Summarizes data based on a field (SUM, Maximum, Minimum, Average, etc.) and reduces the number of rows displayed.
• Dimension: Categories or non-numerical fields structuring data in a data warehouse for analysis.
• Drill-down: Progressively displays increasing levels of detail.
• Drill-Through: Exposes more columns of a report.
• Drill-up: Changes the view of the data to a higher level of aggregation with less detail.
• Fact/Key figure/Measure: Numeric value on which reports are based (sales $, # of units, output etc.).
• Filter: Limits the output of a report based on a value or range of values of fields within the report.
• Granularity: The level of detail displayed.
• Group: Combines records with identical values into a single record which creates aggregated data.
• Pivot Table: Data summarization tools in data visualization programs used for quick data manipulation and analysis, similar to crosstab queries.
• Slice and Dice: Navigating data from different business points of view (dimensions), achieved by filtering the data.
• Sort: Organizes data in ascending or descending order based on dimensions or facts/key figures without changing the number of rows displayed.

Multi-Dimensional Reporting with PivotTables

• The primary purpose of an information system is to process information to produce reports which facilitates decision making.
• Reports organize, analyze, present, and deliver information to end users.
• Report classification is based on the types of systems they are built from.
• Online Transaction Processing (OLTP) systems are optimized for transaction processing and accessed by many users.
• OLTP reports derive from business transactions and support tactical decision making.
• Online Analytical Processing (OLAP) is an alternative information system that allows users to analyze information by creating multidimensional reports.
• OLAP systems deal with large volumes of aggregated historical data and offers more flexibility than reports produced by an OLTP system.

OLTP vs OLAP

• OLTP reports give information about particular transactions which will answer questions such as:
• Who purchased a particular product?
• How much did an employee get paid?
• How many of a product was manufactured?
• OLAP reporting helps understanding why business events occurred and assists in forecasting using questions that assist with such understanding:
• What are the total sales for each product?
• What are the total sales for each department?
• Which salesperson has sold the most?
• Which products does each salesperson sell the most of?
• In which month did most of the sales occur?
• OLAP systems and multi-dimensional reporting are components of Business Intelligence.
• OLTP systems provide transactional data used as input for OLAP system’s multi-dimensional reports.

Working with PivotTables in Excel

• Access the Excel file "Tut_PivotTable.xlsx" from the tutorial to follow along tasks.
• Copy the "Sheet1" worksheet by using the Home | Cells: Format | Organize Sheets: Move or Copy Sheet option and ensuring the "Create a copy" box is checked.
• Rename the new sheet to "FilterSort" by double-clicking the sheet tab or using the Rename option.
• To filter the data, pick a worksheet (FilterSort) and a cell that includes data, then go to Insert | Table, the full range of data should be automatically selected (A1:H7449), and then click OK.
• Data will be formatted as a Table, where each column heading will include drop-down arrows, to sort and filter data.
• Data can be sorted alphabetically, numerically, or in aggregation.
• Click the drop-down arrow next to "Material" to display the Sorting dialog box, then click Sort A to Z to sort Material ascending.
• The filter function displays data that meets specified criteria, while the Sort function displays all data.
• Click the drop-down arrow next to a column to display the dialog box to apply a filter.
• Filters on numbers can be applied by clicking the drop-down arrow next to Quantity, then Number Filters.
• After applying a filter, the drop-down arrow changes to an icon indicating a filter is applied.
• Filters can be removed by clicking the assigned drop-down arrow or the Undo button.
• To create a PivotTable, select the Data tab (menu option in Excel) then select any cell in the table then click the Design tab.
• From the Design tab, click Summarize with PivotTable in the Tools Group and press ok.
• All column headings or fields of your table will appear in the PivotTable Design Area.
• You can design a new PivotTable by dragging the column headings that you want.
• Sales Organisation field can be selected to appear in the Row Labels design area, total Quantity then needs to be calculated for each Sales Organisation.
• The field appears on the worksheet and is automatically placed in the ∑ Values area.
• Each Material can also be seen by Sales Organisation via the Row Labels design area.
• Group data (i.e aggregated), by first selecting the Move Up command from the PivotTable Design Area, in the Rows area.
• Unchecking the Sales Organization field will show the simple totals by material.
• Reports need a time dimension to indicate the period when transactions happened, which can be remedied by adding the Month/Year dimension
• The Rows area of the PivotTable Design Area needs to be modified in order to see quantity sold by month and year.

Navigating Multidimensional Reports

• Drill-Up reduces the number of rows (i.e., detail), moving from a detailed view to a more summarized view.
• Drill-Down is the opposite of Drill-Up and displays more detailed data.
• Drilling can be actioned by clicking the relevant dimension.
• Time is a common dimension used for Drilling-Up and Drilling-Down, and the level of detail shown is referred to as granularity.

Slice and Dice

• This refers to navigation, viewing data from different business points of view (dimensions).

Creating a PivotTable to See Slice and Dice in Action

• Remove the current PivotTable by de-selecting the selected field in the design area.
• Select the Material, Distribution Channel, Sales Organisation, and Quantity fields.
• Display the context menu clicking the Material icon (or Row Labels) in the PivotTable.
• Click (Select All) to deselect the current selections then select Cruze Bike and click the OK button.
• The PivotTable is now Sliced to only display the results for the Cruze Bike Material, and you can further Slice the data to show only the Sales Quantity for a particular Distribution Channel (Wholesale) in a Sales organization (Sydney).

Fixing the layout of the table

• Within "Rows area (bottom right corner of the screen" then click on Material then select "Field Settings".
• Select the "Layout & Print" tab withing "Field Settings window" then uncheck the box "Display labels from the next field in the same column (compact form)" then uncheck the box "Display subtotals at the top of each group".
• Repeat the processes above with "Distribution Chanel" instead of Material.

PivotTable Filters

• Right-click on "WH" to display the context menu then select "Filter" then "Keep Only Selected Items"
• The Distribution and Material Channel will then be adjusted and indicate that "Filters have been applied".
• To remove the filters, click the filter icon within the design area and remove the filter.

Exercises

• The are three exercises to help you practice using PivotTables:
• Which material provided the most revenue in Sydney?
• What is the total sales revenue for Germany (there are three German cities)?
• What is the total wholesale quantity?

Sorting Tip

• PivotTables are sorted by Sales Organisation.
• To sort by quantity instead and display the Sales Organizations in order by increasing Sales Quantity:
• Click the arrow by Row Labels, then select "More Sort Options".
• In the Sort screen, select Ascending and then select "Sum of Quantity" from the drop down box.
• Click OK, and then PivotTable will be sorted by quantity.