How to browse cube in Excel

Friends,

We have soooo many tools to browse cube data. We can use MDX to pull data from SSAS cube and use the result set to generate reports using many tools like ssrs,dundas,xmla .. There are many tools like this but Client’s most favorite  and even green to browse cube is EXCEL. In this post we are gonna see how to connect to SSAS Cube from Excel. I have a cube ready in my server for this demonstration. PFB the steps to be followed:

• Open Excel Sheet.
• Select DATA tab on top of the sheet.
• Select “From Analysis Services” under “From Other Sources” tab as shown below.

• Provide the server name and the authentication details and click on next. In my case the server is my local computer and hence given as LOCALHOST as shown below.

• Select cube database name from drop down list and click on next by selecting the cube as shown below.

• Click on Finish. I will ask you to select the way you wish to IMPORT DATA. Leave the settings as is and click on OK. This will create a Pivot Table Report in Excel sheet.

• You c an see Pivot Table Area on the left hand side of the sheet and Field list from cube on the right hand side of the sheet. You can drag and drop fields into ROWS,COLUMNS,FILTERS and VALUES to see the desired report as shown below.

That’s it .. You can play with Pivot by dragging and dropping Attributes and Measures on the required pane. Hope you understood it .. Happy coding !!

Regards,

Roopesh Babu V