Over the last few months I’ve seen a few folks having problems connecting Tableau and PowerPivot data sources.
Occasionally, the person wasn’t able to connect to a workbook on their local machine. But more often than not, the challenge was connecting to a workbook saved in the PowerPivot Gallery.
Being a masochist, I decided it might be fun to install the Microsoft BI stack on a VM in my domain: It’s been a few years since I’ve felt this particular type of pain, and I was itiching for a good thrashing. In the immortal words of Eric Stratton:
“…In this case, I think we have to go all out. I think that this situation absolutely requires a really futile and stupid gesture be done on somebody’s part”
So, three hours later I have a single node of SharePoint 2013 up and running with Excel Services and the SQL Server PowerPivot System Service chugging along. Hurts so good!
I also went ahead and updated Office (x86) from 2010 to 2013 – I had been running some old add-ins that finally got updated and now work in 2013, so why not go all in.
Off to the Tableau Website where this handy-dandy article told me which version of the add-in to download and install with Tableau 8.1:
http://kb.tableausoftware.com/articles/knowledgebase/choosing-powerpivot-addin-and-driver
Since I’m running 8.1 and like to run x86 Office, here’s what I need:
I dutifully download the correct Add-in from the Tableau site:
http://www.tableausoftware.com/support/drivers
And now, I’m ready to test…
First, I find my PowerPivot file:
Next, connect to it:
Volia, one data model ready for consumption:
But that was supposed to be easy. How about connecting to the same workbook saved directly out in SharePoint?
Yay! An error:
“The path you specified is invalid”
“Analysis Services database error 0x80004005: Either a connection cannot be made to the SharePoint server, or Analysis Services is not running on the computer specified”
Aha! Look what those chumps did to us and what I did to myself with the cute file name!
Note the spaces in the name of the default Power Pivot document library that was created for me: “PowerPivot Gallery”.
You can even see that the space is being encoded with %20 in the browser.
Likewise, my trying-too-hard-to-be-funny file name has tons of spaces.
I need to reference this file like so:
http://SharePoint2013/PowerPivot%20Gallery/I%20want%20my%20morning%20back.xlsx
…and it worked.
Now, the funny thing here is that I JUST tried the same thing again without encoding my spaces:
http://SharePoint2013/PowerPivot Gallery/I want my morning back.xlsx
And it continued to work. In fact, I can’t get it to fail now.
So, you may need to encode your spaces. Or not, I’m not sure. But I have no problems accessing this stuff from the local filesystem or via SharePoint itself.
Enough pain for one day, time to watch the Jersey Shore marathon.