Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

PowerPivot Data Refresh with Excel Source Data

Posted by denglishbi on August 11, 2010

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

image

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.

image

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.

image    image

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.

image

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.

image

Now when you do the import you will be able to reference the data like such:

image

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.

image

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.

Advertisements

7 Responses to “PowerPivot Data Refresh with Excel Source Data”

  1. Hi Dan,
    Just a slight correction for the readers: The link to Dave’s post Working with the 2010 Office ACE provider doesn’t work anymore. Looks like the post got a new url with an update and the current url is http://powerpivotgeek.com/2010/08/18/working-with-the-2010-office-ace-provider/.
    Cheers,
    Hrvoje

    • denglishbi said

      Thanks for that catch. Got to love it when people change the published date on their posts:) I have gone ahead and updated my reference as well since it appears that Dave republished this posting and changed the date…grrrr.

  2. Hi Dan, Thanks for the useful tip. Unfortunately, after adding the Desktop Experience feature to the SharePoint server, rebooting the server, and re-uploading the workbook, we’re still getting this error. Do you have any other suggestions on what to try? This is killing me.

    • Dustin Stormoen said

      I was having the same issues as you were today and this past month. (OLE DB or ODBC error: Failure creating file.; 3436) and I had the Desktop Experience installed and all the ACE providers installed as well. I changed a few things. First check out this article. – (http://support.microsoft.com/kb/943280) Also look at the excel file.

      1. Open the excel file
      2. Click on “data” tab
      3. Click on Connections
      4. If you have a PowerPivot Data entry, click on properites
      5. Click on the definition tab
      6. Click on Authentication Settings at the bottom
      7. If you are using Window Auth, try changing it to the Excel Services Account you have configured in the Secure Store.
      8. Save the file back to SharePoint

      hope it works for you.

  3. James said

    Hi Dan, Did you have to configure anything in SharePoint to get this working? I have a PowerPivot workbook stored in SharePoint that’s pulling in data stored in a CSV file also stored in SharePoint, but I am getting an error for the Microsoft.ACE.OLEDB.12.0 provider not being registered. I have installed and registered the new provider in Excel Services but still getting the error. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: