Excel: Connect to SSAS Cube

You can connect to an SSAS cube from within Microsoft Excel, which is pretty fantastic because it lets you analyze your data in a PivotTable.

Here's how to get connected to an SSAS cube in Excel:

1. Click on the 'Data' tab on the ribbon.
2. In the 'Get External Data' group, click on 'From Other Sources' and choose 'From Analysis Services'.
3. Enter the server name (e.g., 'Server\Tabular').
4. Choose either Windows Authentication or enter a different user name and password to connect. You'll need to make sure that this account has the appropriate permissions to read the cube.
5. Click the 'Next' button.
6. Choose the cube to which you would like to connect.
7. Enter a file name, description, friendly name, and/or keywords as desired. This is just to save the connection in a way that makes it easy for you to identify it later if you reuse it. You can leave the defaults here if you're not picky.
8. Click the 'Finish' button.
9. Choose if you'd like to create a PivotTable Report, PivotChart, or just simply create the connection.
10. Click the 'OK' button.

If you chose either the PivotTable Report or PivotChart option you will immediately be able to interact with the cube right in your worksheet. All of your cube's fields and measures will be visible in the pane on the right-hand side of the screen.

Personally I think this is one of the coolest things about SSAS, being able to expose a cube inside a tool like Excel that's so familiar to so many data analysts. A friendly cube design makes all the data accessible in a way that eliminates the need to know all the individual data sources and joins required.

Categories: Excel, Microsoft Office 2016, PivotCharts, PivotTables, SQL Server Analysis Services, SSAS