SharePoint 2010 Excel Services

Excel Services is one the Service Applications in Microsoft SharePoint Server 2010 designed to publish Microsoft Excel 2010 workbooks for business data analysis. This piece attempts to provide us basic guidance to make the most of this feature.

1. We will start by preparing our data source by installing the AdventureWorks 2008 R2 Data Warehouse sample database.

2. Secondly, using SQL Server Business Intelligence Development Studio,  we will create an Analysis Services Project.

3. Next, we will configure our Data Source using the Connection Manager provided by the studio.

4.  Next, we will create a Data Source View including the dimension and fact tables. For our purpose, I have included the following table: DimDate, DimProduct, DimProductCategory, DimProductSubcategory, and FactInternetSales.

5. Now, we will create our cube by specifying the a Measure Group Table, Measures, and Dimensions.

6. And finally, let us build and process our data source.

7. At this point, we will insert data from Analysis Services into our Microsoft Excel 2010 spreadsheet specifying our preferred fields.

8. Then, we will publish our Excel file to a Document Library in our SharePoint site.

9. At this point, we will configure and start our Excel Services Application.

10. Now, in the Document Library where we have published or uploaded our Excel spreadsheet, let us view the file in browser.

11. And there we go, we have it now in our Web browser.

Take note that we need to properly configure and start our Excel Services, else we might encounter the error below:

12. Another option to have to have our Excel file in a Web browser is to use the Excel Web Access Web part provided by SharePoint 2010 by specifying the corresponding Excel workbook.

Thank you very much and hoping it helps!