Decision making can become an easier, quicker and more informed process when your data can readily be analysed in a variety of different ways. Online Analytical Processing (OLAP) is a technology that is used to organise large business databases and support business intelligence. The data is typically housed in an OLAP enabled data warehouse which allows for pivot table reports to be created in Microsoft Excel. The data is ‘cubed’ which means that the various data ‘dimensions’ and ‘values’ can be easily altered and re-sorted to provide a different slant on the same data. This is often referred to as ‘slicing and dicing.’

Assuming that your data warehouse has been set-up, the pivot table functionality in Excel can easily be used to interrogate your data in so many different ways. Of course there are numerous examples of this but key is to establish which ‘dimensions’ and ‘values’ are potentially important to you at the outset, that way your data warehouse can be configured correctly. Using the example of sales you should consider questions like;
- What products do I sell ?
- How are these categorised ?
- Do I sell across different regions ?
- Do I sell in different ways e.g. online and retail ?
- Are different salespeople in the equation ?
These are all relatively high level but if your data warehouse is also capturing lower level data such as sub-categories of product, special offers, VAT rates, delivery methods, post codes etc these will all be tracked and can be incorporated into your enqiries. In many ways enquiry is a better term than report because you may start by looking at your sales by region but then ‘on the fly’ dive down further and establish that a particular product sub-category was only successful when it was run as a special offer. This may prompt a pricing discussion at then next sales meeting.
Our example illustrates a Revenue by Location enquiry. We have ‘dimensions’ of Company Name, Customer Name, Customer No, Country or Region, Salesperson Name and State. To start with we have ‘values’ of Revenue and Net Profit, although in the example we will add the Cost of Sales value to illustrate the ease with which new data can be incorporated.

 
So, the enquiry above shows all dimensions selected with values showing at the country or region level. We then decide to drill down on the Canada figures and as you can see below the various states and cities appear where relevant.

 
The ‘BC’ value is of further interest to us so we drill in further to see the values against each of our individual customers in Vancouver in this case.

 
Hopefully you will see that the selections we have made are merely an example and that the data structure would allow us to ‘slice and dice’ the information in so many different ways.
The Pivot table field list within Excel can also be used to alter dimensions and values that we are enquiring on. This is achieved by simply dragging and dropping the items shown in the Report Filter, Row Labels, Column Labels and Values areas.

 
The Pivot table field list also allows for other data to be included. As we said earlier we can then easily decide to include cost of sale values to provide further information and this is shown below.

Hopefully this article will have illustrated the potential power of pivot tables. Admittedly a fair bit of preparation is required to get these pivot tables up and running, however the data interrogation power and flexibility that you will then have makes this initial effort worthwhile. Remember these enquiries can make your decision making both quicker and better informed and this has to be a good thing.