Cloud Data Like SalesForce Available to PowerPivot as if it Were in a Local Database:
My Long Wish for a “Data Highway” Gets Closer Every Day
(Click for the Webinar Featuring Yours Truly on YouTube)
Flashback 2001: The “Data Highway” Concept
Back at Microsoft in 2001 when I was working on what eventually became Excel 2003, I pitched a vision that I called “Data Highway.” (OK, not an original name considering the Information Superhighway thing coined by Internet inventor Al Gore, but invention is smart and theft is genius, or something like that.)
The idea behind Data Highway was simple: all relevant data made available to the most popular tools (cough cough Excel), in a convenient and refreshable format. No manual gruntwork required to “fetch” data in other words – saving your brain for actual thinking.
- A common internet protocol for exchanging data.
- “Teaching” Excel, Access, and other tools to consume any data source exposed via that protocol.
- A marketplace for data where providers like Dun and Bradstreet could sell data to be piped straight into Excel.
Well the protocol flopped and our VP killed the marketplace idea before it got off the ground. Having good ideas isn’t enough – you can’t be too early, and you also need to execute better than we did.
Fast Forward to Today
Here we are at the end of 2012, and we have all three elements available in different (but robust and real) forms:
- OData is very much the spirit of what I had in mind for a protocol back in 2001. All kinds of sources are now exposed via OData, including SharePoint, Netflix, eBay, and various government data feeds (see list here).
- PowerPivot (and therefore Excel) is a first-rate consumer of OData sources – it devotes quite a bit of precious ribbon real estate to it in fact:
All Three of These Buttons Connect via OData
- Azure DataMarket is precisely the sort of thing I thought we needed back in 2001, but this time the idea wasn’t killed, its timing was much better, AND it was executed properly.
O.P.D. – the Importance of Other People’s Data
Longtime readers of this blog have seen me rave about DataMarket more than once. Things like this are the future in my opinion. In my post “The Truth is Out There” I talked about all of the silos of information in the world, and all of the secrets they would give up if we merely could analyze them simultaneously.
The humorous example of this is what I found when I imported UFO Sightings data and Drug Usage data into a single PowerPivot model. No, I am not making this up:
Earlier this year, imagine my enthusiasm when I attended TEDxCLE and one of the talks featured a young scientist talking about data sharing as the future of discovery:
But What About Your Cloud Data?
Here’s the trick: other people’s data (like that available in DataMarket, or on the hard drives of scientists on the other side of the world) is naturally siloed. It is, after all, in the hands of others to begin with.
But the rise of cloud services such as SalesForce.com introduces another problem: your OWN data is now stored someplace you can’t get to!
As someone who operates a cloud service for a living, I am NOT saying that cloud services are bad. They are incredibly cost-effective and provide great value – the investments Pivotstream makes in our Cloud PowerPivot service, for instance, are shared across hundreds of organizations who otherwise would have had to make those same investments themselves (hundreds of times over).
You can’t beat the price or value of a good cloud service, so Excel Pros, listen up: we’re going to be seeing a MASSIVE increase, over the next few years, in how much of our important data lives somewhere “out there” in the cloud, rather than “local” databases and text files.
Connection Cloud: Making Your Cloud Data Services Look like Local DB’s
Connection Cloud Channels: It’s a Cafeteria of Data, and PowerPivot is Your Plate.
One of the ongoing perks of my job is that it continually exposes me to some of the most forward-thinking folks on the planet. We recently bumped into a startup from California, led by some very sharp people, who saw this “YD in the Cloud” problem coming years ago.
Connection Cloud makes your data in cloud services like SalesForce look like a database. So, for instance, you can access it from PowerPivot’s “Import from Database” wizard:
SalesForce.com Tables (Including Your Custom Objects) Appear as DB Tables in PowerPivot
Just Select Tables (and Filter Them if You Want!) then Click Finish
How Does That Happen?
The innards of Connection Cloud’s actual cloud service aren’t all that important to Excel Pros. All we need to do is install an ODBC driver which magically handles all of the plumbing. Since ODBC *is* a database protocol, PowerPivot literally does think it is talking to a database:
This post is running a bit long so I will cut it short here and revisit this topic maybe next week.
For now, take a look at the webinar – you’ll see me demo the whole thing end to end.