Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

You can create a dynamic connection between an Excel workbook and a SQL Server Analysis Services Online Analytical Processing (OLAP) database server, then refresh that connection whenever the data changes. You can connect to a specific offline cube file if it has been created on the database server. You can import data into Excel as either a Table or a PivotTable report.   

Use Excel's Get & Transform (Power Query) experience to connect to a SQL Server Analysis Services (OLAP) database server.

  1. Click the Data tab, then Get Data > From Database > From SQL Server Analysis Services Database (Import). If you don't see the Get Data button, then click New Query > From Database > From SQL Server Analysis Services Database.

  2. Input the Server name and press OK.

    Note: You have the option of entering a specific database name, and you can add an MDX or DAX query.

  3. In the Navigator pane select the database, then the cube or tables you want to connect.

  4. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

In Excel 2010 and 2013, there are two methods of creating a connection to a SQL Server Analysis Services (OLAP) database. The recommended method is to use Power Query, which is available if you download the Power Query add-in. If you can't download Power Query, you can use the Data Connection Wizard.

Power Query

  1. In the Power Query ribbon, click From Database > From SQL Server Analysis Services Database.

  2. Input the Server name and press OK.

    Note: You have the option of entering a specific database name, and you can add an MDX or DAX query.

  3. In the Navigator pane select the database, then the cube or tables you want to connect.

  4. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

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

  2. In the Data Connection Wizard, enter the Server name, and select a login option. Click Next.

    Data connection wizard > Connect to server
  3. Select the database and table or cube that you want to connect to, then click Next.

    Data connection wizard > Select database and table
  4. Enter a name and description for the new connection, and press Finish to save it.

    Data connection wizard > Save data connection file and finish
  5. In the Import Data dialog, select an option for how you want the data to be retrieved, and where you want to place the it. Beginning with Excel 2013, you also have the option to save the data to the Data Model.

    Data connection wizard > Import data

See Also

About Power Query in Excel

Import data from external data sources

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×