ALTE DOCUMENTE
|
||||
Microsoft Office Excel Add-in for SQL Server Analysis Services
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
© 2005 Microsoft Corporation. All rights reserved.
Microsoft,
MS-DOS, Windows, Windows NT, Windows Server, Excel, Microsoft SQL Server, and
Visual Basic are either registered trademarks or trademarks of Microsoft
Corporation in the
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Hardware and software requirements
Migrating from beta versions of the add-in
Securing business data in reports
Setting macro security for the add-in
Appendix A: troubleshooting the add-in
Appendix B: considerations for administrators
The Excel Add-in for SQL Server Analysis Services advanced provider string
The Microsoft® Office Excel Add-in for SQL ServerT Analysis Services is an Excel add-in that provides analysis capabilities and flexible reporting for data imported into Excel from online analytical processing (OLAP) cubes provided by Microsoft® SQL ServerT Analysis Services. The add-in is designed to help you access and analyze the information you need to make informed business decisions. For more information on the features of the Excel Add-in, refer to the Cube Analysis Help included with the add-in.
This installation and setup guide is intended to help users install the add-in, migrate from beta versions of the add-in, connect to data sources, secure business data in reports, deploy reports within an organization, and troubleshoot problems with the add-in. This guide also describes considerations for administrators.
This guide assumes that your organization has OLAP cubes provided by SQL Server Analysis Services, and assumes that you have a working knowledge of OLAP and SQL Server Analysis Services. For more information on OLAP and SQL Server Analysis Services, refer to SQL Server Books Online.
The following hardware and software is required to use the Excel Add-in for SQL Server Analysis Services:
Processor: Intel Pentium-compatible, 233 MHz or higher (Pentium III recommended)
Memory: 128 MB RAM or greater
Hard Disk: 150 MB of available hard disk space
Note Depending on the size of your reports, you might need to install additional memory in your computer to ensure adequate performance.
Supported Operating Systems: Windows® 2000, Windows 2000 Service Pack 2, Windows 2000 Service Pack 3, Windows® XP, Windows XP SP1, and Windows ServerT 2003
Supported Excel versions: Excel 2003 or Excel XP Service Pack 3 (SP3) or later
Note The Excel Add-in for SQL Server Analysis Services runs on the 32-bit
versions of Windows Server 2003 Standard,
MSXML 6.0. You can download MSXML 6.0 at the Microsoft Download Center
SQL Server 2005 Analysis Services 9.0 OLE DB Provider. You can download the SQL Server 2005 Analysis Services 9.0 OLE DB Provider at the Microsoft Download Center
Note Both MSXML 6.0 and PivotTable Service 9.0 must be installed before you install the add-in.
Use the following procedure to install the Excel Add-in for SQL Server Analysis Services on your computer. This procedure assumes that you have already installed Excel 2003 or Excel XP on your computer, and that a beta verison of the add-in has not been installed on your computer.
Before you install the add-in, save and close any open workbooks, and then exit Excel.
Double-click XLAddinSetup.exe.
In the title screen, click Next.
In the License Agreement screen, select I accept the license agreement, and then click Next.
In the Select Installation Folder screen, enter the path where you want to install the add-in, and then click Next.
In the Confirm Installation screen, click Next.
In the Installation Complete screen, click Close.
If you are installing the Excel Add-in on a computer with a beta version of the add-in installed, use the following procedure to install the released version of the add-in and migrate any reports built with the beta version to work properly with the released version.
Click Start, click Control Panel, and then double-click Add or Remove Programs.
In the Add or Remove Programs dialog box, click Microsoft Office Solution Accelerator for Excel Reporting, click the Remove button, and then click Yes.
Start Windows Explorer, and then navigate to the \Documents and Settings\%username%\Local Settings\Temp\Excel8.0 folder.
Delete ReportBuilderAddinLib.exd. If this folder contains subfolders, delete ReportBuilderAddinLib.exd from each subfolder.
Back up any reports created with a beta version of the add-in.
Install the Excel Add-in, as described previously in Installing the add-in.
Open each report created with a beta version of the add-in, and then immediately save each report to a new location to avoid overwriting the files that you have backed up.
Before you can build reports with the Excel Add-in for SQL Server Analysis Services, you must connect to one or more data sources that contain the business data that you will analyze with the add-in. Data sources include OLAP cubes provided by Analysis Services (referred to as Analysis Services cubes in the remainder of this document) and local cube files. For more information on connecting to data sources, refer to SQL Server Books Online.
Use the following procedure to connect directly to the server that hosts Analysis Services cubes:
On the Cube Analysis menu, click Manage Connection, and then click New.
In the Define Connection - Choose Server dialog box, click the Server option button.
In the Server box, type the name of the server to which you want to connect.
Click Next.
In the Define Connection - Choose Data dialog box, choose a database from the Database list.
In the Cubes list, click the cube that will provide the data for this connection.
If you want to change the default connection name for this connection, edit the Connection Name box.
If you want to change the default description for this connection, edit the Description box.
Click Finish.
In the Manage Connection dialog box, click the Connection Name of the connection that you created in the previous step, and then click Connect.
If your administrator has enabled HTTP or HTTPS connections to Analysis Services, you can connect to the server that hosts Analysis Services using the HTTP or HTTPS protocols as follows:
On the Cube Analysis menu, click Manage Connection, and then click New.
In the Define Connection - Choose Server dialog box, click the HTTP option button.
In the HTTP box, type the name of the server to which you want to connect.
If your administrator has provided a port number for HTTP connections, type that number in the Port box. The default port number for HTTP connections is 80.
If the server that hosts Analysis Services cubes requires a secure connection that uses the HTTPS protocol, do the following.
Select the Secure Connection check box.
If your administrator has provided a port
number for HTTPS connections, type that number in the
Click Next.
In the Define Connection - Choose Data dialog box, choose a database from the Database list.
In the Cubes list, click the cube that will provide the data for this connection.
If you want to change the default connection name for this connection, edit the Connection Name box.
If you want to change the default description for this connection, edit the Description box.
Click Finish.
In the Manage Connection dialog box, click the connection name of the connection that you created in the previous step, and then click Connect.
You can use the Excel Add-in for SQL Server Analysis Services without connecting to the server that hosts Analysis Services cubes by connecting to a local cube file. A local cube file provides a static copy of the data stored in an OLAP cube at the moment the file is created. Local cube files allow you to take your OLAP cube data with you when you travel; however local cube files will not reflect any changes made to the original OLAP cube after the files were created. Use the following procedure to connect to a local cube file:
On the Cube Analysis menu, click Manage Connection, and then click New.
In the Define Connection - Choose Server dialog box, click the Local Cube option button.
In the Local Cube box, type the path to the local cube file or click Browse to browse to the local cube file.
Click Next.
In the Define Connection - Choose Data dialog box, type a connection name in the Connection Name box.
If you want to create a description for this connection, edit the Description box.
Click Finish.
In the Manage Connection dialog box, click the connection name of the connection that you created in the previous step, and then click Connect.
Note You can use Excel or Analysis Services to create, refresh or browse local cube files. In Excel, local cube files are called offline cube files. For more information on cube files, refer to SQL Server Books Online or the Excel documentation.
This section provides recommendations for securing the business data that is imported into the Excel Add-in for SQL Server Analysis Services. Before deploying reports to other users in your organization, ensure that you have taken steps to secure reports that contain business data and to prevent macro viruses from harming your computer or other computers within your organization.
Analysis Services uses roles to secure data stored in OLAP cubes from unauthorized access. In most organizations, an administrator creates several Analysis Services roles to allow different groups of users to access the data that they need to do their jobs, while restricting access to other data stored in OLAP cubes. Your Analysis Services permissions are determined by the roles that you have been added to by the administrator.
When you create or refresh a report with the Excel Add-in, the report will only contain data from cubes, dimensions and cells that you have permission to access. When you save a report, Excel saves the metadata used to refresh the report; but it also saves the data loaded from Analysis Services, taking that data outside of the control of Analysis Services security.
If a report with sensitive business data is deployed to all users in your organization, users will be able to view the data in that report regardless of the roles set up in Analysis Services to protect that data. Therefore, you must take steps to secure reports created with the Excel Add-in to protect business data from unauthorized access.
Do one or more of the following to secure business data in reports:
Use Excel
password security to secure your report. You can use Excel password
security to prevent unauthorized users from opening, viewing, or modifying
worksheets that contain reports. For more information on
Save reports
to a secure shared network folder. You can secure business data by saving
workbooks that contain reports to secure shared network folders. A shared network
folder is secure if the sharing and security settings for the folder have been
set to restrict access to specific users and to set appropriate permissions for
those users. The computer that hosts the shared network folder must also be
secure. For more information on securing shared network folders and securing
computers, refer to the Windows documentation.
Note Structured reports may not function correctly when opened from a shared
network folder. Save the workbook that contains the structured report to your
computer's hard drive before you open the workbook.
Use file permissions to secure your report. You can use Windows file permissions to secure workbooks that contain reports. File permissions can be configured to restrict access to specific users and to set appropriate permissions for those users. For more information on Windows file permissions, refer to the Windows documentation.
Note The Excel Add-in can be configured to refresh data when a worksheet is opened, which will remove any data from the report that the user does not have permission to view in Analysis Services. This feature does not provide any security for business data in reports because users can change this setting on their computers. For more information on refreshing reports, refer to the Cube Analysis Help included with the add-in.
To enable undo functionality in the Excel Add-in, use the following procedure to configure macro security in Excel. For more information on macro security, refer to the Excel documentation.
On the Tools menu, click Macro, and then click Security.
In the Security dialog box, click the Trusted Publishers tab.
On the Trusted Publishers tab, select the Trust access to Visual Basic Project check box.
Note If your organization does not have a security policy in place, you may want to set macro security to High or Very High on the Security Level tab to protect your computer and other computers in your organization from macro viruses.
To deploy reports within your organization, you must distribute the Excel workbooks (.xls files) that contain your reports and one or more Office Data Connection files (.odc files). When you create a new connection, information about that connection is stored in an .odc file in the following location: \Documents and Settings\%username%\Application Data\ORSLN. The file name of the .odc file will be the same as the connection name used in the add-in. When you edit a connection, the .odc file for that connection is updated with any changes that you made in the Define Connection - Choose Server and Define Connection - Choose Data dialog boxes.
If users open a report on a computer that lacks the .odc files used by that report, they will be unable to refresh the report or manipulate the data rendered in the report. Use the following procedure to deploy reports within your organization:
Start Windows Explorer, and navigate to \Documents and Settings\%username%\Application Data\ORSLN.
Copy the .odc files with file names that correspond to the connection names used by your report.
In Windows Explorer, navigate to a secure shared network folder that has been set up for your report. For more information on secure shared network folders, refer to Securing reports, previously in this document.
Paste the .odc files into the secure shared network folder.
Save the Excel workbook that contains your report to the secure shared network folder.
Alert the users to whom you are deploying your report that your report is available, using an e-mail message, for example. Provide users with the path to the secure shared network folder, and instruct them to copy the .odc files in that folder to the \Documents and Settings\%username%\Application Data\ORSLN folder on their computer before using your report.
This section provides instructions for troubleshooting problems with the Excel Add-in for SQL Server Analysis Services. For more information on the known limitations of the Excel Add-in, refer to the Readme file that is included with the Excel Add-in.
|
Problem |
Cause and Solution |
|
When I refresh a worksheet, connection or report, the Report Refresh dialog box notifies me that the refresh failed. |
This might be caused by one or more of the following problems: The connections used by your report are closed
or the .odc files for those connections are missing. Analysis Services roles have been changed,
causing you to lose permission to access the data sources used by your
report. Windows permissions on the server that hosts
Analysis Services have been changed, causing you to lose permission to access
the data sources used by your report. Analysis Services has been moved to a
different server, or the server name has changed. A function that was entered manually (or
edited) might have errors. |
|
When I manipulate a structured report, all of the cells in the report become empty. |
This might be caused by one or more of the following problems: Dimensions, dimension members or measures used by your structured report have been deleted from Analysis Services cubes. Analysis Services roles have been changed,
causing you to lose access to the dimensions, dimension members or measures
used by your structured report. |
|
When I refresh a free-form report, some of the cells in the report become empty. When I refresh a structured report, some of the cells display #NA. |
The
user who created the report has different Analysis Services permissions than
you have, allowing them to access cells that you do not have permission to
access. The data that you cannot view shows up as empty cells, or as cells
that display #N/A, when you refresh the report. |
|
When I start Excel, the Cube Analysis menu does not appear in the menu bar. |
Problems
occurred during installation of the add-in. |
|
When I try to perform what-if analysis, write back to the database, or drill through data on a report, I get an error message. |
This might be caused by one or more of the following problems: You might not have permissions in Analysis
Services to perform what-if analysis, write back to the database or drill through
data; or writeback might not be enabled on the cube. You might be attempting what-if analysis on a calculated member. |
|
I need to export the MDX query that the Excel Add-in uses to build my report into another program. |
Solution: For structured reports, use the STRUCTUREDREPORTGETMDX or STRUCTUREDREPORTGETXML functions to extract the MDX query from your report. For more information on functions for structured reports, refer to the Cube Analysis Help included with the add-in. For free-form reports, work with your administrator to enable MDX logging on your computer to extract the MDX query from your report. |
|
Analysis Services actions do not function in my report. |
The
Excel Add-in only supports URL actions. All other Analysis Services actions
are disabled. |
This appendix describes issues that administrators should consider when supporting users of the Excel Add-in for SQL Server Analysis Services. As an administrator, you may need to modify the advanced provider string that the Excel Add-in uses to connect to data sources in order to resolve issues with database or network latency, and to optimize performance of the add-in. This appendix also describes security considerations that you should be aware of in order to protect business data from unauthorized access.
This appendix assumes that you are familiar with SQL Server Analysis Services, including how to set up and configure Analysis Services cubes.
The advanced provider string defines a connection to a data source. The advanced provider string is used whenever a user creates a report, refreshes a report, or manipulates a report. For more information on the properties of the advanced provider string, refer to SQL Server Books Online.
Note Changes to the advanced provider string are stored in an .odc file that can be deployed to a large number of users if those users are using the same .odc files for their reports. You can use a secure shared network folder to deploy .odc files with updated advanced provider strings. For more information on deploying reports, refer to Deploying reports previously in this document.
As an administrator, you may need to modify the following advanced provider string properties:
Provider: A predefined string containing other initialization properties. The default value for this property in the Excel Add-in is MSOLAP. You will not need to change the value for this property.
Connect Timeout: Determines the maximum amount of time (in seconds) that the Excel add-in will attempt to connect to the server before timing out. The default value for this property in the Excel add-in is 30 seconds. You may need to increase this value if network latency or high database utilization is causing the Excel add-in to time out for some users.
Client Cache Size: Controls the amount of memory used by the client cache, expressed as a percentage of the computer's physical memory. The default value for this property in the Excel add-in is 25 percent. You may need to decrease this value for reports that use four or more connections because four connections using 25 percent of the computer's physical memory each will occupy all of the computer's physical memory.
Auto Synch Period: Controls the frequency (in milliseconds) of client/server synchronization. The default value for this property in the Excel Add-in is 10000 milliseconds (10 seconds). You may need to increase this value if frequent synchronization is impeding the performance of Analysis Services.
Safety Options: Determines how security for user-defined functions and actions is handled. The default value for this property in the Excel Add-in is 2, which ensures that all classes for user-defined functions are checked to ensure that they are safe for initialization and scripting. For more information on the Safety Options property, refer to SQL Server Books Online.
Data Source: The name of the server computer or the full path to the local cube file. There is no default value for this property in the Excel Add-in.
This section describes issues that administrators should consider to secure the business data stored in Analysis Services cubes from unauthorized access. This section is not intended as a replacement for the security documentation included with Windows XP, Windows Server 2003, SQL Server Analysis Services, or Internet Information Services.
Note For information, tools, and tips about securing Windows platforms and applications, refer to the Microsoft Security Web site
Although
the server that hosts Analysis Services is protected by the security features
built into the operating system, these security features may be compromised if
the server is
To secure the server that hosts Analysis Services, you should keep your server up-to-date with the latest security updates from the Windows Update Web site , and keep the antivirus software installed on the server current by installing the latest security updates. The other steps that you should take to secure the server depend on which operating system is installed on the server:
For information on securing Windows NT® 4.0 Server, refer to the Maintain Security with Windows NT Server 4.0 Web site
For information on securing Windows 2000 Server, refer to the Securing Windows 2000 Server Web site and the Windows 2000 Server Security Technical Overview
For information on securing Windows Server 2003, refer to the Windows Server 2003 Security Guide
Analysis Services allows you to specify security settings for users or groups of users at the cube, dimension and cell level, as well as set security options drillthrough and writeback. The following summary describes the security features included with Analysis Services:
Roles:
Roles are the basic unit of security in SQL Server and Analysis Services. Roles
allow you to collect users into a single unit against which you can apply
permissions. A role can contain SQL Server logins, other roles, Windows users,
and Windows groups. You can use roles to secure the entire Analysis Services
database, or you can use roles to secure
Cube Security: You can use cube security to grant or restrict access to an OLAP cube. To control access to an OLAP cube, you must create a cube role or update an existing role to control access to the OLAP cube.
Dimension Security: You can use dimension security to restrict access to some of the data in an OLAP cube without restricting access to the entire cube. To control access to a dimension, you must create a cube role with restricted dimensions or update an existing role to restrict access to one or more dimensions.
Cell Security: You can use cell security to grant or restrict access to a single cell in an OLAP cube without restricting access to any other cells in the cube, or to allow users to write back to a cell when performing what-if analysis. Cell security can be configured for any cell in the database, and permissions can be assigned to users, groups and roles.
Drillthrough
Security: You can use drillthrough security to grant access to specific
columns in the fact tables that are used to generate OLAP cubes.
Important Enabling drillthrough security for a role can
override cube-level permissions set in that role. You can deny access to a
column by using either dimension security or cell security.
For more information on securing Analysis Services from unauthorized access, refer to SQL Server Books Online.
If you allow users to connect to Analysis Services from the Internet, then Internet Information Services (IIS) security is an issue that you will need to address. For information on IIS security, refer to the IIS Security Web site . For more information on configuring Analysis Services for access from the Internet, refer to SQL Server Books Online.
|
