Connect to Analysis Services

You can create a dynamic connection from an Excel workbook to an Analysis Services data source, then refresh that connection when the data is updated.

Use Excel's Get & Transform (Power Query) experience to connect to Analysis Services.

  1. Go to Data > Get External Data > From Database > From Analysis Services. If you don't see the Get Data button, click Get External Data > From Other Sources > From Analysis Services.

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

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, 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.

  3. Under Log on credentials, do one of the following, then click Next:

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

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

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

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

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

  9. Click Finish to close the Data Connection Wizard.

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

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

    • To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

    • To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

Data Connection Wizard

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

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, 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.

  3. Under Log on credentials, do one of the following, then click Next:

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

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, 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.

  6. 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).

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

  8. Click Finish to close the Data Connection Wizard.

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

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

  11. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

  12. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×