MDX to generate CSV file as Output

Question

MDX to generate CSV file as Output

shrikant-279400

SSCommitted

Points: 1651
More actions
September 22, 2009 at 12:57 am

#215508

I am looking for tool/method (other than SSIS or SSRS) to generate results of MDX query as CSV file. I tried EXCEL 2007 however it seems it does not support writing MDX in way we write T-SQL code. These CSV files would be used by end users, so I am looking for option which will have no/minimum dependency on IT (except to provide MDX, if they add some more dimensions/measures)
Regards
Shrikant Kulkarni

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 1

MDX is not like T-SQL, so not sure what you are looking to accomplish here. You could setup a linked server in SQL Server and utilize OPENQUERY and setup stored procedures in SQL Server to pull the data and then export to CSV.

SSRS or using Report Builder seems like the easiest way for an end-user to do this. You could setup a Report Model and allow the users to use Report Builder to create the reports and then export to CSV.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Dan English-729975 (9/26/2009)
...and then export to CSV.

I believe that's the crux of the original question... how would you do that without SSIS or SSRS, Dan?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

If the requirement is for the end user to actually type the MDX, then as Dan mentioned, hitting a linked server with SSMS and TSQL would definitely solve the OP's question. Why anyone would want an end user to type the MDX themselves is beyond me but this could work.

I think the OP has conflicting requirements - "users typing MDX" and "minimal IT involvement" sounds like an oxymoron to me.

Steve.

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 4

I understood the question and provided an alternative if Excel is not cutting it for them, but the best alternative if you are sticking within the SQL stack for end-users would be Report Builder.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

I agree with you, but you didn't tell them how to export to a CSV. Keep in mind that T-SQL is part of the stack and the user doesn't want to use SSIS or SSRS. Considering the slothfulness of SSRS, I agree.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 6

Thanks Dan, Jeff for inputs. Yes, Report Model is the best option for me. Sorry if my requirement confused you. Basically, business users to whome I am going to provide CSV files for data mining from our existing cube are are using SPSS PASW Modeler. I wanted this s/w to directly query our CUBE and then users can get this precalculated measures. We had sent queries to SSPS however they don't support connecting to SQL 2008 cube and thats the reason I had to for MDX to output CSV files and then use them as input to SPSS.

Regards
Shrikant Kulkarni

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

So, are you all set or do you still need help on this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)