none
connect PowerPivot Excel 365 to SQL2008 (non R2) database RRS feed

  • Question

  • Hi there ,

    With one customer we ran into issues with Excel 365 connecting to MS Cubes on a SQL2008 instance.

    We noticed that this instance is the first version of  2008  instead of the R2 version.

    Would it still be possible to use power pivot to import tables from that instance to Excel?

    Wednesday, April 29, 2020 3:29 PM

All replies

  • It's hard to say without knowing what the error message was, but in theory the data providers should be fully backward compatible right back to SQL 2005. So I believe you should be able to create pivot tables directly against a SQL 2008 cube as well as import into PowerPivot.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, April 29, 2020 11:07 PM
    Moderator
  • The error message I received from the customer is in Dutch,

    So roughly translated:

    --------------------------------------------------------------------------------------------------------------------

    Cannot establish a connection

    An error occured when connecting

    Details: Analysis Services: Earlier SQL server version then SQL server 2008R2 RTM are not supported.

    ---------------------------------------------------------------------------------------------------------------------

    RTM is 10.50.1600

    The customer is using  10.0.5538.0  (which is in the Katmai range  prior to  2008R2 version)

    Check the error message in following post:

    https://community.powerbi.com/t5/Desktop/Error-connecting-to-SSAS-2005-The-DbpropMsmdSubqueries-property/td-p/118719


    • Edited by tiesblom Thursday, April 30, 2020 5:55 AM
    Thursday, April 30, 2020 5:44 AM
  • Details: Analysis Services: Earlier SQL server version then SQL server 2008R2 RTM are not supported.

    Oh OK, so that error message seems pretty clear that what you are trying to do is not supported, so there is not really much we can do about this. The 2008 version is over 12 years old and is no longer supported, so I think the general advice would be the same as in that other post that you linked to where they suggest upgrading to at least 2008R2.

    If you used an OLEDB connection type and used the SSAS OLEDB provider you might be able to connect, but I think you would have to hand write MDX queries which may not be that easy depending on how many attributes you are trying to extract. So it might still be technically possible, but difficult if you don't have experience in writing MDX queries.


    http://darren.gosbell.com - please mark correct answers

    Thursday, April 30, 2020 7:19 AM
    Moderator
  • Thanks for your feedback. This is a very average customer used to browsing cubes from within Excel.

    Certainly not prepared to write any query , least of all MDX.

    Next week we try to explain using Power Pivot to load DWH tables and build a DAX model in Excel.

    Fingers crossed..

    Thursday, April 30, 2020 1:46 PM