Search This Blog

Saturday, 29 September 2012

Execute MDX Query with TSQL

To start with execution of MDX query with TSQL first we need to create a Linked Server. Let’s discuss few points regarding this
Linked Server
  Linked server enables the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle, mysql, MS Analysis services etc. Many types of OLE DB data sources can be configured as linked server.
Now let’s proceed with creation of linked server within the sqlserver instance
Create a linked server
·         To create a linked sever you must have admin right of SqlServer.
·         Navigate to Server Objects > Linked Servers
·         Right click on Linked Servers and select New Linked Server.
Let’s take a sample cube database AdventureWorksDW2008R2 and create a linked server on this.

@server='Test', /* Here specify a name to linked server */
@srvproduct='',/*Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL.*/
@provider='MSOLAP',/*Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer.*/
@datasrc='localhost',/*'DRIVER={SQL Server};Server=(local); Initial Catalog=master;Integrated Security=SSPI;'*/
       @catalog='Adventure Works DW 2008R2' /*Cube Database Name*/
4.       Once the linked server is set, we need TSQL Rowset OPENQUERY function.
Open Query

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax: - OPENQUERY (linked_server,’query’)
Now you can write your Script
Declare @MDXExpression as Varchar(MAX)

Create Table  #temp(
                        Country varchar(Max),
                        Sale_Amount  float,
                        Product_cost float,
                        Tax_amount Float,
                        TProduct_Cost Float

Set  @MDXExpression =
      [Measures].[Sales Amount]
      ,[Measures].[Standard Product Cost]
      ,[Measures].[Tax Amount]
      ,[Measures].[Total Product Cost]
      } ON 0,
NonEmpty ( [Sales Territory].[Sales Territory Region].[Sales Territory Region])

      } ON 1
      [Adventure Works]';     

insert into #temp     
Exec ('SELECT * FROM OpenQuery(Test,''' + @MDXExpression + ''')')

/* Here you can join your MDX result set with your respective Sql server database tables and can extract expected result set */

SELECT      SalesTerritoryGroup,  #temp.*
FROM         DimSalesTerritory inner join #temp   on #temp.[Country]= DimSalesTerritory.SalesTerritoryCountry