The Ongoing Battle Between Excel and BI

I would have thought that by now business intelligence tools and Microsoft Excel would be happily coexisting.  In some cases they are, but  a larger number of Excel users, managers, and BI professionals simply seem battle weary.

Last week at TDWI in Washington, DC, I taught an updated course on "BI & Excel: Friends or Foes?” I last taught this course four years ago. I learned my first lesson about BI and spreadsheets the hard way back in the early 1990s. At the time I was the project manager for a reporting system based on a custom transaction system. Typical of many IT projects, I gathered business-user requirements, went away for a couple months (at least it wasn’t a year!), and we developed some parameterized reports on the mainframe. The final solution was flexible, interactive, and exactly what the business users asked for. We launched the new reporting app in a training class I had personally developed and was thrilled to be teaching.

We were only half an hour into the class, when the power user in the group, Frank, declared, “I don’t want any of this. I just want all my data in Excel.” Frank was the statistician in the group -- the trusted analyst. Normally, he and I were on the same side. We would swap notes on how to tweak Lotus macros or how to extract data from mainframe sources. But he basically just told me I had wasted months of effort and that what my team had built was crap.

The business managers in the room liked the reports, but they relied on Frank for all things data. Frank had planted a seed of doubt in their minds that our whole approach was wrong. Frank had become my foe. I tried not to get teary-eyed (hey, I was in my 20s!) and decided to cancel the rest of the class.

Frank felt fixed reports constrained him. He needed flexibility. For the managers in the group, who were not yet proficient in spreadsheets, the mainframe-based reporting system might have been fine. But Frank could make any data looking prettier in Excel, with better formatting, colors, charts, and so on.

Even though that lesson about the role of spreadsheets in BI was learned 20 years ago, BI leaders, managers, and Excel gurus continue to grapple with BI and Excel’s coexistence.

At the start of last week's TDWI class, about a third of the attendees agreed with the suggestion that Excel and BI are friends; Excel helps improve decision-making and fulfills the vision for business intelligence.  “I'm on the friend side because  I know if I treat Excel as a foe, I will lose," said one attendee.  "It's easier to embrace your enemy and gain trust and creditability.”

The remaining two thirds felt that BI and Excel are foes: too many spreadmarts (some in the hundreds of megabytes) and multiple versions of the truth undermine the BI team’s efforts to provide a single version of the truth.  One attendee who started the day thinking spreadsheets were the enemy later said  the class had changed her view.

To fully appreciate Excel’s role in BI, start by understanding why users love their spreadsheets. Sometimes it may be personal and job security. Knowledge is power, and being the designated source of data is an enticing role.  Beyond job security, there are a number of valid reasons that Excel plays a powerful role in business intelligence, such as:

• Familiar user interface: Attendees acknowledged that Excel is the preferred interface for power users, but managers and front line workers prefer dashboards and easier- to- use tools (they were wowed by SAS’s Email integration).
• Ability to tweak a report, whether to sort, filter, pivot, or remove a column
• Extensive formula library
• Access to multiple data sources: Excel’s ability to combine data from multiple data sources is a must-have requirement for many types of analyses
• Ability to “massage” the data: few attendees said they use Excel for data cleansing, but several people spoke of changing group roll ups.
• Ideal prototyping environment where users themselves can build applications.

For any given requirement, assess whether Excel is the best solution or if the BI tool would be better. For example, newer version of BI software allow users to filter, sort, and interact with a report via a browser. Habits are sometimes hard to change, and if users don’t know about these capabilities, they will fall back to what they have done for years (click that Export button). 

The multiple-data-source issue is probably the biggest challenge. BI teams don’t like personal or departmental data to be accessed from a central business view and certainly not from within the data warehouse.  Excel is ideally suited for joining data from multiple data sources for a one-off analysis. The scalability improvements with the PowerPivot feature in Excel 2010 make it even more suitable. However, for recurring analyses that require joining data from disparate systems, multi-source becomes a problem for the entire  organization. The BI team has to recognize this or they risk being less relevant and  the cause of spreadsheet chaos. Don’t back business users into a corner.

Most BI platforms now offer an Excel add-in that supports data integrity while giving users their data in the familiar Excel interface. It surprized me, though, how few attendees were either aware of or using these add-ins.

Why aren't add-ins being used? In some cases, it’s performance and licensing problems. In other cases it seemed to be lack of awareness. There also seemed to be a degree of fear: BI teams shy away from Excel because they’ve been burned in the past.

My recommendation is to recognize Excel as part of the BI environment. Manage the spreadsheets, whether via SharePoint, a content management system, or the BI portal. Consider carefully when it’s appropriate to use Excel versus the BI tool; and in all cases, make available add-ins part of your BI tool portfolio.

By the end of the morning, attendees were more positive, with two thirds agreeing that Excel and BI can be friends when managed. The remaining third are still wary. Where do you stand? Have you successfully deployed these Add-Ins? I'd like to hear from you.

And if you followed my last blog about a few Washington, DC, mishaps, for this trip, the cherry blossoms were in full bloom. The view of the Jefferson Memorial, in 80 degree sunshine, was nothing short of spectacular ... and I didn't get kicked out of a single cab!

Regards,

Cindi Howson

 

 

 

 

 

Comments

[Ad Stam]

Excel and BI can coexist, stronger Excel is part of BI. You should never take Excel away from an analyst or even from a casual user who is used to get Excel based reports. What you can do is to make the main Excel user at the start of your efforts a part of the solution and not of the problem. However, most valued added information logistic processes including data warehouses and are so badly designed that it takes ‘forever’ to integrate data from multiple sources in both the initial projects and the ongoing battle to keep it up to date. In such cases and especially when you have not teamed up with the Excel guru’s you will always lose the battle. And by the way most Excel guru’s love to be freed from the burden to keep all kind of standard reports up to date. They love to “play” with data and if you provide them with a good data source they will love that also. But having said so, somewhere deep down I believe that when you have an Excel sheet that does not fit on one screen you should look for another tool……………

