Refresh external data in Excel Services
Data in a Microsoft Office Excel workbook that has been displayed in Excel Services can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as in a database or in an Online Analytical Processing (OLAP) cube.
If the data comes from an external data source, the workbook author or administrator has defined the connection information, often in an Office data connection (.odc) file, which describes how to locate, log in, query, and access the external data source.
When you are connected to an external data source, you can perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.
What do you want to do?
Learn about refreshing data
The following illustration explains the basic process of what happens when you refresh data connected to an external data source.
1. A refresh operation gets up-to-date data.
2. A connection, often saved to a connection file, defines all the information needed to access and retrieve data from an external data source.
3. There are a variety of data sources you can access, such as OLAP, SQL Server, OLEDB providers, and ODBC drivers.
4. Up-to-date data is added to the current workbook.
Learn about security concerns
When you are connected to an external data source and you attempt to refresh the data, it's important to be aware of potential security issues, and to know what you can do about any security issues.
A data connection file often contains one or more queries that are used to refresh external data. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it's important to ensure that:
-
The connection file was authored by a reliable individual.
-
The connection file is secure and comes from a trusted Data Connection Library (DCL).
For more information on how an administrator can author connection files, create trusted locations, and secure connection files, see Microsoft Office Excel 2007 Help and Office SharePoint Server Central Administration Help.
Security Note: Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner, and that you do not inadvertently reveal these credentials to others.
Control how data is refreshed
You can control how to refresh data from an external data source by doing one or more of the following:
Refresh on open
In Microsoft Office Excel 2007, a workbook author can create a workbook that automatically refreshes external data when the workbook is opened by selecting the Refresh data when opening the file check box in the Connection Properties dialog box. (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Usage tab, and then click Refresh data when opening the file.) In this case, Excel Services always refreshes the data before it displays a workbook and creates a new session. If the workbook author clears the Refresh data when opening the file check box, the data that is cached with the workbook is displayed, which means that when a user manually refreshes the data, the user sees refreshed, up-to-date data during the current session, but the data is not saved to the workbook. If you want to ensure that up-to-date data is always displayed when you display the workbook in Excel Services, select the Refresh data when opening the file check box by using Office Excel. For more information, see Microsoft Office Excel 2007 help.
If you are using an Office Data Connection file (.odc), make sure you also set the Always use connection file check box in the Connection Properties dialog box (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Definition tab, and then click Always use connection file).
The trusted file location site settings, Short Session Timeout and External Data Cache Lifetime, can also have an impact on refresh operations. For more information, see your administrator or the Microsoft Office SharePoint Server Central Administration Help system.
Manual refresh
Note: You can manually refresh only a PivotTable report.
-
Select a cell in a PivotTable report.
-
On the Excel Web Access toolbar, under the Update menu, click Refresh Selected Connection.
Notes:
-
Any interactive operation that causes a requery of an OLAP data source initiates a manual refresh operation.
-
If this Refresh command is not visible, then the Web Part author has cleared the Refresh Selected Connection, Refresh All Connections property. For more information, see Excel Web Access Web Part custom properties.
-
Refresh all connections
-
On the Excel Web Access toolbar, under the Update menu, click Refresh All Connections.
Periodic refresh
By using Office Excel 2007, the workbook author can specify that data automatically refreshes at a specified interval after the workbook is opened for each connection in the workbook. For example, an inventory database may be updated every hour, and so the workbook author has defined the workbook to automatically refresh every 60 minutes. For more information, see Microsoft Office Excel 2007 Help.
A Web Part author can select or clear the Allow Excel Web Access Periodic Data Refresh property to enable or disable this periodic refresh operation, if the property has been defined by the workbook author. When the time interval elapses, by default, the following refresh alert prompt is displayed at the bottom of the Excel Web Access Web Part:
Refresh data for <List of Connections>? (Now) (Always) (Don't ask me again)
An Excel Web Access Web Part author can also set the Display Periodic Data Refresh Prompt property to control the behavior of the message that is displayed when Excel Services performs a periodic data refresh during a session:
-
Always means that the message is displayed with a prompt at each interval.
-
Optionally means that a user can choose to continue periodic refresh without displaying a message.
-
Never means that Excel Web Access performs a periodic refresh without displaying a message or prompt.
For more information, see Excel Web Access Web Part custom properties.
Cancel a refresh
Because a refresh operation may take longer than you expect, you can cancel it. While the refresh operation occurs, Excel Services displays a message with a prompt. Click Cancel to interrupt the operation, so that you can complete the refresh operation at a more convenient time. Data returned by queries before you cancelled the refresh operation will display.
No comments:
Post a Comment