Get started Bring yourself up to speed with our introductory content.

Is an Excel pivot table really business intelligence (BI)?

Although a pivot table is one component of Microsoft’s BI stack, it takes a lot more to create a full-fledged business intelligence solution.

To answer the question posed in the title -- no. But it plays a role.

Generically, a pivot table is a data summarization tool; you’ll find it in many spreadsheet applications (OpenOffice’s Calc has this feature, for example), as well as dedicated business intelligence (BI) tools. Microsoft Excel has its own pivot table feature, conveniently dubbed PivotTable. There is also a new enhancement called PowerPivot, part of the SQL Server 2008 R2 technology set, that is compatible with Excel 2010. Even Google Docs provides very basic pivot table functionality.

Essentially, a pivot table sorts, counts and totals the data stored in one database table or spreadsheet and creates a second table – the actual pivot table – that summarizes the data. The idea is that you can quickly change what’s being summarized by dragging and dropping.

It’s important to understand that a pivot table is just a data visualization and summarizing tool; it’s only as good as the data it can draw from. So while an Excel pivot table can be part of a BI solution, it is not BI all by itself.

Typically, users will use a pivot table to extract information from a BI solution’s data warehouse, enabling them to mine through data interactively and on their own through what’s called self-service BI. In fact, Microsoft’s business intelligence “solution stack” places Excel – and its pivot table feature – at the top of the solution as a kind of “thick client” for consuming the data in a BI system.

Still, regardless of how good a spreadsheet jockey you may be, a pivot table isn’t the be-all and end-all of BI. A good BI solution may very well expose data to Excel users, and users who are very comfortable working with Excel often appreciate that and can be very productive.

A major goal of business intelligence, however, is getting information into everyone’s hands, and training someone to use Excel’s pivot table feature can be mighty time consuming. That’s why most BI solutions also provide other forms of visualization, such as dashboards, reports and scorecards. In a pure-Microsoft BI solution, for example, these features are provided by SharePoint Server and SQL Server Reporting Services.

The best BI solutions will also provide interactive analytical capabilities, often through a Web-based interface. These might allow a user to start with a chart on sales performance, for example, and click the chart to see a breakdown of sales by region or product line. Users could continue clicking through to see ever-more detailed data. For instance, clicking on a product line might reveal the top sellers in that line, and clicking a product might show a breakdown of product costs and overhead. In this fashion, a user can interactively explore the relationships between data in the business intelligence system, drilling down to the root cause of whatever problem they’re researching.

So while Excel’s pivot table feature isn’t BI in and of itself, it can be a very useful component of a well-built business intelligence system.

Don Jones
is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at

Dig Deeper on SQL Server Business Intelligence Strategies