[Alan Whitehouse]

I believe that Excel and BI can definately co-exist. In regards to you comment on different data sources, I think the PowerPivot for Excel add-in does a fantastic job in that area.

[Anthony Sammartino]

I am of the friends point of view; Excel for superusers and a BI tool for executives who like pretty dashboards, scorecards, and reports.

Also, if your superusers are doing too much repetitive work with data using Excel (Powerpivot), the back end Data Warehouse, DB, ODS might need to be tweaked.

[Cindi Howson]

Thanks for the comments, everyone. Well, while I am on the "friends" and "co-exist" view, I don't think Excel should be used unilaterally as the BI solution for power users (to Anthony's point). I don't want to see too many calculations and business definitions housed in Excel, for example - they should be in the DW or central BI meta data layer. But when the BI tool is inflexible (like in joining dept data sources), then Excel is a great tool in the BI portfolio. I think one of the single biggest improvements Microsoft could offer to powerpivot is to make the business model for relational (the one used by Report Builder) accessible.

Regards,
Cindi

[MMorrisonDW]

It’s a fact of life: business users want Excel, IT wants BI. In the end, what they all want and need is better reports. The bigger issue is not does the technology work together, but do the combined offerings meet the needs of both the business user and the IT department?

Cindi’s story is a great example of what happens all the time at mid-sized and large enterprises: IT gathers user requirements, goes “away for a couple of months” and comes back with functional reports. The biggest flaw in that is the process doesn’t reflect the changing needs of the users.

After all, what business is exactly the same as it was months later? Chances are something changed – the product portfolio, geographic rollups, overhead allocations. How great is the likelihood the user still needs that exact information with those same parameters?

BI and Excel can co-exist together, but neither fulfills the dynamic report requirements of most businesses today.

Business users are happier (and IT is as well) when they find a report solution that feels and acts like Excel. One that offers Excel-like functionality, extensive formula libraries, access to multiple data sources and the ability to massage the data.

It’s not a question of BI or Excel, or BI and Excel, it’s a question of what else maximizes my investment in my existing reports while offering me the functionality of Excel.

-- Michael Morrison, president & CEO, Datawatch

[Steve Adler]

I agree with the previous comment, this is almost never an "either/or" answer but an "and" answer. Excel is so widely used with so much free help that it is easy for users to quickly find answers to their questions or problems. In general, there are also Power Users that can help in many organizations.

With much BI, users need to go through an overworked IT resource or a trainer for help and the documentation is frequently lacking. Any delay for a time-pressed business users can be met with real frustration. Additionally, the error messages in BI solutions are frequently obscure to business users and add to the sense that whoever implemented or built the solution didn't fully understand the business issues.

One clear need for BI providers is to better understand the average business user and focus on the "chrome" of the applications not just the "under the hood" things that users don't fully get.

[Zeiss rifle scopes]

The previous comment hit the nail on the head. Excel is so common that anyone in business HAS to use it. If they need help with an Excel problem, they can get it for free by simply going online. Its fast and free to find answers and solutions online due to the experts and communities out there willing to help. In this sense, BI takes a backseat to Excel.

Matt Cabent
Scout-32

[Yoav]

Hi Cindi,

Thanks for this great review.
My opinion is that for sure Excel is part of the BI systems, but as such, the gaps between a local, unmonitored tool should be reduced systematically.
I recommend several things:
1. Implement Excel add-in that almost every BI major platform has to offer (Liveoffice, PowerPivot, Cognos 8 Go! & ctr.).
New users should start using them as soon as they start working.
2. Implement software that allows you to monitor excel activity, trace data holes.
3. Meet the users & understand theirs working processes : from my experience many of them are not well trained in excel, some data they can analyze in the BI tool, others work very hard on a small piece of data that they compare using vlookup to other spreadsheets, combing data in the BI tool can easily solve it & reduce effort & time.
4. Analyze the working processes that can be automated.
5. The big statistics guys and the major annalists should get a "special" treatment, they should be the first one to get & be trained with the BI tools and use if needed massive row analysis tools like SAS.
6. Use automatic loading tools that can transfer the data online to the Data base & can be afterwards easily analyzed in the BI tool.
Thanks

Yoav

[Martin]

As far as add-ins go I would recommend XLhub, an Excel add-in created by Metric-X to enabling Business Intelligence even based on data that is locked up in Excel. XLhub allows users to push and pull information to and from a database where it then can become a data source for SSRS, Crystal Reports, or another BI tool of your choice. Of course, you can also pull that data into an Excel file and do your BI that way, too. Users can pull data from multiple sources (some Excel, some data warehouse, etc.) allowing for up to the minute information that fuels their forecasting, budgeting, and status reports. Another benefit is that users no longer have to send emails back and forth to update their spreadsheets; any user can simply access the updated information right from Excel and make necessary changes and publish them for other users to see.
To see more information go to the XLhub Main Page. A free trial is now available to start using, you can download it here.

[Martin]

The link for Metric-X: http://www.metricx.com

The link for XLhub: http://www.metricx.com/xlhub-excel-integration

For the free trial:
http://www.metricx.com/xlhub-excel-integration/demo

[methusael]

"My recommendation is to recognize Excel as part of the BI environment. " - exactly! i use to create reports from thousands of data pulled from BO, and export it to excel where i can better have a complete overview of the enormous data at hand. I can't imagine doing analysis without excel. They are both complimentary and not competitors.

methusael cebrian