none
Vendor-specific SQL generated by PowerPivot RRS feed

  • Question

  • Hi all,

     

    Wondering if anyone else has experienced this with PowerPivot connecting to "other" data sources.

     

    I am using vendor-supplied OLE DB drivers to connect to a Netezza data warehouse using PowerPivot (v10.5.2500). The SQL that is auto-generated as a result of boxes checked in the "Select Tables and Views" dialog is causing SQL parsing errors on the Netezza engine.

     

    The following SQL shape is generated for each selected table:

     

    'SELECT "<SCHEMANAME>"."<TABLENAME>".* FROM "<SCHEMANAME>"."<TABLENAME>"'

     

    This shape results in a 42000 OLE DB / ODBC error that is caused by the explicit SCHEMANAME in the SELECT clause - Netezza does not like this format.

     

    These shapes work with Netezza:

    'SELECT "<TABLENAME>".* FROM "<SCHEMANAME>"."<TABLENAME>"'

    'SELECT "<ALIAS>".* FROM "<SCHEMANAME>"."<TABLENAME>" <ALIAS>'

     

    Workaround is to write the SQL to import each table/view's data manually but I'm trying to encourage usage of PP as much as I can within our org, so avoiding this would be great for increasing adoption. 

    This appears to be a PP issue, but I'm not sure where PP's vendor-specific SQL requirements/responsibilities begin or end. 

    Any PowerPivot team guidance here would be great.

     

    Cheers,

    Nick


    http://nickbarclay.blogspot.com
    Monday, July 25, 2011 2:51 PM

Answers

  • At this point the the story for PowerPivot connectivity to the datasources that are not listed in the PowerPivot import data from list is that you would have to use the workaround mentioned above - handwrite a query in particular syntax.

    We are looking at providing better extensibility story for this scenario.

     

     


    http://www.sqljunkies.com/WebLog/edwardm/
    • Proposed as answer by Edward Melomed Wednesday, August 3, 2011 4:37 PM
    • Marked as answer by Nick Barclay Thursday, August 11, 2011 12:17 PM
    Wednesday, August 3, 2011 4:36 PM

All replies

  • At this point the the story for PowerPivot connectivity to the datasources that are not listed in the PowerPivot import data from list is that you would have to use the workaround mentioned above - handwrite a query in particular syntax.

    We are looking at providing better extensibility story for this scenario.

     

     


    http://www.sqljunkies.com/WebLog/edwardm/
    • Proposed as answer by Edward Melomed Wednesday, August 3, 2011 4:37 PM
    • Marked as answer by Nick Barclay Thursday, August 11, 2011 12:17 PM
    Wednesday, August 3, 2011 4:36 PM
  • We have the same issue and the same expectation to keep it simple to our end user: any plan / schedule improvement on the PowerPivot generated SQL codto keit "Netezza friendly" ?

    Thanks

    Thursday, November 3, 2011 8:14 PM
  • We have the same problem. What is annoying is that every time i want to add a new table i have to create the connection and enter all the paramers (provider, source name, user, passwor, ctalog, etc).

    I also noticed that to load a table from  8 millions records it takes a lot. My machine has 3 GB in memory. Is this normal?

     

    Thursday, December 15, 2011 6:47 PM