MDX retrieve recordset

Question

I am trying to match existing reporting functionality while building up a new cube for a Proof of Concept.

Can I retrieve data from my cube using an MDX query and put it into a recordset so I can then populate an Excel Sheet with the data?

Answer 1

Have you considered attaching from Excel directly to Analysis Services, and using the built-in drag-and-drop query functionality? Excel will generate the MDX for you.

I've never done it, but it should be possible to integrate ADOMD.NET into a VSTO application, which will then run inside Excel. You might also be able to access some of Excel's MDX query machinery directly that way....

Answer 2

Best way to work with OLAP and Excel is detailed here: http://stackoverflow.com/questions/49876/simpler-interface-for-sql-server-analysis-services-cubes-for-end-users/70515

If you want to write your own MDX instead of using drag-drop, you can't do this with Excel as far as I know. However, most languages would let you fire off some MDX at a cube, and then you could loop the recordset yourself to create a CSV file.

Or even simpler, use SQL Server's Query Analyser like this, and save the results to a CSV:

SELECT TOP 100 * FROM OPENROWSET('MSOLAP',
'Datasource=MyHotServer;Initial catalog=MyLovelyCube',
'SELECT {dimensions(0).members} ON ROWS, {time.defaultmember} ON COLUMNS FROM sales')

(N.B. If you don't know MDX yet....don't start learning unless you really have to! It isn't as easy as SQL, and you can get away without it usually.)