Sharepoint 2013 – refreshing excel workbook with direct connection to sql server analysis services (SSAS) cube
I have not blogged in a while, due to moving countries and starting a new job. So i do hope that this blog will help someone or let them know how easy it is to use SharePoint 2013 to refresh data in an Excel spreadsheet.
What I needed to do, was to use an existing Excel spreadsheet which connected directly to an SSAS cube. We then wanted SharePoint to manage the refreshing of the data. In the past I thought that this was only applicable to Power Pivot Excel workbooks, but after today I realized that you can do this directly to your SSAS cube.
Getting the location of where you will store your Data Connections in SharePoint
The first thing that you need to do, is to ensure that you have the location of where you want to store your Connection File in your Excel workbook.
You will also need to have the Data Connections created in your SharePoint site.
Example:
· In our Example we are going to be connecting our Existing Excel Workbook, to a SQL Server Analysis Services (SSAS) cube, using a data connection that is stored within SharePoint.
· Once we have created our connection, we are then going to use the PowerPivot Refresh within SharePoint to refresh our Excel Workbook from the cube.
Assumptions:
· This is based on SharePoint 2013 Enterprise Edition and SSAS 2012
· We are going to assume that you have already got your SharePoint site set up.
· We are also going to assume that you have already created your Excel Workbook, which connects to a cube.
· We are also going to assume that you have either created a Documents Library, or are going to use an existing Documents Library.
· And then you have uploaded your Excel Workbook to your documents library.
NOTE: You will be required to have Owner rights to do the following below within your SharePoint site.
1. Log into your SharePoint site and click on Site Settings
2. Then click on Data Connections
a. 
3. Once this opens you will need to copy everything before the /Forms/AllItems.aspx
4. As with our example we copied the following:
http://SharePointBIWebSite/sites/wcsa/Data%20Connections
5. Now either save the above link or copy the link which will be used in the next steps.
Changing our Excel File to use the stored connection within SharePoint
1. Open your Excel File from your SharePoint location
a. NOTE: The easiest way is to navigate to where you have uploaded your Excel file and then say Open in Windows Explorer
2. Then open it in Excel
3. Then click on Data, and click on Connections
a. 
b. Then click on Properties
4. Once the Connection Properties Window opens click on Definition
5. Next what you need to do is where it says Connection Name, change this to something more meaningful and possibly shorter than the default.
a. We changed ours to the following name:
b. 
6. Now at the bottom where it says Export Connection File click on the button
a. 
7. This will then open the File Save Window
a. Now at the top where it asks you the location of where you want to save the file click on the Drop down in the Address Bar and put in the URL which we either saved or Copied in Step 4 above and paste it:
i. 
ii. Now where it says File Name you can either leave this with the default, but what I recommend is changing it to then match your Connection Name, as we did with our example:
1. 
iii. Then click Save
b. This will then open the Web File Properties Window, where it asks for some more information.
i. Once again we checked to ensure that our Title Matched our Connection File Name
ii. 
c. Then click Ok.
8. Now when you go back to your Connection Properties Window you will now see that your connection File has changed to the location of our ODC which is saved to your SharePoint data connections site.
9. The next thing that you need to do is to make sure you put a tick in the box, “Always use connection File”
a. NOTE: This is so that whenever and where ever the Excel spreadsheet is used it will always use this connection file
b. NOTE 2: This is so that when it is uploaded or run from SharePoint it will then use the associated ODC file.
c. 
10. The next thing that you need to do, is to configure the Excel Services Connection.
a. NOTE: This is required as part of SharePoint so that it can use an Excel Services connection to make the authentication to the SSAS Cube to actually refresh the data.
b. NOTE 2: You will have to ensure that the person responsible for your SharePoint installation has configured the Secure Store Service (SSS), as well as that the domain account that is linked to the SSS has access to the SSAS cubes.
c. Click on the Authentication settings.
i. Now in the Excel Service Authentication Settings screen configure it with the following below
1. 
2. NOTE: The name of our SSS ID is ExcelDataConnection
ii. Then click Ok.
11. Now when you click OK it is going to go and refresh all your sheets within your current Excel Workbook.
12. You can now save and close your Excel Workbook.
Configuring your Data Refresh in SharePoint for your Excel Workbook
In the steps below we will now configure our Excel Workbook to have a scheduled refresh, so that when people open it up it will have the latest data based on the refresh.
1. Now go to SharePoint where you have got your Excel spreadsheet uploaded.
2. Click on the Open Menu Ellipses, then the Ellipses again, and finally Manage PowerPivot Data Refresh
a. 
3. This will then open the Manage Data Refresh web page.
a. You can then configure it with the following:
b. Under Data Refresh, you must Enable it.
i. 
c. You can then select your Schedule Details based on your requirements
i. NOTE: If you want to test this now, you must select the Also refresh as soon as possible tick box
ii. 
d. For the Earliest Start time select when you want the data to be refreshed.
i. 
e. For the E-mail notifications, this is for people you want to notify if the refresh fails.
i. 
f. Under Credentials, this is where you MUST specify the same SSS that you configured in your Excel Authentication settings
i. As with our example we put in the following:
ii. 
g. Then finally for the Data Source, it should be configured with your Existing Data source you configured earlier
i. 
h. Then click Ok.
4. Now if you selected step 3c above, you can wait a few minutes and see if it worked by going back into Manage PowerPivot Data Refresh and you should see the following:
5. Now you have completed the data refresh when connecting to an SSAS Cube via SharePoint
I was running into the same issue with the same servers you use after going through a SSAS tutorial. I was going to do these steps, but I thought: why not try and give the unattended store ID I use for powerpivot to the workbook I created hitting that is hitting the cube. To my surprise the refresh errors have gone away. I haven’t figured out how to modify the data yet and refresh to see if it is being updated, but no refresh errors.