How can I export my data from a SASS cube after cr

问题:

I can create my query in SSAS and execute, but I want to export the result to excel or a .csv file so that I can analyse it further.

There doesn't seem to be an intuitive way to do this and PowerPivot is not able to provide what I want.

回答1:

You can create a pivot table in Excel that is linked directly to your cube. This is shown e. g. in this video. Basically, you need to install the Analysis Services client on your computer (which will already be done in case you have the SSAS developer client). Then you select "Data/Form other sources/From Analysis Services" in Excel, and follow the Wizard to configure the connection to your SSAS cube, and to save this information in a llocal file, and then select the cell, where the pivot table will be located.

Another approach would be to use cube formulas like CUBEMEMBER and CUBEVALUE in Excel cells which allow you to directly get information from a cube to a cell. You can find their documentation somewhere in the Excel help or here. You can even convert a Pivot table to these formulas by selecting "Options/Tools/OLAP Tools/Convert to Formulas" as described here for Excel 2007, but there is no way back.

回答2:

Go to the actions tab and create a drillthrough to the detail data. These two examples should help you get started Defining and Using a Drillthrough Action & Excel Actions and Drill Down for SQL Server Analysis Services

回答3:

Web Pivot Table is exactly this kind of tool. It can connect to SASS and do any pivot operations just like excel. It is web based and totally run inside browsers. You can also make your own MDX query and get response display as beautiful pivot table or pivot charts. Then you can export it to excel file or as a report.

Here is demo and documents.