Exporting a Cube into MS Excel

Hello,

I am wondering if it is possible to import hierarchies established in a SSAS 2005 cube into MS Excel. Right now, when I import a cube into MS Excel (Office 2003) through the following path in Excel: Data/Import External Data/New Database Query/Olap Cubes/"MyCube".cube, I am able to see cube's dimensions and measures but I cannot see my hiearchies. I would like my users to be able to drill down to the lowest level in a given hierarchy, the same way it works in a Cube Browser in BI Studio. Can it be done?

Any thoughts about it would be greatly appreciated.

Thanks a lot!

Hi.

There shouldn't be any problems using SSAS hierarchies in Excel 2003 (I do it quite a lot myself). One problem though is that the hierarchies are not displayed as such in the Pivot Table Field List but are shown as regular dimension values. Try to change the hierarchies' names, e.g. put a '-' in the beginning of the hierarchies' names to make it easier to distinguish them from regular dimensions.

Hope this helps.

Regards

Kjetil

|||

Yep, I found them. You were right, those hiearchies looked like dimension values. Let me ask you one more thing. My cube is pretty big, and I am wondering if there is a way in SSAS 2005 to get a subset of this cube, let say instead of sales for the whole week, I would like to see one day sales. I know that I could use perspectives in a cube browser but I am not sure how it is translating when you import a cube into an Excel file.?

Thank you very much for your help!

|||

Hi again.

I am not sure if I have understood your question correctly but if you want to break down the displayed data into one-day sales, one thing you can do is to to create a hierarchy with date as the lowes level member, e.g. a 'Year-Month-Week-Date' - hierarchy. This way you can select what level to display and make the calculations on and even filter out unwanted data at any level.

Hope this was what you wanted.

Regards

Kjetil

|||

That's exactly what I am going to do.

Thanks a lot Lars!