MS-Excel Power Pivot + Amazon Redshift: possible to connect the data source?

0
votes

Hi: Has anyone ever managed to connect a Amazon Redshift database to Power Pivot in Excel 2013? It does not seem to work and from my online searches, I found that many people are facing the same issue.

For business intelligence / reporting purposes, I am using Power Pivot as an add-in for Excel 2013 on Windows 10 (32 bit version of Excel).

The database that I need to analyze is in Amazon Redshift (it is an export from Amazon Mobile Analytics). I have tried to connect it to Power Pivot as follows:

  • Get External Data / From other Sources
  • Others (OLEDB/ODBC)
  • Connected to my Redshift database (the test connection works)
  • Select the table that I want to connect to, from the list shown

I then get the error : "OLE DB or ODBC error. ODBC driver does not support the requested properties. An error occurred while processing table 'xxx'. The current operation was cancelled because another operation in the transaction failed.

I am able to download the data to Excel when I am not using Power Pivot: the download works perfectly well from the DATA menu in Excel. So it's not due to a basic issue like login or driver. I could not find a solution online.

Thank you!

3
mysqlexcelamazon-web-servicesamazon-redshift

3 Answers

2
votes

Official Amazon Redshift ODBC drivers produce the same error for me but it works well with postgresql drivers which can be downloaded from here: http://www.postgresql.org/ftp/odbc/versions/msi/

0
votes

Yes, we have done that successfully.

0
votes

Ken,

I too had the exact same problem, and stumbled onto this thread, having being unsuccessful in connecting Powerpivot to Redshift via the Amazon ODBC driver or the Postgres 8 driver (as suggested by an AWS engineer).

Note that a non-powerpivot connection (via Data > From Other Data Sources > Data Connection Wizard) is successful via the Amazon ODBC driver. May be this is what amirbehzad is referring to?

I did stumble upon and try the trial version of an OLE DB provider for Redshift from pgoledb.com and have been successful in pulling data from Redshift into Powerpivot. I plan on trying out the full version of the OLE DB provider in the next week or so. It may well be worth it depending on your scenario, unless Amazon adds Powerpivot support to their ODBC driver.

Note that the trial version of the OLE DB provider limits the results to 100 records and you can only view a limited number of tables via Powerpivot. The full version costs $580 and may well be worth it.

Another issue you may face with this OLE DB driver is if you need Mac support. Hope this helps, and please share your findings.