Post reply

MDX query result to SQL 2005 table

  • June 18, 2010 at 6:18 am

    #143820

    Hi,

    Can anyone tell me how we can push the results of MDX queries into SQl 2005 table and how this MDX query can be scheduled to run at a scheduled time?

    Thanks in Advance,

    Valli

  • June 23, 2010 at 10:26 am

    #1184071

    You need to add yr analysis services database as a linked server and then use openquery to send in yr mdx query

    i.e in yr 2005 database new query window...mdx query to get top 5 selling products by sales

    e.g.

    SELECT "[Dim Product].[English Product Name].[English Product Name].[MEMBER_CAPTION]" As Product,

    "[Measures].[Sales Amount]" AS Sales_Amount

    INTO #temp

    FROM

    OPENQUERY(OLAP,

    ' SELECT [Measures].[Sales Amount] ON COLUMNS,

    TOPCOUNT([Dim Product].[English Product Name].Children, 5, [Measures].[Sales Amount])

    ON ROWS

    FROM dsvAdventureWorksDW ')

    in my example above my linked server is called OLAP and am saving results into a temp table

    Select * from #temp gives :

    Product Sales_Amount

    Mountain-200 Black, 461373469.5481999971

    Mountain-200 Black, 421363142.0933999969

    Mountain-200 Silver, 381339462.7903999968

    Mountain-200 Silver, 461301100.098399997

    Mountain-200 Black, 381294866.1411999967

    you can wrap this into a stored proc if you wish and run it as job

  • July 28, 2010 at 6:00 am

    #1199175

    Thanks a lot.I was able to find time to try that out only today. It is working fine.

Viewing 3 posts - 1 through 2 (of 2 total)

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