How to Use OLAP in Excel

1. Launch Microsoft Excel. Click the “Data” tab on the main menu ribbon. Click “From Other Sources” in the “Get External Data” group. Click the “From Analysis Services” option in the drop-down list.
2. Type the name of the remote OLAP server into the “Server Name” input field in the Data Connection Wizard. Click the check box next to “Use the following User Name and Password.” Enter your database username and password into the applicable boxes. Alternatively, click “Use Windows Authentication” if the remote server is set up to work with your Windows username and password. If in doubt, check login details with the server administrator. Click “Select Database and Table” followed by “Next.”
3. Click on the OLAP database that contains the data you want to import to Excel. To access a specific table or data cube within the database, click and select the “Connect to a Specific Cube or Table” check box. Select your preferred table or cube from the list of available items. Click “Save Data Connection File and Finish.” Click the “Next” button.
4. Type your preferred file name for the imported data into the “File Name” box, or skip this step to retain the default file name. Click “Browse” to set the download location, or use the default location of “My Data Sources.” Type a description of the data, a data name and relevant keywords into the applicable input fields. Click the check box beside “Always attempt to use this file to refresh this data” to enable this option.
5. Click the “Finish” button. Select your preferred option from the list under “Select how you want to view this data in your workbook” in the “Import Data” dialog box. You can choose to create a PivotTable Report or a PivotChart and PivotTable Report. Select “Only Create Connection” if you just want to save the OLAP data connection without importing data.
6. Select “Existing Worksheet” or “New Worksheet” in the “Where do you want to put the data” section. Type the worksheet cell reference of the first cell in the output table range into the input box; for example, type “A1” or 'A22.'
7. Click the “OK” button to import the data from OLAP to Excel.