How to connect Excel to SQL Server 2005 Analysis Services

Note Before you connect Excel to SQL Server 2005 Analysis Services, make sure that Microsoft OLE DB provider for SQL Server 2005 Analysis Services is installed on the computer. To do this, follow these steps:

1. Click Start, click Run, type notepad, and then click OK.
2. In Notepad, click Save on the File menu.
3. In the Save As dialog box, click All Files in the Save as type box, type text.udl in the File name box, and then click Save.
4. Double-click the text.udl file.
5. In the Data Link Properties dialog box, click the Provider tab, and then determine whether Microsoft OLE DB Provider for Analysis Services 9.0 is listed in the OLE DB Provider(s) list.
   
   If Microsoft OLE DB Provider for Analysis Services 9.0 is not listed, you must install Microsoft OLE DB provider for SQL Server 2005 Analysis Services. To obtain Microsoft OLE DB provider for SQL Server 2005 Analysis Services, visit the following Microsoft Web site:
   http://go.microsoft.com/fwlink/?LinkId=54583

How to connect Excel 2007 to SQL Server 2005 Analysis Services

Method 1

1. In Microsoft Office Excel 2007, click the Data tab, click From Other Sources in the Get External Data group, and then click From Analysis Services.
2. In the Server name box, type the name of the OLAP server.
3. Under Log on credentials, perform one of the actions:
   • To use your current Microsoft 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 User Name and Password boxes.
4. Click Next.
5. Under Select the database that contains the data you want, click a database.
6. Finish the rest of steps in the wizard to create a connection to a SQL Server 2005 Analysis Services cube and to import data into Excel as a table or a PivotTable report.

Method 2

1. In Excel 2007, click the Data tab, and then click Connections in the Connections group.
2. In the Workbook Connections dialog box, click Add.
3. In the Existing Connections dialog box, click Browse for More.
4. In the Select Data Source dialog box, click New Source.
5. In the Data Connection Wizard, click Microsoft SQL Server OLAP Services, and then click Next.
6. In the Server name box, type the name of the OLAP server.
7. Under Log on credentials, perform one of the actions:
   • 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 User Name and Password boxes.
8. Click Next.
9. Under Select the database that contains the data you want, click a database.
10. Finish the rest of steps in the wizard to create a connection to a SQL Server 2005 Analysis Services cube.
11. On the Data tab, click Existing Connections in the Get External Data group, click the newly created connection, and then click Open to import data into Excel as a table or a PivotTable report.

How to connect Excel 2003 or an earlier version of Excel to SQL Server 2005 Analysis Services

Method 1

1. In Microsoft Office Excel 2003 or in an earlier version of Excel, point to Import External Data on the Data menu, and then click Import Data.
2. In the Select Data Source dialog box, click New Source.
3. In the Data Connection Wizard, click Microsoft SQL Server OLAP Services, and then click Next.
4. In the Server name box, type the name of the OLAP server.
5. Under Log on credentials, perform one of the actions:
   • 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 User Name and Password boxes.
6. Click Next.
7. Under Select the database that contains the data you want, click a database.
8. Finish the rest of steps in the wizard to create a connection to a SQL Server 2005 Analysis Services cube and to import data into Excel as a table or a PivotTable report.

Method 2

1. In Excel 2003 or in an earlier version of Excel, click PivotTable and PivotChart Report on the Data menu.
2. Under Where is the data that you want to analyze, click External data source, and then click Next.
3. Click Get Data.
4. On the OLAP Cubes tab, double-click New Data Source.
5. In the Create New Data Source dialog box, specify a name for the data source, click Microsoft OLE DB Provider for Analysis Services 9.0 in the Select an OLAP provider for the database you want to access list, click Connect, and then click OK.
6. Click the newly created OLAP Cube connection, and then click OK.
7. Finish the rest of steps in the wizard to import data into Excel as a PivotTable report.