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. 

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.