Refreshing or Filtering Data in Published Excel Services Workbooks [SharePoint 2010]

This is Ayman El-Hattab, a Regional Technology Solution Professional (Collaboration Platform) from MEA Center of Expertise. I'm also a SharePoint MVP, active blogger, published author and a community fanatic. I'm really so excited to be a part of MEA Center of Expertise and to write the first technical post here :) I would also like to thank Khaled Hnidk for the introductory post he just published in which he introduced the team, its mission and goals.

 Let's start with Excel Services in SharePoint 2010, as shown in the screenshot below, I was trying to consume data from SQL Server Analysis Services cube using Excel 2010 Pivot Table.

Refreshing and filtering data worked as expected on the client but when I published my workbook to Excel Services, filtering and slicing produced the following error :

“The data connection uses Windows Authentication and Excel Services is unable to delegate user credentials.”

Of course, the workbook existed in a trusted location and the external data connection file resided in a trusted connection library.

To solve this issue, I navigated to the Central Administration and started “Claims to Windows Token services” as shown below :

This allowed me to refresh data using Windows Authentication…