Microsoft Office Tutorials and References
In Depth Information
Working with SQL Server Analysis Services
As you can see, the EWA Web Part is available from any Web Part Page on the server. The same is true
for the KPI Web Part and the filter Web Parts you used earlier. Similarly, you can create a report library
under any site and benefit from the features it offers.
The final section of this chapter delves into using Excel and Office SharePoint Server with Excel Services
to connect to SQL Server Analysis Services, and creating a BI solution.
Working with SQL Server
The dashboard that you created earlier in this chapter is not bound to any external data. In many cases, you
need to have workbooks and metrics bound to data that is changing and sourced from enterprise data
systems such as your CRM or ERP applications. SQL Server Analysis Services provides a premium data source
for BI scenarios for both Excel client and Excel Services. This last section of the chapter walks you through
using Excel Services and SQL Server 2005 Analysis Services. For this section, it is assumed that you are
familiar with Analysis Services and that you have them set up and running with the Adventure Works
sample Unified Dimension Model (UDM) deployed. You also need to be able to access the UDM using
either your username or some other username that you have available for this purpose.
To create a dashboard that is live against data in Analysis Services, you first need to set up a way for the
server to get to external data in Analysis Services. To summarize from Chapter 5, you must be able to
provide valid credentials when logging into Analysis Services. There are three primary ways of doing this:
If Analysis Services is on the same box as the Excel Calculation Services (ECS), use integrated
If Analysis Services and ECS are not on the same physical machine, set up a single-sign-on
(SSO) account using Office SharePoint Server.
If Analysis Services and ECS are not on the same physical machine, set up constrained
delegation or Kerberos between the two machines.
A fourth option is also relevant for when you do not have the two services on the same box: use the
browser on the same box as ECS. You can then use integrated authentication to connect to Analysis
Services because your credentials only need to move one hop. However, this option is not a viable
scenario for real-world use cases.
The rest of this example assumes that you have set up the SSO solution that is part of Office SharePoint
Server. In addition, you must create an SSO application ID called SSO and map it to a user who has
permission to access your SQL Server Analysis Services cube. If this is not possible, you can evaluate the
functionality by working on the same box as the server.
For help on setting up SSO and an SSO application ID, refer to the Office SharePoint Server technical
This chapter also assumes that you are using an evaluation setup of Office SharePoint Server and, thus,
have a trusted DCL in the Report Center. If you do not, you need to set up one. In addition, the trusted
location to which you are saving your workbooks must support external data queries.