You can use an Office Data Connection (.odc) file to connect to an Online Analytical Processing (OLAP) database.
- On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.
The Data Connection Wizard is displayed. This wizard has three screens.
Screen 1: Connect to Database Server
- In step 1, type the name of the OLAP server in the Server name text box.
Note To specify an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), type the complete file path, file name, and extension. For more information about creating offline cube files, see Create an offline cube file from an OLAP server database.
- In step 2, under Log on credentials, do one of the following:
- To use your current Windows user name and password, click Use Windows Authentication.
- To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password text boxes.
Security Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
Screen 2: Select Database and Table
- Under Select the database that contains the data you want, select a database.
- To connect to a specific cube (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.
Screen 3: Save Data File and Connection
- Optionally, in the Filename text box, revise the default file name. Click Browse to change the default file location of My Data Sources, or check for existing file names.
- Optionally, type a description of the file, a friendly name, and common search words in the Description, Friendly Name, and Search Keywords text boxes.
- To ensure that the connection file is always used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data. This check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.
- To specify how a PivotTable is accessed if the workbook is saved to Excel Services and is opened by using Excel Services, click Authentication Settings, and select one of the following options to log on to the data source:
- Windows Authentication Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
- SSO Select this option to use Single Sign On, and then enter the appropriate identification string in the SSO ID text box. A site administrator can configure a SharePoint site to use a Single Sign On database where a user user name and password can be stored. This method can be the most efficient when there are many users.
- None Select this option to save the username and password in the connection file.
Security Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.
Note The authentication setting is only used by Excel Services, and not by Microsoft Office Excel. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same.
- Click OK.
- Click Finish to close the Data Connection Wizard.
The Import Data dialog box is displayed.
- Under Select how you want to view this data in your workbook do one of the following:
Under Where do you want to put the data?, do one of the following:
- To create just a PivotTable report, click PivotTable Report.
- To create a PivotTable report and PivotChart report, click PivotChart and PivotTable Report.
- To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.
- To place the PivotTable or PivotChart report in an existing worksheet, select Existing worksheet, and then type the first cell in the range of cells where you want to locate the PivotTable report.
Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .
- To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.
- Optionally, you can change connection properties by clicking Properties, by making your changes in the Connection Properties dialog box, and then by clicking OK. For more information, see Connection properties.
Tip When you use a workbook connected to a Microsoft SQL Server 2005 Analysis Services database, you may need additional information to answer specific product questions, such as reference information about multidimensional expressions (MDX), or configuration procedures for an online analytical processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) server.
For more information about SQL Server 2005 Analysis Services, you can do the following: