SSAS : Connecting to a cube with excel

Hi,

For one of my clients i'm building a cube for analysis of  information. An interesting project because the data is never analyzed before. The situation is as follows; there are datasources which has been developed in the last couple of years and there is data gathered in earlier OLAP development projects. The trick is to combine these measures into an analysis tool with a cube, reporting services and (off course) excel.

This blog is about how to connect to an analysis cube with Excel. I'm not talking about building a cube but on how to connect to the cube with Excel.

When to choose for a cube:

• A technical reason : performance. The performance of aggregated data is very well certainly in case of huge amounts of data. 
• Pivoting possiblility. In my opinion a very handy functionality of cubes. The flexibility of dynamically playing with data is very handy for getting insight in your data.
• Seamless integration with Excel. The integration with excel is great.
• Prototyping. Certainly in combination with excel you can let the key users play with the data to get some insights, thoughts and ideas. When ideas have come to mind, you can build the reports and dashboard on it.

Okay lets start building an Excel sheet based on a cube. If reporting accesses to cubes can be provided from Microsoft Excel, then report building can be performed by an end user. Majority of the time, using this method, users can construct reports the way they wish. Improvements in Excel 2007 have provided a number of new fancy features that can be used with cubes.

Please note that this article does not cover how to build SQL Server Analysis Services (SSAS) cubes. Also i'm assuming that you have your cube deployed on a proper server.

1. Open Excel and goto 'Data'' and select "From other sources". Below you can see some screenshots (in dutch):

2. Select the right cube from the list :

 3. Store the file with the connection information on a certain location

4. And now it's possible to drag certain values from the cube into Excel

Okay that's it for now.

Greetz,

Hennie