.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Excel Services data refresh from SSAS: How to?

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :SharePoint

Hello everybody,

I know there's been several threads already on this topic, but I haven't been able to distill a solution to my particular situation. The problem is largely because of my ignorance on the SharePoint part, I'm afraid.

Anyway, here's the situation:

  • SharePoint 2010 Ent in Stand-Alone mode
  • Windows Server 2008
  • Analysis Services 2008 R2 on the same box
  • No windows domain, all people using the system are outside the company
  • All users are created as local windows users
  • We're using the msmdpump to allow access to Analysis Services
  • Excel 2010 is the client for SSAS
  • The user id and password are both stored in the ODC file
  • The user in the ODC is member of a role that allows read-access to the cube
  • The ODC is in a trusted location
  • Since I want Excel Services to use the connection string, the Authentication setting is set to None in the ODC

Now, if I publish an Excel Pivot table to SharePoint and the try to open it in the browser, the initially cached data is displayed. As soon as I try to refresh the data, I get a Data Refresh Failed error. I've been reading for two days straight, and I think I understand that I need to configure the unattended service account using the Secure Store Service.

This is where I'm getting confused. i

View Complete Post

More Related Resource Links

Excel Services no refresh data & no error logs

Hi! I'm trying to publish an Excel workbook which loads data from SQL Server. The result is: no data loaded in the workbook and no error logged on Sharepoint logs. Notice that if I try to refresh the workbook in Office Excel all works fine! I try to set up Windows Authentication, None (to use unattended account) and with .odc file but with no result! The procedure I follow is: 1) create a new Excel file and create new data connection to the database (SQL Server 2008) 2) upload the file in the trusted file location 3) open the file in a web part and click on the "refresh all" button I used the same unattended account which I setup for this purpose with BCS and it works fine! I also try to set with PowerShell the Application ID created ("ES"): "set-spexcelserviceapplication -identity 6d871d92-19dd-4421-b33b-0012f9a5ea18 -unattendedaccountapplicationid ES" with no result. I noticed that the identity of the Excel Service application shown in the Central Administration isn't in the IIS Application Pool list: could it be the problem? I also tried to delete / re-create the application but the two IDs remains disequal! Have someone of you already worked with Excel Services and could give me any help? Thanx in advance, Elena

Cannot refresh OLAP-based data in Excel Services workbook in browser (SP 2010 / Project Server 2010)



In SharePoint 2010, Project Server 2010, Business Intelligence Center, upon attempting to refresh OLAP data in an Excel chart in the browser, the following error appears:

An error occurred during an attempt to establish a connection to the external data source.  The following connections failed to refresh:

My ODC Connection Name

