Microsoft Office Tutorials and References
In Depth Information
You need to understand an important step from our earlier exercise related to data connection. You’ve saved the data
connection file (.odc) to the local file system. As you are aware, this will definitely not be the scenario in a production
environment. You will have to save the file to a location that is trusted. To accomplish this, you’ll have to upload the
.odc file to the Data Connection Library (DCL) on your SharePoint site.
To create a DCL, go to your SharePoint site, and then to Settings ➤ Site Content. Select “add an app” and Choose
Data Connection Library, provide a valid Name, and click on Create. On successful creation of the library, upload the
.odc file as shown in Figure 5-41 . By default, the location of the .odc file will be in c:\users\<UserName>\Documents\My
Data Source\ unless you’ve chosen a different location. Make sure to “Approve” the file.
Figure 5-41. Adding an .odc file to a Data Connection Library
After approving the file, click on it. This opens a new Excel workbook using the Excel client on your local
machine, which will be readily connected to the data source embedded in the .odc file. You need to republish your
workbook to SharePoint if you want to make use of the data connection file for Excel Services.
The Excel workbook will be prepopulated with the PivotTable. However, to add a PivotChart, select a chart from
the Charts section of the Insert tab. Following steps 8 through 16 in the previous exercise produces the results shown
in the Figure 5-40 .
Because SQL Server Analysis Server uses an Active Directory account, be sure to do the following:
%program files%/common files/Microsoft shared/web server
Monitor errors in the
Ensure that the logged-in user or the unattended account under Secure Store Services has the
required level of access permissions for SQL Server Analysis Services.
Make sure that Windows Firewall between the SharePoint server and the SSAS server allows
SSAS ports for operations. (The defaults are 2382 and 2383.)
Ensure that the firewall is not blocking SQL Server Analysis Services or Excel Services.
Be aware that if SSS is not configured properly, you might end up with the error shown in Figure 5-42 when
refreshing or filtering the data in the Excel workbook. In such situations, verify that the unattended service account
credentials are accurate and have sufficient privileges. Also, ensure that in the Trusted File Locations (Excel Service
Application on Central Administration) ➤ External Data section), you have correctly configured all the settings values.
Search JabSto ::