none
Can't refresh reports connected to the cube in Excel Services RRS feed

  • Question

  • In the BI center, I can create reports connecting to the SQL Analysis Services cube ODCs, but I can't refresh the reports in Excel Services.  When I try, I get the message:  "An error occured during an attempt to establish a connection to the external data source.  The following connections failed to resfresh:  OLAP Portfolio Analyzer."

    Any suggestions?

    Thanks

    Tuesday, January 4, 2011 4:00 PM

Answers

  • when you use cube to create a report in excel - it saves the portfolio analyzer cube in your local folder. You can verify this if you go to your excel file, go to Data -> connection -> properties and you can see the data connection your report is using is located on the local path.  

    Now you have to do two things:

    1. use the cube located on your BI/<data connection folder>/<cube data folder>

    2. add your data connection path, i.e. BI/<data connection folder>/<cube data folder>, to your the excel trusted data location

    for more explanation with screen shot you can follow my blog from the below link where i have mentioned every step to eliminate this error.

    Let me know if this helps.

     

     


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Tuesday, January 4, 2011 8:49 PM

All replies

  • Assuming its 2010, SSAS data with Excel services only works with windows authentication, what authentication mode are you using ?

    another thing try accesing your OLAP cube with Excel sheet manually with windows authentication and see if youa re able to access, also are you able to access non OLAP data and refresh it in excel web access ?

    Also check for secure store service identity is setup properly and mapped with the SSS ID  being used to retireve the OLAP data 

     



    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Tuesday, January 4, 2011 5:24 PM
    Moderator
  • when you use cube to create a report in excel - it saves the portfolio analyzer cube in your local folder. You can verify this if you go to your excel file, go to Data -> connection -> properties and you can see the data connection your report is using is located on the local path.  

    Now you have to do two things:

    1. use the cube located on your BI/<data connection folder>/<cube data folder>

    2. add your data connection path, i.e. BI/<data connection folder>/<cube data folder>, to your the excel trusted data location

    for more explanation with screen shot you can follow my blog from the below link where i have mentioned every step to eliminate this error.

    Let me know if this helps.

     

     


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Tuesday, January 4, 2011 8:49 PM
  • Sunil,

    Yes it is 2010 and yes we are using windows authentication.

    Yes I can access the OLAP cube in Excel and refresh it.  Yes, I can access and refresh non-OLAP data in in excel web access.

    In our organization, responsibilities are separated, so I didn't do the initial installation of the software and don't have access to Sharepoint Central Admin to check how the SSS ID was set up.  Based on what they told me, they used ProjectServerApplication as the target application ID; however, I'm not sure what you mean by is "mapped with the SSS ID being used to retrieve the OLAP data."  I'll check with folks who installed the SW. 

    Thanks for the suggestions.

    Thursday, January 6, 2011 8:10 PM
  • DKLJoe, have you tried the steps i have mentioned on my above post?

    have you verified if your OLAP Cube folder is added added in the trusted locations folder in central administration?

     


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Thursday, January 6, 2011 9:08 PM
  • Khurram - I sent your suggestion and the one from Sunil to the folks who have access to Sharepoint Central and they fixed it - so it now works.  I haven't heard back from them yet on what the actual problem was.  Thanks for your help (and thank you Sunil).
    Friday, January 7, 2011 8:31 PM