locked
BUG : 3 different errors when connecting PowerPivot to SSAS named instance RRS feed

  • Question

  • PowerPivot (Office Beta) trying to connect to a named instance of SSAS 2008 SP1 throws and error in the "Table Import Wizard"

    "You cannot pick file type other than excel. Please provide  valid Excel URL"

    CAUSES:

    1) Wrong default OLEDB Provider: When Excel creates a classical connection to a SSAS to get the data from acube, it select the highest MSOLAP driver available (in my case MSOLAP.4). However, PowerPivot only specifies MSOLAP, which has no support for named instance (and tested both with MachineName:InstancePort and MachineName\InstanceName)

    A ) Workaround : Go to the advanced option and specify the Provider to be MSOLAP.4

    SUGGESTION : Provide consistent behavior in the OLEDB providers choosen for excel when Connecting to SSAS


    ____________________________________


    2)  Running from within a virtual machine willing to connect to physical SSAS. Due to runing under different Windows domains and accounts, the wizard errors out. Even when it is possible to click the advanced button and change the parameterts in the connection string to use an specific username for the SSAS machine, even after a Sucessfull connection test, changin the value of connection property "Integrated Security" from SSPI to blank, and specifiying a userid, it works.

    When you close the "Advanced" window, and go back to table import wizard, you get a second error : "Failled to connect to the server. Reason : Errors in the OLEDB provider. The integrated security property cannot be set if a suername and password is supplied"

    Workaround:

    A) Create a username and password matching in the client machine with one of the server . That is impractical for escenarios when domain trust is not defined or user doesn´t have a domain.

    SUGGESTION : PowerPivot should allow specifying the credentials to connect to SSAS for testing purposes, with a warning stating that it is recommended to use Windows Integrated authentication. It shouldn´t force the SSPI.


    -_________________________________________

    3)  If an Excel workbook already as a defined connection to SSAS (by a pivot table or a CUBE function for instance) , and try to reuse it from within PowerPivot, two options are shown in the "Table Import Wizard" :

    a) Select from a list of tables and views to choose the data to import
    b) Write a SQL Query to specify the data to import.

    When choosing a) you get a list of internal data structures fo the SSAS Database (like the DimAccountMG and $Account tables).  If the second one is choosen, then we get an error : "Failled to retrieve data from $Account". Reason : Parser. The syntax for * is incorrect". However, it is known that since Yukon, SSAS support a small subset of T-SQL to query these special objects. 

    Workaround : 
     NONE

    Suggestion : For connections to SSAS, the first opetion of the wizard should invoke a different parser that understand the subset of t-SQL available to SSAS Cubes. 


    ______________________________________________

    That´s all guys. Maybe you would want to break this appart into 3 connect items. If so, please let me know. 

    Alejandro Leguizamo
    SQL Server MVP 
    SPAIN






    Alejandro Leguizamo
    Sunday, November 22, 2009 9:20 PM

Answers

All replies

  • Hi, Alejandro,

    Thank you for reporint these issues. Can you log these through the official connect feedback site so the product team can get these investigated? The connect web site is

    https://connect.microsoft.com/SQLServer/Feedback

    Thanks,
    Lisa

    Monday, November 23, 2009 4:38 PM
  • Hi Lisa:

    Done. Do you want me to specify here the associated connect item? (It has the same name as this thread). I marked it as private.

    Alejandro Leguizamo
    SQL Server MVP
    SPAIN
    Alejandro Leguizamo
    Monday, November 23, 2009 7:50 PM
  • Alejandro,

    We got the report. It is now being investigated.

    Chu
    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, November 25, 2009 12:38 AM