We have loaded and configured Project Server 2010 according to spec (http://technet.microsoft.com/en-us/library/ee662109.aspx).  Excel Services is configured according to spec (global settings, trusted file locations, trusted data providers and trusted data connection libraries).  The Secure Store Service is configured with the ProjectServerApplication target application. 

Non-OLAP data refreshes of spreadsheet data work fine in the browser.  OLAP data refreshes in the Excel client itself work fine.  Only the OLAP refreshes in the browser fail.  The problem occurs whether using a Microsoft ODC or one that we created (saved in a trusted location).

The ULS log has only one error, which is:

06/21/2010 13:47:17.86  w3wp.exe (0x1238)               &

SSAS 2008 data refresh problem in Excel 2007



My problem sounds like that:

I have some cube created with SSAS 2008 and I'm using Excel 2007 as browser to view cube data.

I made pivot table with nessesary data, everything works fine till I save excel file and sent it to my client.

When client uses some filter, some row measures disapear. Dimension data are shown, measures are blank cells, but calculations from those measures is filled.

e.g. I see difference of sales between years in percent, but I can't see measures, which were used to count that difference, because in excel I see blank cells.

Any ideas?

Excel: Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services


Excel 2003 lets you dynamically integrate the data provided by different Web services. It also lets you take advantage of the latest capabilities in Office 2003 to customize list views, graphs, and charts, and to catalog bulk items online or offline. Find out how you can makle the most of the data returned from your Web services with the Office 2003 Web Services Toolkit API.

Alok Mehta

MSDN Magazine February 2005

Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 201

Learn how to use BCS in SharePoint Server 2010 to access and update external data by using Microsoft Excel 2010 as a client.

visio services - can't get rid of data refresh error


keep getting this error message on trying to refresh data

"Viso services was unable to refresh external data connections in this web drawing because of changes in teh structure od an underlying data source. one or more columns in this data source have been deleted after this web drawing was last saved."

The underlying data source has not been changed and I have tried re-creating the visio web drawing from scratch - always this error!

any ideas on how to progress?



2010 Visio Services Data Auto Refresh Issue


I have published vdw diagram in Sharepoint 2010 Shared Documents. The diagram data is linked to SQL database.

When I view the diagram in the browser, unless I click dedicated "Refresh" button on the top left at the sharepoint page, data is not updating.

I have changed all the SP 2010 Global settings to minimal value = 0 (as per previous post called 2010 Visio Services Data Refresh Issue).

Still you need to click dedicated "Refresh" button on the top left at the sharepoint page in order to see updated data.

In the article called "Visio Services for SharePoint 2010" is stated that new Visio service supports AUTOMATIC REFRESH.

Am I missing something? Is it possible to have a visio diagram with AUTOMATIC REFRESH published in SharePoint 2010?

I can do some workarounds like publishing in web parts page that has auto pooling, but my understanding was that the Visio diagram refresh is event based on data change only - means if data do not change 10 minutes there will be no calls?



Excel services external data source connection


can't connect to an excel services data source on the sharepoint 2010

Error : ... can't connect to external datra source .....

Excel Services data connection issue. Single environment, none authentication


I really need someone's help to configure my SharePoint 2007/ Excel services connectioon to SSAS 2008. It surely cannot be that complicated???

MOSS on server 1
SSAS 2008 cube on server 1
Trusted data provider created (OLE DB, MSOLAP.4)
Trusted data connection library created
Trusted File location created - children trusted, allow external data = "Trusted data connection libraries and embedded"
Unattended Service Account - setup

I use Excel and create a new .odc file with athenticated type = NONE and save it to the Trusted data connection library. Using the odc I open a new workbook. I firstly get a security warning saying that "MS Office has identified a potential security concern. Data connections have been blocked. If you choose to enable data connections..."

I click on Enable, and now have access to my cube. I create a pivit table and chart and publish the workbook back to a child folder in the Trusted File location. All okay so far!!

Now I open the new workbook, and as soon as I try and interact with it, I get the dreaded error "Unable to retrieve external data for the following connections:


The data sources may be unreachable, may not be responding, or may have denied you access

Verify that data refresh is enabled for the

Excel Services establish a connection to the external data source


Hello All,

I am getting below error when I click Slicer in excel 2010 powerpivot chart.

An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:

PowerPivot Data

I am using SQL Analysis services cube to fetch data into my powerpivot using windows authentication mode.

Appriciate any help.




Using the Excel Services REST API to Access Excel Data in SharePoint Server 2010 (Visual How To)

Learn how to use the REST API to retrieve resources such as ranges, charts, tables, and PivotTables from workbooks stored on SharePoint Server 2010.

Video: Using the Excel Services REST API to Access Excel Data in SharePoint Server 2010 (Visual How

Watch a short video that shows how to programatically retrieve data from an Excel workbook.

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

Creating an Excel Spreadsheet and Adding Data to It Programmatically

The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged application can interrelate with Excel as a COM server.

delete data in excel sheet



           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;


Please can anybody this answer.It is urgent

Export Gridview data to Excel sheet


Hi ,

     I have a requirement where i need to export the gridview data to excel.My code export the data in excel that was fine.

But the enchancement needed now , I was exporting the 3 gridview in same excel sheet under the workbook.

Now i need to export the 3 grid view in 3 different sheet in the same workbook.

so can please tell or share the updated code in my below code ...

public void writeovrsummary()


        Response.Write("Overall Summary");
        //Response.Write("Report as of " + DateTime.Now.ToString("hh:mm ss tt"));

        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        SqlCommand com = MyConnection.CreateCommand();
        com.CommandText = "useroverall_Report";
        SqlParameter[] par = new SqlParameter[1];
        par[0] = new

Insert data to Excel using OLEDB



I am trying to insert data to Excel Document using OLEDB as follows:-

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 
values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";
myCommand.CommandText = sql;

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 

values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";

myCommand.CommandText = sql;


But, the Title1 ,Title2, Title3 are in the 3rd row of the Excel Document. So, the data is not inserted.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend