It Just Works: Excel 2010 to SSAS

It’s been a while since I posted a geeky article .. so, it’s well over time.

I have the pleasure of working with a talented data warehouse architect on my current project, and the need to connect Excel 2010 to SSAS became a reality this past week. The instructions to do so are easy enough:

• Navigate to the Excel 2010 Data tab.
• Click the ‘From Other Sources’ from the ‘Get External Data’ section of the ribbon.
• Select ‘From Analysis Services’; you’re presented with a ‘server name’ and ‘credentials’ dialog. We are using a Windows Azure Virtual Machine for this project, so you may have to create an endpoint that maps to a obfuscated port number (write me for details). Fill out these fields, adding “: port number” after the server name and your local login information (ensure your local login information represents a local account with an SSAS role on the system).

Click “Next”, and here’s where it gets dodgy:

• You should be presented with a “Select Database and Table” dialog box. Select the cube you want to use and click “Next”.
• In the “Save Data Connection File” dialog, click “Authentication Settings”, and then “None”. Trust me on this: you won’t see all the screens you need unless you have the system force you to enter login information in another step.
• Click “Finish”; you may be prompted to save the .odc file, replacing the old one (I did this many, many times).
• You will then see the “Import Data” dialog, which lets you place the PivotTable in your current worksheet. Select the location and click “OK”.

Now the fun starts:

• You may get an error dialog: “An error occurred in the transport layer”. Click “OK”. Because you selected “None” in the previous step, you’ll be presented with a new dialog, the “Multidimensional Connection”.
• In the “Multidimensional Connection” dialog, select “Analysis Server”; you will see the server you identified earlier.
• Your User ID should come over too .. enter your system password and click “Next”.
• Select your database in the next screen and click “Finish”.

Your .odc file should now be set up properly; save your Excel sheet and re-open, you may see the “transport layer” error again, but after this, you’ll be prompted to re-enter your system password.

Ideally, you should be able to cache this login information .. I’m looking into that and will update the post.