Connect to a SQL Server Analysis Services Database (Import)

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.

Newer versions Office 2010 – 2013



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.

   

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

   

3. Select the database and table or cube that you want to connect to, then click Next.

   

4. Enter a name and description for the new connection, and press Finish to save it.

   

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.

   

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