Using PowerPivot Service in SharePoint 2010 is a good way of sharing PowerPivot sheets and doing data refresh without the need of installing PowerPivot Add-in on client's machine.
My first experience of using this service with Oracle 10.2.0.4, was a bit annoying but successful at the end. So, I want to share the points with people who stuck in similar situation and don't know what they should install on their SharePoint server to make a complete connection to Oracle database via already made PowerPivot workbooks.
Here I summarize those steps I have taken to get it works:
- Installing and configuring SharePoint and PowerPivot as Microsoft explained in this article.
If get an unsuccessful error message during SQL Server 2008 what pointed out here (for those who missed the step there is a workaround) may solve your problem.
- Installing Oracle 10g Release 2 ODAC and Oracle Developer Tools for Visual Studio .NET
- Installing 64-bit Oracle Data Access Components (ODAC)
- Installing a fix for bug No. 3807408 of Oracle
In this post the writer describe in Oracle 11G Client Tools the problem related to the bug No. 3807408 has been resolved. I didn't try it myself but it seems a good alternative.
- Adding the tnsnames.ora file in NETWORK folder of Oracle Home
- Restarting the server
