In this new series, Rob Collie, chief technology officer at software company Pivotstream, explains how you can do more with Excel through an exciting add on program from Microsoft: PowerPivot.
Accurate, actionable, and timely data underpins any well managed business workflow, but today’s ‘go to’ sources of business data are fundamentally the same as they were ten or 20 years ago. They fall into one of two primary areas: spreadsheets in the hands of the business units themselves, or reporting and analysis systems driven by IT.
Each approach offers clear benefits. Spreadsheets offer a level of flexibility and ‘time to results’ unmatched by any other approach. Formal BI systems can handle vast volumes of data, automatically refresh reports and analysis systems on an ongoing basis, maintain institutional control over intellectual property, and reduce human error greatly.
Yet they also have disadvantages. BI systems suffer from high start up costs and lead times and are seldom flexible enough to meet rapidly changing business environments. They are typically used to address only the most central and static five to 10% of the business’ need for information.
The remaining needs are met by spreadsheets. For one time reports and analyses, spreadsheets generally provide a very practical solution. However, a significant proportion of these documents are ongoing, and the long term costs of spreadsheets stand in sharp contrast to their start up costs.
Taken together, the shortcomings of each approach leave the business with a difficult choice: pay an impractically high cost now, or pay an impractically high cost later. Furthermore, there is no easy way to change once you have committed to one method or the other.
Spreadsheet agility, with BI
PowerPivot provides businesses with a hybrid option that is practical both in the short and long term. It is designed as ‘Excel plus’, making use of users’ existing skills.
Below is a PowerPivot workbook in Excel 2010, with a few features highlighted in orange:
Instant benefits
While PowerPivot greatly reduces the ongoing cost of spreadsheet based intelligence, experienced Excel users will notice benefits that enhance their normal Excel capabilities considerably:
- the ability to handle up to 100 million rows in a single workbook, while maintaining manageable file sizes and fast performance
- PivotTables and PivotCharts built over multiple tables of data, without requiring those tables to be VLOOKUP’d together into a single table
- the capability to quickly create mashups that cross reference and integrate data from multiple sources
- a new formula engine for Pivots that goes far beyond any formula capabilities available in Excel Pivots to date.
Read more on the Excel blog.
Ongoing advantages
Also available is a web application that offers the same interactive access to data. Anyone can access it from their browser, even if Excel is not installed on their computer.
With PowerPivot, all it takes to transform a PowerPivot workbook into a web application is a single action: ‘Save As’ to the server.
This web application is where many of the benefits associated with formal BI show up:
- consumers have interactive access but not the ability to download or modify the workbook, ensuring data security
- no concerns about consumers forking off the official workbook with their own customisations, or working from a stale copy on their desktop
- no need to manually refresh reports every day, as the server provides an automatic, scheduled refresh
- no one except an approved list of authors may modify the published workbook; and when they do, there is an audit trail.
Find out more
Look out for next month’s article, and in the meantime get started by downloading PowerPivot, reading an introductory blog post, or watching a demonstration and viewing the workbook used.
Link
CIMA professional development
CIMA on demand