It Just Works: Excel 2010 to SSAS
March 2, 2013 Leave a comment
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).
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”.
Click “Next”, and here’s where it gets dodgy:
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.