On May 14, I had the privilege of co-presenting with Andrew Brust to the NYC Chapter of TDWI and at the NY Tech Council BI and Analytics special interest group. Our topic was “I.T. Can Run, But I.T. Can’t Hide (from Microsoft Excel)”. We didn’t pick the title!
The slides are here. This posting is more-or-less the speaking notes for the slides. Andrew did three great demos; I will summarize them below.
Conventional wisdom holds that end-users in corporations adore Excel and IT departments hate it. We believe the situation is far from black-and-white. We see many reasons end-users love Excel. Most of these are obvious including the ease of building an appropriate and relevant solution, quickly and without interference. Obvious reasons IT departments worry about wide-spread use of Excel include fears about data quality, data staleness and security.
We found that users have their own fears about using Excel to serve their own needs independently of IT and its data sources. They definitely have to work harder to find the data they need for decision making. They have to build models and calculations, perhaps from scratch. And responsible employees worry about making mistakes and potentially losing data through email or file shares.
Counter to the conventional wisdom, IT benefits from users serving their own needs with Excel or other tools. In a world of do-more-with less and hyper-competitiveness among companies, IT is pained by being the bottleneck that holds back the creativity and innovation of employees.
The slides provide more detail. The thing that surprised Andrew and me is this; we expected everything that users love about using Excel to be the opposite of the things that IT hates about it. If you’ve ever built a 2×2 grid to enumerate the pros and cons of two paths, A and B, you’ve often seen the pros for A are the cons for B and vice-versa. In our case we were surprise, and delighted, to realize that end-users and IT share similar concerns about data quality, modeling accuracy and working together. This being the case, we found several strategies that companies can deploy to get the empowerment that users (& companies) want and the alignment the company requires.
A key strategy is enabling self-service, giving end-users access to quality data and a tool they enjoy using and expecting them to satisfy their own needs for analytics. An observation of ours is that when employees get home, that is out of the office, they largely serve their own information and information integration needs. The Facebook generation, and indeed anyone who is facile using the web, is used to working with different data sources and “mashing-up” their own, usually simple, solutions. We have email in Hotmail or Gmail, photos in Flickr, contacts in LinkedIn, friends in Facebook, etc. We track our money in Quicken or Mint online. We get our maps from Google or Bing. And mostly we don’t build apps as much as occasionally cut-and-paste.
Having end-users with Web 2.0 experience bodes well for implementing self-service at the office. But the flip side is the Facebook generation has ideas about sharing that can give IT pause. The first worry is about employees publishing company data or secrets in social media. That’s largely a cultural issue. Things my generation takes for granted are not second-nature for some younger generations.
Beyond the leakage problem, the other potential negative is a willingness of many people to ask their friends for information and to trust that data more than the data they get from “authorities”. Studies have found that 78 percent of people trust peer recommendations[1] while only 14 percent trust traditional advertising[2]. The scenario IT fears is this; Joe needs some data about the square footage of all of the stores in his district. He takes a stab at getting the data from SAP, but fails. So he sends a few IMs to people in his work-network and finds out that Bob has an old email from the Real Estate team that lists the square footages. So Joe copies that data into his spreadsheet and never looks back.
So the dilemma for IT is how to encourage self-service, but using corporate data. In his demos, Andrew started by showing how difficult it is for users to directly access corporate databases. They need to think about servers, tables and views, queries, etc. It’s a non-starter for most employees. They will generally go around IT if this is the only level of data support IT provides.
In his second demo, Andrew showed what I call the brick o’data approach. When we built SQL Server Reporting Services in the early 2000’s, we observed that one person’s report was another person’s data source. We invented (& patented) an idea called report-as-data-source. SQL Server 2008 R2 supports this, serving data inside reports as Open Data (http://www.odata.org/producers) feeds. But what about companies that have not yet adopted Excel 2010 (required for consuming OData) or SQL R2?
We found out that since 1997 users could import data from web pages straight into Excel. Excel will scrape data out of HTML tables. All you need is a URL. You can set a refresh period so you always have fresh or relatively fresh data. The brick o’data approach uses simple web apps to publish views of data in plain HTML tables. They are not meant for humans as much as for Excel web queries. You can find more information here: http://bit.ly/OldSchoolXL. The approach we recommend is to build a well-know site inside your firewall with as many links to views as you think are reasonable. Keeping this inside the firewall mitigates most security concerns, or at least allows you to address them the same way as any other data security challenges.
Finally Andrew demonstrated PowerPivot and Open Data working together to provide fresh, secure data and amazing BI functionality in Excel. Much has been written about PowerPivot, so I won’t repeat that here.
Finally, we discussed five strategies for companies to use to cope with end-user self-service AND data quality:
· Transparency & Attribution
· Self-service
· Sharing
· Publishing
· Model analytics
These range from mostly cultural (transparency) to mostly technical (model analytics). The slides have more details.
Overall, we found some optimism for companies trying to span the spectrum of empowerment to alignment. And we had fun building and delivering the presentation. Thanks to Jon Deutsch, President of the Tri-City TDWI chapter for putting the program together.
[1] July 2009 Nielsen Global Online Consumer Survey
[2] “Marketing to the Social Web,” Larry Weber, Wiley Publishing 2007