How to Break Business Intelligence Users’ Excel Addiction
Recently, a reporter asked me to comment on how organizations can adjust their Business Intelligence (BI) efforts to users’ persistent “addiction” to Microsoft Excel and if I had any ideas for weaning users away from Excel in favor of more modern solutions?
I think there are two issues here. First, there are the users that should be using Excel because they are looking to do that deep dive analysis that requires them to slice and dice through the information. With the level of interaction and manipulation they want to do with the data, Excel is likely the most appropriate tool for them. The problem is that in many cases they are using it ineffectively. Often, in order to have access to all of the data they need to do their analysis they find themselves copying and pasting in information from various sources. They may download pieces of data from Access databases, move over columns from other reports, link to data in other spreadsheets, cut and paste from email, or manually enter various items. Additionally, if they are writing their own queries to various tables of information, they may be running into issues with data retrieval, bandwidth, or invalid returns. By the time they are done just sourcing the data they need, there usually isn’t much time left to do the real analysis needed.
Organizations can help better support these users by providing them with a centralized structure for all of that information. We have worked with many customers to utilize tools like Microsoft SQL Server to provide a centralized environment for the data which can then be presented to them in a more meaningful format. They can add speed to the analysis and take away the complexity as well by building out cubes for the key dimensions and measures the users need access to; providing them with accurate and accessible data which allows them to spend less time searching for answers. They can still use tools like Excel with Pivot Tables where they can now quickly select the fields they need for their analysis and start to drill into the data. This also allows for a centralized model for multiple groups, where each area or individual can still select a subset of that data for their own use.
There are other users, however, that have found themselves utilizing Excel due to the fact that they have no other solution to choose from. Or the data that is presented to them in reports or other solutions is more or less than what they need. These users then download the information they do have to Excel in order to massage, manipulate and augment that data rather than do analysis. Many of these users would be better served with dashboard or reporting solutions. The challenge here again is presenting these users with the data that they need and in a format they can consume. These users can benefit from the same strategy of a cube build that will then quickly provide them with the data they need in a dashboard, through a Pivot Table, or through more ad hoc or interactive reporting solutions such as PowerView. However, these can also be large, expensive and time consuming processes. We have had a lot of success with customers working with smaller iterations of projects such as these by doing initial pilots on a small area and by utilizing an Agile approach to building out an appropriate business intelligence solution. This provides faster deliverables that users can start to adopt right away, building excitement and momentum for continued use. It is also more cost effective and establishes a quicker ROI for senior management, providing further support for expansion to other areas.
One final thought is that in order for any of this to happen, the users need to have confidence in the data that is being presented to them. Many times when you start to move away from the manual processes they are accustomed to, there is a great deal of hesitation that the automation is pulling the right data. It is often appropriate to run both the old and new process in parallel for a period of time so that they can become comfortable with not only the new way of working but also that that information is accurate. This issue can be further mitigated with Data Quality solutions utilized to profile, validate and cleanse the data upfront. Having the users participate in creation of the validation rules for the data can also help alleviate this concern and there are some newer solutions, like SQL Server 2012’s Data Quality Services, that will provide views into the results of these activities for further validation of them.