So wouldn’t you know, one of the source files that we get to load into PowerPivot is Excel. Initially the data was being loaded into the PowerPivot Window using either the Linked table or the Copy & Paste options. I immediately decided to change this option to help streamline the process and decided to use the ‘Excel File’ import option under the ‘From Other Sources’.
The reason that I did this was to be able to quickly refresh them by using the ‘Refresh’ option in the PowerPivot Window and to also be able to eventually leverage the scheduled data refresh option in SharePoint.
The one thing about this setup was that I had to have a fairly clean file to start with, meaning that the data was in a pretty standard format with no special headers or cells in the file, just column headers and data. It didn’t matter if the data was starting out in cell A1 because the import wizard could detect this and would properly grab the data even if it started in B3.
What I would run into would be some typical Excel applications that had some crazy layouts with tons of worksheets and formulas all over the place. These files typically had some data in the top left-hand corner that was helping to drive the entire worksheet. Now for these worksheets I had to go ahead and setup a named range in the Excel worksheet.
Once this was setup we could then reference this named range when importing the Excel data into PowerPivot. Now in my example here I wouldn’t have had to have done this, but this is just showing you how you go about doing this type of setup.
Now when you do the import you will be able to reference the data like such:
You will just need to make sure that if the data in the source file changes that the named range gets updated appropriately so that you can refresh the PowerPivot data without any issues. The other thing that we ended up doing was making sure that the source data was being stored in a common location, which means not on our workstations. The location we picked was a SharePoint document library. We then accessed the data utilizing the SharePoint WebDAV protocol meaning we were using UNC paths out to the SharePoint library like such – \\ServerName\DavWWWRoot\Site\DocumentLibrary\ExcelData.xlsx.
Now that you have all of the data loaded into PowerPivot referencing a shared location the next step would be to create some PowerPivot PivotCharts and PivotTables (if you are using PowerPivot reporting functionality). Once this is done you will want to then upload the data to SharePoint into a PowerPivot Gallery to share with the end-users. The PowerPivot Gallery is also where you get the option to schedule data refreshes and this is what we are after.
The first time I setup the scheduled data refresh I ended up getting the following failure message:
The provider ‘Microsoft.ACE.OLEDB.12.0’ is not registered. The following system error occurred: Class not registered A connection could not be made to the data source with the DataSourceID of ‘<guid>’, Name of ‘<PowerPivot ConnectionName>’. An error occurred while processing the ‘<PowerPivot TableName>’ table. The operation has been cancelled.
I had received a similar error message when I tried to load Excel data into SQL Server on my x64 workstation. To resolve that issue I had to install the ‘2007 Office System Driver: Data Connectivity Components’. I tried this on the server and still ended up with the same error message (slight difference between SQL Server Management Studio, 32-bit, and SharePoint Server, 64-bit). Thanks to some help from some external resources I realized that I needed to install a x64 version of the drivers, which meant installing ‘Microsoft Access Database Engine 2010 Redistributable’. But when I went to install this it told me that I had to uninstall Office 32-bit software. The server that I was using was setup in a Sandbox (single server) setup for development purposes, so all of the software was installed and this included 32-bit version of the Office 2010 suite. I went ahead and uninstalled the data components that I just installed, Office 2010, and the PowerPivot add-in. Once that was done I was able to install the x64 version of the 2010 components and I initially got this error message:
OLE DB or ODBC error: Failure creating file.; 3436. A connection could not be made to the data source with the DataSourceID of ‘<guid>’, Name of ‘<PowerPivot ConnectionName>’. An error occurred while processing the ‘<PowerPivot TableName>’ table. The operation has been cancelled.
After some quick research I ended up on Dave Wickert’s site (if only my initial search would have brought me here first, or if I just recalled reading this posting earlier this year…) Working with the 2010 Office ACE provider. This told me that I needed the Desktop Experience feature enabled on the server. Unfortunately this was already enabled since this server was setup in a Sandbox setup for development purposes. After a little more testing, reloading the PowerPivot file in the Gallery, and running the data refresh again I ended up with a successful data refresh. Ahhh…..relief….satisfaction….scheduled Excel data refreshes into PowerPivot:) Excel-to-Excel, sweet harmony.
Subscribe to Blog feed
.png){kind=small}
. Definitely worth the $$$.
Professional Microsoft PowerPivot for Excel and SharePoint (Wrox…
. 
PowerPivot for the Data Analyst: Microsoft Excel 2010 (MrExcel Library)…
Dan English's BI Blog 