Post reply

MDX to generate CSV file as Output

  • 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

  • September 26, 2009 at 7:44 pm

    #1058817

    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

  • September 27, 2009 at 8:28 am

    #1058840

    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)
    Intro to Tally Tables and Functions

  • September 27, 2009 at 10:16 am

    #1058849

    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.

  • September 27, 2009 at 3:16 pm

    #1058873

    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

  • September 27, 2009 at 7:48 pm

    #1058890

    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)
    Intro to Tally Tables and Functions

  • October 4, 2009 at 9:22 am

    #1061471

    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

  • October 5, 2009 at 5:34 am

    #1061646

    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)
    Intro to Tally Tables and Functions

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

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