How to Connect Excel to Analysis Services
Prefer Written Instructions?
How you connect to a cube in Excel depends on if you are connecting to a specific cube for the first time or if you have connected to the cube previously.
To connect to a cube for the first time:
1. Go to the Data Menu in Excel
2. Click the “Get Data” button on the far left
3. Hover over the “From Database” option and click “From Analysis Services”
After selecting “From Analysis Services,” the Data Connection Wizard will pop-up on your screen.
4. When the wizard appears, type in your server name
5. Select “Use Windows Authentication”
6. Then, click next
You will now be able to see all the databases you have access to as well as the individual cubes in each of those databases.
7. Select the database that contains the data you desire by using the drop-down window
8. Then click on the specific cube that you are interested in connecting to
9. Click next
10. The wizard will ask you to save the connection to the desktop. You may want to change the file name, location, and/or friendly name. However, for this demonstration, we’ll leave all the fields at their default settings and click “Finish.”
11. The “Import Data” dialog box will appear and prompt you to create a pivot table with the data you just connected to
12. Simply click “OK”
13. That’s it! Your data is now connected to Excel!
To open a cube you have previously connected to:
1. Go to the Data Menu
2. Click on existing connections
3. This will bring up a list of all the cubes you connected to previously. Select the cube you want to use and click “open.”
4. When the “Import Data” dialog box appears, hit okay
5. Your cube will now be connected to excel!