Microsoft Office Tutorials and References
In Depth Information
Working with SQL Server Analysis Services
open. This is an important step. It means that every time the workbook is opened, the PivotTable reports
in it are refreshed and new data is retrieved from Analysis Services. This serves two purposes:
It guarantees that if the data in the cube changes (for example, new sales data is loaded into it),
the users viewing the report always see the most up-to-date values.
It is potentially important from a security point of view.
When Excel creates the PivotTable, it saves the latest data queried from Analysis Services with the
workbook. There may be cases in which different users have different permissions to the database or cube. In
these cases, you do not want users to open the workbook and see the cached values. These values may
have been queried with the credentials of a user who has permission to different data. For example, you
may have two managers and each may have the permission to see only the salary data for their
organization. The Human Resources analyst creating the report for them may have permission to see all the
data. The values for both organizations are saved with the workbook (because the analyst’s credentials
were used to query the cube and create it). If the first manager creates a workbook, it contains the cached
results of his or her organizations salary data. If the workbook is not set to refresh on open, then either
manager viewing the workbook on the server sees all salary information for all employees.
To provide a solution to this problem, the workbook author must set the workbook to refresh on open.
The workbook is then published to the server and the two managers are given View Only permissions
so that they see only the server-based workbooks. In addition, the server administrator must ensure
that the server is configured to fail when it attempts to open workbooks if the data query in the refresh
on open fails. Now, when one of the managers tries to view the workbook on the server, the query to
Analysis Services is performed on open, and the data that is returned is based on that manager’s
permission level only. If there was a problem querying the data, the workbook is not displayed at all. And,
because the manager cannot open the actual workbook file, but only view the server-based workbook,
the manager never sees the salary information for employees who are not in his or her organization.
You begin this Try It Out from where you left off in the previous one, with the Import Data dialog
Try It Out Creating a PivotTable on Analysis Services Data
To create a PivotTable on analysis services data, follow these steps:
Ensure that the PivotTable Report option is selected (the default).
Click the Properties button to set the report to refresh on open. This guarantees that the data
is up-to-date when the dashboard is viewed.
Click OK to finalize the properties.
Click OK to create the PivotTable. You should have a blank PivotTable on the sheet and the
PivotTable Field List should open.
Scroll down the Field List to show fields related to Internet Sales (in the drop-down menu).
Check the Internet Sales Amount measure to place it in the PivotTable values.