How do I query multi-dimensional data in a relational database with T-SQL? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

    You can use OpenQuery and OpenRowSet to execute pass-through queries against a cube. You can try the steps blow against sample database:
    1. In SSMS (SQL Server Management Studiu), connect to the database engine, expand the folder ‘providers’ below the folder ‘linked server’, double click the provider ‘MSOLAP’, and then ensure option ‘Allow InProcess’ has been selected.
    2. create a linked server:

    EXEC sp_addlinkedserver 
    @server='TestForOpenQuery', -- the given name to the linked server
    @datasrc='localhost',--the instanse name of SSAS(machine/instanse) @catalog='Adventure Works DW 2008'

    3. Execute the query:

    SELECT "[Product].[Category].[Category].[MEMBER_CAPTION]" AS Category,
    ROUND(CONVERT(float,"[Measures].[Internet Sales Amount]"),2) AS Amount
    FROM OpenQuery(TestForOpenQuery, 'select {[Measures].[Internet Sales Amount]} on 0, [Product].[Category].members on 1 from [Adventure Works]')

    Then you will get this:
    Category     Amount
    NULL         29358677.22
    Accessories  700759.96
    Bikes        28318144.65
    Clothing     339772.61
    Components    NULL

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, June 23, 2010 3:15 AM