The Strength of on Line Analytical Programming Lies in the Fact That It Enables Us to Look

Pivot Tables

The strength of On Line Analytical Programming lies in the fact that it enables us to look at and summarise data in different ways. This is known as “slicing and dicing”, for example we can look at different time periods, measures, geographic regions etc.

A PivotTable report in Excel is an interactive table that you can use to quickly summarize large amounts of data. You can use a PivotTable report when you want to compare related totals, especially when you have a long list of figures to summarize and you want to compare several facts about each figure. Because a PivotTable report is interactive, you or other users can change the view of the data to see more details or calculate different summaries.

PivotTable reports can be displayed in different formats. You can create a PivotChart report to view the data graphically. You can also make a PivotTable report available on the Web by using a PivotTable list on a Web page. When you publish an Excel PivotTable report to a PivotTable list, others can view and interact with the data from within their Web browsers.

Fields and items

A PivotTable report contains fields, each of which corresponds to a column (or OLAP dimension) in the source data and summarizes multiple rows of information from the source data. Fields in a PivotTable report list items of data across rows or down columns. The cells where the rows and columns intersect show summarized data for the items at the top of the column and the left side of the row. The fields in the first (customer) example are as follows

Summary functions To summarize the data field values, PivotTable reports use summary functions, such as Sum, Count, or Average. These functions also provide subtotals and grand totals automatically, where you choose to show them

Pivot Tables: Analysing Data on Customers

1. Open the customer file containing demographic and income-related data on customers and details of their transactions.
2. Highlight all the data and insert a pivot table.
3. Fill the pivot table field list by dragging headings into the desired boxes.

• Row labels : fields here will be on the LHS in the order they are dragged, and this affects the order in which things are summarised.
• Column labels are used if you want to break down the figures differently.
• Fields in the values area will be summarised mathematically- you can change this if you want to change the way the values are summarised (e.g. average instead of total).

Play round with different layouts of the pivot table to see what kind of information you can get.

Try to find out, for example:-

1. Are larger household more likely to rent or own?
2. Do larger households spend more and is this different in different regions?
3. Do richer people have more purchases?
4. Are rich households larger?
5. How do the above vary according to region?

Use the file customer2 to find out:-

1. How many customers are there in each sector?
2. How many customers in each state in each sector?
3. Average number of employees in each sector in each state.
4. Total number of employees in each sector in each state.

Production Line Problems

Copy over production.xls from the S drive.

batch : identifies each batch or group of products produced.

product : a unique number that identifies each product

machine : a unique number identifying the machine on which products are produced.

employee : a unique number that identifies each employee using products

batch size: the number of products in a given batch

num defective: the number of defective products in a give batch.

1.Highlight the following using conditional formatting

a)all batches made by employee 1111

b)All batches with >10 defective products.

c)All batches with size>1000

d)All batches for product 10

2. Use pivot tables to show:

a)The number of machines used to produce each product.

b)The number of defective products produced by employee by product

c)The total number of products produced by each employee

d)Number of products and number of defective products produced by each employee.

3. There is an unacceptable number of defective products produced. Use autofilter, conditional formatting and pivot tables to identify the source of the problem. Maybe analyse by employee, by product, by machine or by batch size. Use any of the above analyses if you think they’re useful.

Based on your analysis, recommend how to solve the problem.