locked
SAS connect to power pivot - any good news? RRS feed

  • Question

  • Hi eb

    I googled this from december 2013 right here 

    http://pivot99.rssing.com/chan-14525538/all_p31.html

      PowerPivot cannot import data using the "OLE DB provider v9.4" from SAS Instittute.
      Testing the connection in the wizard is ok, and the table list apear and chosing a "table" from the list is ok, but importing fails with a "An error occurred while processing table. OLE DB or ODBC error: The Local Provider does not currently support SQL processing.." error message.
      According to SAS documentation, that is correct. The Local Dataset does not support SQL Processing.

      But importing the same dataset into Excel using the same provider for but using Query Tables (Data tab) works just fine.
      Aparently there is a difference in how Query Tables and PowerPivot import works in Excel 2013/2010.
      Is there a way to get around this SQL Query dependancy in PowerPivot ?

      Thanks in advanced

    Is this still the best we can do two years later??

    TIA!

    Rea

    Sunday, August 16, 2015 1:47 PM

Answers

  • Hi Rea,

    According to your description, you need to know if there are any improvements for importing data using SAS OLE DB 9.4 Local Dataset Provider, right?

    PowerPivot for Excel can import data from a wide variety of sources, it does not install the providers that are listed for each data source. Some providers might already be installed with other applications on your computer; in other cases you will need to download and install the provider. If there are any improvements fro this provider, Microsoft will announce it on the link below.
    https://msdn.microsoft.com/en-us/library/gg399082%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, August 17, 2015 3:23 AM
  • Has this been resolved?

    'If this was helpful, please mark and an answer or as being helpful'

    Friday, August 28, 2015 2:39 AM

All replies

  • Hi Rea,

    According to your description, you need to know if there are any improvements for importing data using SAS OLE DB 9.4 Local Dataset Provider, right?

    PowerPivot for Excel can import data from a wide variety of sources, it does not install the providers that are listed for each data source. Some providers might already be installed with other applications on your computer; in other cases you will need to download and install the provider. If there are any improvements fro this provider, Microsoft will announce it on the link below.
    https://msdn.microsoft.com/en-us/library/gg399082%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, August 17, 2015 3:23 AM
  • Thanks has any one got a work around or better experience

    processing SAS tables in Power Pivot models using 

    newer versions of the SAS OLEDB provider?

    TIA!

    Rea

    Monday, August 17, 2015 5:19 AM
  • Has this been resolved?

    'If this was helpful, please mark and an answer or as being helpful'

    Friday, August 28, 2015 2:39 AM
  • NOPE, still doesn't work.  It's Excel Powerpivot (and powerquery) don't know how to send a select command that SAS can interpret. 

    I can do it quite easily using powershell, which shows that the general environment works.

    $ConnStr="DSN=Maps"
    $Sql="SELECT * FROM maps.austral" 
    $Conn=New-ObjectSystem.Data.Odbc.OdbcConnection($ConnStr)
    $Conn.open()

    $Cmd=New-Objectsystem.Data.Odbc.OdbcCommand($Sql,$Conn)

    $cmd.CommandTimeout =30

    $DA=New-Objectsystem.Data.Odbc.OdbcDataAdapter($cmd)

    $DT=New-Objectsystem.Data.datatable

    $null=$DA.fill($DT)

    $DT|format-table-autosize

    $conn.close()

     


    $env:PSModulePath


    Wednesday, October 18, 2017 3:08 PM