Import Data From SQL Server Analysis Services Into Excel

Before you connect Excel to SQL Server Analysis Services, make sure that the necessary provider is installed on your computer to create a connection to a SQL Server Analysis Services Online Analytical Processing (OLAP) database server.

Follow these steps to import data into Excel as a table or a PivotTable report:

1. In Microsoft Office Excel, click the Data tab, click From Other Sources in the Get External Data group, and then click From Analysis Services.
   
   
    
2. On Connect to Database Server Dialog box, in the Server name box, type the name of the OLAP database server.
   
   
    
3. Under Log on credentials, do one of the following:
   
   
   1. Click Use Windows Authentication to use your current Windows user name and password.
   2. Click Use the following User Name and Password to enter a database user name and password, and then type your user name and password in the corresponding User Name and Password boxes.
      
    
4. Click Next.
    
5. On Select Database and Table Dialog box under Select the database that contains the data you want, click a database.
   
   
    
6. And then save out this connection so that you can use it again, or share with others by pressing Finish.
   
   
    
7. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:
   
   
   • Now you get to choose how you want to import the data. I am going to choose a simple pivot table. 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.
     
   
   
    
8. Finish the rest of steps in the wizard to create a connection to a SQL Server Analysis Services cube and to import data into Excel as a table or a PivotTable report.