Is an Excel pivot table really business intelligence (BI)?
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
This was first published in June 2010
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.
ABOUT THE AUTHOR
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
www.ConcentratedTech.com.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation