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.

About Michael Coates
I am a pragmatic evangelist. The products, services and solutions I write about fulfill real-world expectations and use cases. I stay up-to-date on real products I use and review, and share my thoughts here. I apply the same lens when designing an architecture, product or when writing papers. I am always looking for ways that technology can create or enhance a business opportunity .. not just technology for technology's sake. My CV says: Seasoned technology executive, leveraging years of experience with enterprise and integration architectural patterns, executed with healthy doses of business acumen and pragmatism. That's me. My web site says: Technology innovations provide a myriad of opportunities for businesses. That said, having the "latest and greatest" for its own sake isn't always a recipe for success. Business successes gained through exploiting innovation relies on analysis of how the new features will enhance your business followed by effective implementation. Goals vary far and wide: streamlining operations, improving customer experience, extending brand, and many more. In all cases, you must identify and collect the metrics you can apply to measure your success. Analysis must be holistic and balanced: business and operational needs must be considered when capitalizing on a new technology asset or opportunity.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: