Connected Excel: Different perspectives

Connected Excel means different things to different people.

Most vendors when quizzed whether they connect with Excel would nod their heads vigorously in the affirmative. Sure, they connect with Excel. Have done it for years. Piece of cake. Easy-peasy.

But they could be meaning any of these things:
Type #1 Creating reports in Excel format: Most vendors provide an option in thier reporting application to dump the report in Excel. Once those reports are created, they are spat out on the users file system. Thats it. Done. The Excel file, then, is the usual frozen-in-time Excel file, disconnected with any application, incapable of being refreshed with fresh data.

A variant of this is when the reports are mailed out using some e-mail server to users mailboxes at specified time intervals. The

Type #2 Taking in Excel files as input: Most vendors also provide options for importing data from Excel files. Provided the excel file is in the format specified by them. Provided it lies in a specific directory. Provided it contains no other data. Not your everyday use Excel files, but files specifically created for data input purposes. You could have as well created a flat file.

Type #3 Talking to only "their own" Excel files: Some BI vendors give birth to Excel spreadsheets from their applications, with their VB code in it. This VB code acts like an umblical cord between these Born-to-BI spreadsheets and the mother BI applications. In these spreadsheets, you can refresh data from the mother app, sometimes even upload data to the mother - but these spreadsheets cannot be tampered with. They are special - and you need to take care to let them be pristine. You cannot play with them, cannot put other data into them, nothing.

Type #4 Providing web service interfaces for Excel: For Excel 2003+, vendors claim that they have web services interfaces that can be called using the web query feature of Excel. And vendors actually expect end-users, the average Joe, to make sense out of XML LOL!

There were no credible options to connect Excel with enterprise applications. Not after Extensio came in though!

There are some voices heard about Excel connectivity in SAP's and Microsoft's Duet (formerly Mendocino) , but as far as I could see in the current release, all they do at this time is provide the Type #1 type of connectivity - ie create Excel files - and with its e-mail variant. With the hype that is created and the promises I see in the Duet Colleteral, that was a bit of a let down, but I am hopeful. Surely, biggies such as SAP and Microsoft have more for this 400M strong user application!

- Sangeeta

Not only is Excel disconnected, it is risky too!

Using Excel is risky business. There are numerous examples of the risks shouldered by Excel usage documented at length at www.eusprig.org, a worldwide interest group focussed on this very issue.

One of the major reasons for the risks of Excel is the risks of having bad data. While this is a risk for any application (Remember the old principle of GIGO - Garbage in, Garbage out), this risk is very large in Excel, because Excel is largely populated by hand. Worse, there are no audit trails, as there are in enterprise applications. Once the data is entered in Excel, it just sits there and is emailed around.

No one knows who put the data in Excel, no one knows if someone changed it, no one knows if it is current.

A connected Excel can solve this problem in a big way. If Excel can be connected to data sources, and in a manner that does not need to user to learn complex SQL or XML, a single refresh can take away all the data risks for Excel. I presented a paper on this problem in the EuSpRIG conference last year. My co-author and I had suggested a mechanism of connecting Excel to back-end sources that can reduce the data risks of Excel to a large degree. We did build out most of what we had said. Check it out.

I heard that Microsoft is getting into the act and creating what is called as the "Excel Server" to mitigate some of the risks, and serve this very large user community. Not many details are out at this time though. It is slated for release sometime in the next few quarters.

- Sangeeta

Excel: The Data Island

Not that this needs to be said, but here it is anyway. Excel is one of the most popular tools to be used on a Microsoft desktop. If someone is using a desktop, and is working on it for more than three hours in a day, besides using the browser, Excel is perhaps what is being used.

Using this wonderful piece of software, people build their portfolios, their contact lists, their business model, their companys financial results, their production plan, their home accounts- the list is endless. The users puts the data that needs to be worked on into Excel and get cracking. This wonderful piece of software sits there, on the desktop, smug, completly oblivious to the fact that there is a teeming world of information out there. Excel with its pristine, neatly laid out rows and columns, gleaming white, waits for its users to populate it. The users love its scratch-pad like flexibility so much that they spend their time heavy lifting the data from multiple sources, or worse, typing it all in, never mind working the fingers to death. And all this in times when the information superhighway is zipping by, carrying loads and loads of information. Even in these times, if you are a Excel user, YOU are the one who is going to do the work for getting the data into it. No, siree, no other options!

Amazing, isnt it? I mean, if it had'nt been for the fact that we are "used" to Excel being this way, this is the first thing we would notice about Excel, right? That it is not connected to the "rest of the world". Its a data island.

And it has stayed that way. For years and years. At least till date. Barring users who can write complex SQL queries and know XML, getting data into Excel is as manual a process as it used to be, back in the information stone age. This, for a software that is HUGELY popular and perhaps contains about 25% of the worlds data!

Ok, ok. Maybe I exagerrate. Maybe there is 5% of the worlds data in Excel files. Maybe it is 30%. Who knows - there aren't any studies that tell you where people keep their data. But most of us know intuitively know that if a day dawned when the Excel application stops reading our XLS files, it would be a bad day indeed. Very bad. For some of us, disastrous.

So, how come we don't have Excel connected to the "rest of the world"? How come we can't download our data straight into Excel, whichever the source? How come it is still so painful to populate our Excel file? How come we love the fact that we have to only type a URL to get the data we need in a browser, and continue to be resigned to the painful Ctrl-C and Ctrl-V where Excel is concerned?

Makes one muse, does'nt it?