Many of our clients have sophisticated tools and expertise in-house in order to analyze and report on data. Therefore, these clients engage us so that we can help integrate their data from separate data sources in order to develop a single customer view. Once they have a customer data integration application in place, they can analyze and report on their separate data sources using their single view of the customer.
However, we are also seeing more and more clients who are not only asking to implement a customer data integration system and develop a single customer view, but are also looking to us to help analyze and report on their data integration. Sometimes, these clients do not have the internal personnel and/or tools to analyze and report on data. Other times, they may have the tools, but lack the expertise on how to use those tools. Further, there are times when the client does have the internal expertise and the tools, but those resources are not available. Therefore, I thought it would be a good time to revisit the subject of Excel Pivot Tables. We have been using Excel Pivot Tables to assist clients with their analysis and reporting when they do not have internal expertise available.
I spoke with Steve LoCastro from BDS to get a sense of what he likes about Excel Pivot Tables and what are some of the limitations.
Steve stated that Excel Pivot Tables are basically a front-end interface that enables you to build a query in a similar fashion as Access in order to develop a report.
The things that Steve likes about Excel Pivot Tables are that they are:
- Simple to use
- Easy to set-up
- Easy to change
However, Steve also pointed out some of the limitations of Excel Pivots Tables.
First, if you want to report on any information that needs to be derived, this needs to be developed outside of the Pivot Tables. For example, if you want to report on how old someone is and your data provides the date of birth, but not the actual age, this would have to be derived outside of the Pivot Tables. Therefore, we typically need to write an application in order to build a data framework in order to derive certain pieces of data that need to be analyzed before using the Excel Pivot Tables.
Another major limitation is that the Excel Pivot Tables are best suited for looking at one query or question at a time. If you want to know how much revenue that you have, that may be one query and one table. Then, if you want to look at how many sales by representative, by region, by product line, or other variables, the Excel Pivot Tables are best suited to look at each one of these variables separately. Other more sophisticated tools may give your end-user the option to take a look at the general question regarding your revenue and then keep drilling down further to look at different slices of revenue. Of course, these other sophisticated tools are more expensive, require more expertise to set-up, and require more time to develop before the end-user can start drilling down on these variables.
Comments