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.
EXEC
sp_addlinkedserver
@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
USE
AdventureWorksDW2008R2
GO
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 =
'SELECT
NON EMPTY
{
[Measures].[Sales Amount]
,[Measures].[Standard Product Cost]
,[Measures].[Tax Amount]
,[Measures].[Total Product Cost]
} ON 0,
NON EMPTY
{
NonEmpty ( [Sales Territory].[Sales Territory Region].[Sales Territory Region])
} ON 1
FROM
[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
