I thought I would share the configuration steps, as this is a simple and effective way to enhance the SharePoint content and provide your SharePoint users with some useful BI data.
In this example we are going to use a set of stored credentials to access the SQL data source and need to set this up in the SharePoint Secure Store service application.
I will refer to the SQL cube just as a data source, because most of this post is applicable when accessing other data sources whatever they may be.
In Central Administration > Service applications
Select 'New' from the ribbon to create a new target application.
Name your target application ID, in this case we will be using a group mapping. See the explanation on the left of the fields for more detail.
We just need the username and password for the service account we will use to connect to the data source, so we can leave this section as default.
Set the administrator, and the members.
The members are users who will be allowed to use the stored credentials to connect to the data source.
Now the ID has been created we can set the credentials.
Insert the service account credentials, this is the account which now needs to be permissions on the data source.
Now we have created the secure store target application ID we can open Excel.
In the data tab, click on connections.
Add a new connection.
You will need to browse, and find your data source files.
On my machine I have a data sources folder in my documents which contains some template .odc files.
Use one of these files to fire the wizard.
In this instance I am using the connection shown above for an SSAS server, again this could be used for a different data source type.
Enter the SQL server and instance name for the cube and leave the log on credentials as is for now, we will change this in the next step.
You user doing this configuration will need access to the cube or this will fail when you hit next, as it needs to connect to the server.
It has found the server, and I can choose the database and cube that I want.
Name your file, this will be saved to your data sources folder, before finishing we can set the authentication settings.
Here we can set it to use the stored credentials that we set in SharePoint at the start.
Lets now insert a PivotTable to test the connection.
Choose connection.
Select the data connection we have created.
The Pivot table of our data should now be added.
This sheet will now load the PivotTable in Excel services when added to a SharePoint page, bear in mind that users you want to be able to load the data source will need to be members of the Secure Store target application ID so you could use AD groups if this is a large number of users.
Thanks for reading,
Matt
