skip to Main Content


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.

imageThere were three elements to the pitch:

  1. A common internet protocol for exchanging data.
  2. “Teaching” Excel, Access, and other tools to consume any data source exposed via that protocol.
  3. 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:

  1. 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).
  2. 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

  3.  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

1991 hip hop hit "O.P.P" performed by Naughty by NatureLongtime 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:

Phil Niles on Open Data

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 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: 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:

Accessing SalesForce and other Cloud Sources from PowerPivot

More Info

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.

Click Here to See me Demo All of This on YouTube

Also, check out the two-minute Connection Cloud overview video.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 3 Comments
  1. Al Gore was actually paying attention to the internet very early on. I remember he was in Chicago on Milton Rosenberg’s Extension 720 talking about how the internet was going to change everything. I called in toward the end of the program and asked him if his plans were big enough (no joke) So you could say that I am the father of the internet just without knowledge of how any of it works 🙂

    Amazing that those Excel skills that served us well in the 80’s & 90’s are still producing the goods in this crazy wired-up world we live in now.

    I watched Rob’s presentation in real time and he did a great job with it.

  2. Just curious about the choice of implementing ODBC vs OLE DB for ConnectionCloud. I thought PowerPivot could only import from OLE DB data sources. Do users need to use an OLE DB for ODBC bridge to get to the data?

Leave a Reply

Your email address will not be published. Required fields are marked *