According to a recent survey, many users are still using Microsoft Excel spreadsheets to analyze and report on corporate data. These same users also spend much of their data-gathering time correcting out-of-date information in these Excel spreadsheets. So what are the real benefits of using Excel spreadsheets to analyze and report data? What additional benefits do business intelligence (BI) tools offer?
In this podcast, SearchCIO.com interviews data warehouse consultant Rick Sherman on the differences between BI tools and Microsoft Excel spreadsheets and how they can be used together in the enterprise to gather and analyze data.
BIOGRAPHY: Sherman, founder of Athena IT Solutions Inc., has more than 20 years' data warehousing and decision support systems experience. His work designing and implementing relational database for decision support systems began with Oracle in 1982. Sherman has worked on more than 50 data warehouse and data mart implementations across many industry groups, sourcing data from a variety of business applications. He has been an expert instructor and speaker at numerous data warehousing conferences and seminars.
Read the full transcript from this video below:
Karen: Hello my name is Karen Guillomo, the Special Projects Editor for SearchCIO.com. And I'd like to welcome you to today's expert podcast on BI tools vs Excel spreadsheets. I'd like to first welcome today's speaker, Rick Sherman, founder of Athena IT solutions. Rick has more than 20 years of data warehousing and decision support systems experience. His work designing and implementing relational database for decision support systems began with Oracle in 1982. He has worked on more than 50 data warehouse and data mart implementations across many industry groups, sourcing data from a variety of business applications.
Rick has been an expert instructor and speaker at numerous data warehousing conferences and seminars. In addition to teaching at industry conferences, he offers on sight data warehouse, business intelligence training, and teaches public courses in the Boston area. He also teaches data warehousing at North Eastern University's graduate school of engineering. Welcome, Rick!
Rick: Hello Karen, and thanks for inviting me to talk today.
Karen: Great! And as I mentioned earlier, we're here today to talk about BI tools versus Excel spreadsheets. I'll spend the next ten plus minutes asking Rick to answer a number of questions about today's topic. So let's get started. First question I have for you is, I know a few years back you wrote a column for search CIO about how Microsoft Excel was still the king of all BI tools, and users still prefer using Excel over fancier BI tools. Would you say that's happening today? Is Excel still king?
Rick: The simple answer is yes. Despite the fact that the BI market has been thriving and spreading across most Fortune 1,000 companies, Excel is still sort of the tool of choice by most business users. I co-authored a report where we did a study last year on this phenomenon. And in the study we found meeting of 30 spread marts or data shadow system which we'll talk later on about, in most companies today. So it's a very pervasive issue. If you're IT you might consider it a problem and then I guess that's what we're going to talk about.
Karen: Okay so what about errors? Aren't Excel spreadsheets more prone to errors and therefore more production time spent correcting out some of this data?
Rick: Well certainly errors, and there's been a number of studies in this area. Certainly errors can be prevalent in spreadsheets. Certainly most business people create their macros, do a quick check, but there can be a lot of hidden errors as opposed to a BI system where the IT group goes through a much more rigorous testing of a lot of different scenarios and data. So the answers are much better. In most cases there is an issue with errors in Excel.
Karen: Okay and what about compliance. Which would you say is safer when it comes to adhering some of the compliance regulations like SOCK. Is it it the BI tools or the Excel spreadsheets?
Rick: The simple answer, of course, is that the BI tools are going to be more in compliance with SOCK. But I'd like to sort of break that down a little bit. First off, a lot of what happens with spreadsheet is really the gathering of data. Date integration, pulling data from various sources, bringing them into a spreadsheet and manipulating the data. That area, that data integration is certainly an area that doesn't comply with SOCK and needs to be addressed.
The second area is really the reporting aspects of the BI tool. Is it better to have the BI tool versus the Excel spreadsheet? Now certainly, again we mentioned earlier the BI tools are generally tested, they're documented and so that gets them in compliance with SOCK and other industry regulations. But if an Excel spreadsheet was documented, if it was managed just like a BI report there's no reason why an Excel spreadsheet also couldn't be SOCK compliant. And the last thing to sort of note is, a lot of times Excel spreadsheets are sort of used as the final formatting and massaging of the reports. So the BI tool might do the heavy data transformation of the things that you do need to be in compliance with. But the last little bit of which column is where, how to subtotal, that kind of thing doesn't necessarily need to be documented in a SOCK compliant environment and again, Excel could be used in that particular instance.
Karen: Okay. So could you tell us now a little bit about data shadow systems, spread marts or runaway spreadsheets and how they do work in the enterprise?
Rick: All right, great. Sort of one of- first off often times people look at this simply
as a spreadsheet issue. Runaway spreadsheets or whatever. Business issues out of control, using all
of these spreadsheets to do these things that BI tools need to do. But over the course of the last
decade or so I've been involved in a lot of different companies and talked a lot of folks. Either
through training or the conferences or whatever. And what emerged from my consulting work and my
working in the industry has been that it isn't simply an issue of spreadsheets.
It's really the creation of spread marks and I use a term called data shadow systems. Because really isn't not just an issue of a couple of spreadsheets. That's often times how these things get started. A business user, a financial analyst get asked to do a report or needs to look at some data. They put it into a spreadsheet, but soon what happens sort of this data shadow system. What that usually is, is a business user is going to pull data from a number of sources.
They might use a Microsoft Access if they're a little more proficient statistically they might be using SAS. And the might also use the Excel spreadsheet. They're going to pull in that data from multiple sources. They're going to then transform the data and then finally have it in the spreadsheet to do the formatting, the macros or whatever they need to do to actually get the final numbers out. But the data shadow system isn't just a set of spreadsheets, it's often dozens or sometimes hundreds of access databases and spreadsheets where people are sort of building their own little, if you're in the data warehouse world, the term is data mart. Or Olap Que, analytical processing que. Sort of doing that in a spreadsheet.
And rather than using the data integration tool and the BI tool, they're using the office tools, the stuff that they have on hand. Access, Microsoft Access, Microsoft Excel, again maybe SAS or or some other tool that's sometimes used. And they're pulling data together and they're getting it into this data shadow system or spread mart. No business user starts off with the idea that they're going to build up a system with hundreds of access databases to do reporting. But it's a pretty common scenario. When we did our study, those 30 median number of spread marts, data shadow systems, often had hundreds of access databases or spreadsheets. And certainly in the last few years I've had clients in insurance, health care, retail, consumer goods and other industries that have massive amounts of these spread marts, data shadow systems to do budgeting and planning, to do national account analysis and to do hundreds of other things that business peoples do on a daily basis.
Karen: So finally, can enterprises benefit from using both BI tools and spreadsheets together?
Rick: I think what would happen- what we looked at in the study when I come in to a client, or talk to people in my training classes. A lot of times the first reaction of IT groups is Excel spreadsheets are bad. You mentioned earlier the fact that there are errors. I mentioned often times that they're doing with a lot of data gathering, data transformation of data which is a big cause for problems.
So initially there is a backlash against Excel. But the better approach is to really separate out the data integration from the business intelligence tools from the actual formatting at the end. To really, the most important thing is to go after and take the business users, take the business people, out of spending as Forester estimated, up to a quarter of their time just gathering data into these data shadow systems.
So get them out of the business, have IT go off and get that data, put it into some databases or put it into some formats that the BI tools or Excel spreadsheets can use. And then to actually, when appropriate, use the BI tools to create dashboards, to create exception reports kinds of things a lot of business users would like and at their disposal. And finally, to, when appropriate have the business user actually use Excel.
The reality is, no matter how many IT folks you have in your company, you're not likely to have enough resources or time to meet every business user's reporting or analytical requirements. So you're going to have to use Excel as a stop gap or as part of the tool kit that a business person's going to have. In addition, BI vendors at first just like the IT people that I mentioned, were initially sort of seeing Excel as a competitor.
Nowadays most of the BI tools work pretty effectively, seamlessly almost with Excel. So that they can pass off the data into Excel or Excel can sometimes use the BI tool in the background and not even know they're using the BI tool in order to get the data. So the answer is that it's a very effective approach to use Excel with the BI tools. There are some business users, of course, who don't have the time or inclination or the need to really learn the BI tool. So for them Excel is a better choice.
And there's also a lot of needs that the business users have, business people have that IT just can't get to. So in that case again, Excel is a good answer. The main sort of take away from this. The two take away's, I think from this are one that you want to get the business users out of data integration. That's the area where SOCK compliance and errors can happen is when they, who aren't professionals on the IT side, are gathering the data into hundreds of Excel spreadsheets. I've seen these things laid out. There's no documentation, there's no real work flow. It's catch as catch can. You start off with a couple of these spreadsheets or access databases then the next thing you know it's dozens or hundreds. And that's where you have data integrity problems and data consistency problems.
So you want to attack that problem, that's the first take away. And the second take away is that on the BI side, sometimes business users can use those tools, sometimes Excel is the most appropriate need. But a blending of those tools, I think, is a very effective approach both from business user preference, from IT availability and also from a cost perspective. Which, as we go into 2009 that's probably a consideration we all have.
Karen: Okay. And on that note, that does conclude today's podcast. Thanks again to Rick Sherman for speaking with us today. Thank you all for listening, and have a wonderful day.
This was first published in January 2009