Connect to or import data from SQL Server Analysis Services

By using an Office Data Connection (ODC) file (Office Data Connection (ODC) file: A file that stores information about a connection to a data source (such as an OLE DB data source) and the data associated with the connection.), you can create a connection to a SQL Server Analysis Services 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.)) database server. You can connect to a specific 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.) if it has been created on the database server. You can also import data into Excel as a Table or a PivotTable report.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

Excel  Ribbon Image

The Data Connection Wizard is displayed. This wizard has three screens.

  1. On screen 1, Connect to Database Server, in the Server name box, type the name of the OLAP database server.

 Tip   If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  1. 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 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.

  1. To advance to screen 2, Select Database and Table, click Next.
  2. Under Select the database that contains the data you want, select a database.
  3. 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.
  4. To advance to screen 3, Save Data Connection File and Finish, click Next.
  5. In the File Name box, revise the default file name as needed (optional).

Click Browse to change the default file location of My Data Sources, or check for existing file names.

  1. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).
  2. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  1. 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, select one of the following options to log on to the data source, and then click OK.
    • 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.
    • SSS    Select this option to use Secure Storage Service, and then enter the appropriate identification string in the SSS ID text box. A site administrator can configure a SharePoint site to use a Secure Storage Service database where a 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 user name 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   This authentication setting is only used by Excel Services, and not by Microsoft 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.

  1. Click Finish to close the Data Connection Wizard.
  2. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:
    • To create just a PivotTable report, click PivotTable Report.
    • To create a PivotTable report and a 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.
  3. Under Where do you want to put the data, do one of the following:
    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image.

  1. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.
  2. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.
 
 
Applies to:
Excel 2010