SQL Server – SQL+MDX in one Apartment (Hybrid Query).

While replying on the MSDN Forums Most of the time people ask on how to write SQL+MDX both in single place means.

1-      Certain data like aggregated data from MDX Query.

2-      Select that data from SQL Query and get these data in table to show on reports.

I already answer couple of the question on forums but then think it’s better to write blog on above topics.

Some for the forum post I Included here for reference.

1-      SSRS combining MDX and T-SQL.

2-      Need help on MDX query to retrieve data from linked table.

3-      MDX Named Set and OPENQUERY.

4-      The OLE DB provider “MSOLAP” for linked server “Linked Server Name” supplied inconsistent metadata for a column.

How to Create SQL+MDX-

Basically to write SQL+MDX combined we have to create link server from where we want data to combine with SQL. To create link server you need to have Admin access to the SQL Server.

IF IS_SRVROLEMEMBER
(‘sysadmin’) = 1

print ‘Current user”s login is a member of
the sysadmin role’

ELSE IF
IS_SRVROLEMEMBER (‘sysadmin’) = 0

print ‘Current user”s login is NOT a member
of the sysadmin role’

ELSE IF
IS_SRVROLEMEMBER (‘sysadmin’) IS NULL

print ‘ERROR: The server role specified is
not valid.’

Above Query returns the whether you have admin admittance to the server or not to create Link Server.More Details on IS_SRVROLEMEMBER (Transact-SQL)

Suppose we have access to the Link server now to get data from created linked server we use another function named as OPENQUERY – Basically OpenQuery function 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.

More Details on OPENQUERY (Transact-SQL)

More Details on Hybrid Query-

USE
AdventureWorksDW2008R2
GO
EXEC
sp_addlinkedserver
@server=’Test’,
@srvproduct=”,
@provider=’MSOLAP’,
@datasrc=’localhost’,
@catalog=’Adventure Works DW 2008R2′
Declare @MDXExpression as Varchar(MAX)
Select @MDXExpression =

SELECT
NON EMPTY
{
[Measures].[Sales Amount]
,[Measures].[Standard Product Cost]
,[Measures].[Tax Amount]
,[Measures].[Total Product Cost]
} ON 0,
NON EMPTY
{
[Sales Channel].[Sales Channel].[Sales Channel]
} ON 1
FROM
[Adventure Works]
‘;
Exec (‘SELECT * INTO ##TestTemp FROM OpenQuery(TestTest1,”’ + @MDXExpression + ”’)’)
SELECT
CONVERT(varchar,t.”[Sales Channel].[Sales Channel].[Sales Channel].[MEMBER_CAPTION]”) AS [Date],
(CONVERT(nvarchar,t.”[Measures].[Sales Amount]”)) AS [Sales Amount],
(CONVERT(nvarchar,t.”[Measures].[Standard Product Cost]”)) AS [Standard Product Cost],
(CONVERT(nvarchar,t.”[Measures].[Tax Amount]”)) AS [Tax Amount],
(CONVERT(nvarchar,t.”[Measures].[Total Product Cost]”)) AS [Total Product Cost]
from
##TestTemp t
–DRop Table ##TestTemp

Query Execution-

1-      So above query first creates liked server with named as ‘Test’.

2-      After that MDX query executed on linked server where we have kept the MDX query in MDXExpression variable.

3-      Using OpenQuery function data gets dump in to ##TestTemp table.

4-      Finally we get the data from ##TestTemp table.

 

Regards

Suhas Kudekar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

4 Comments on “SQL Server – SQL+MDX in one Apartment (Hybrid Query).”

  1. Excellent article Suhas. One fine edit required. You need to change the alias that you have used for Sales Channel. Currently the alias used is “Date”

  2. Another option, which gives you a finer degree of control, is to create a table-typed SQLCLR function. By parsing the parameters supplied to the function and generating an MDX query, one can easily parameterize MDX. The downside is the result set is fixed in terms of columns (names, datatypes, etc). When I need more flexibility, I’ll have the SQLCLR function return XML, for further parsing.

Leave a Reply

Your email address will not be published. Required fields are marked *