Recently we had the opportunity to assist one of our customers who is using our SharePoint service to build dashboards. We used Excel Services to take advantage of Excel's charting and Pivot Table functionality to easily allow users to update and create new metrics. Some of the data for their dashboard comes from an applications they host on-premise and some are other cloud services not hosted by us. In order to connect their data we leveraged an ODBC driver from RSSBus.
Here's how it was done:
First the ODBC drive has to be installed on the web front ends in the farm. Our technical support team handles this for our customers since customers don't have access to them (except when the customer is using dedicated SharePoint).
The ODBC connection is then configured as a System DSN.
Next the ODBC driver is added as a Trusted Provider in Central Admin Excel Services as an OLE type.
Next we setup a Secure Store Service Application for the customer using a service account that has permission to execute the ODBC connection. Note, the RSSBus ODBC drivers securely store the credentials to the actual data source so this set of credentials is just for Excel Services to make the connection. If you are in your own environment or a dedicated environment you could also setup the Excel Services Unattended account and that would take the place of the Secure Store Application.
With this in place the rest is up to Excel. In Excel create the Data Connection as an OLE DB type that references the DSN name you created.
There are some options to set here - the most important is Authentication - this should be set to SSS and the name of the Secure Store Application you created needs to go here. Or, if you are using the unattended account you set it to None. There are many ways you can save the connection: inside the workbook or to an ODC file which can be published to a Data Connection library.
It is worth mentioning a basic here: The workbook (Excel file) and, if you decided to use one, the ODC must be in trusted locations as defined in Excel Services.
Save the workbook to your Document Library and then you can use the Excel Web Access web part to show pieces or the entire Excel file. In the web part properties you have options to allow users to initiate data refresh. Since doing this from the web part does not save the file, the data will be updated but the file will display the data as it was the last time it was saved.
You can also set the refresh options in the workbook to refresh on open (which will cause the web part to load current data each time a user views it) or to refresh on a set interval.
In the end you create a dashboard that connects to data and has the ability to be up to date in real-time. Designing the metrics is easy for anyone familiar with Excel and everything is secure and abstracted so users can only see the data you publish and have no direct access to the data source(s).
Contact us if you are interested in learning more.