How to Use DMX Queries from T-SQL

Here is a simple example how you can use DMX queries from T-SQL.

From SQL Server side, create a linked server to the Analysis Services. Use T-SQL Openquery function, and inside this function use DMX Select to send DMX queries to AS. Don’t forget SQL Server tables do not accept nested tables, so you have to flatten your DMX results (FLATTENED keyword).  Code:

— Consuming DMX results in T-SQL

— Creating a linked server
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’ASLOCAL’, @srvproduct=N”,
@provider=N’MSOLAP.3′, @datasrc=N’localhost’, @catalog=N’DMDSV_Prepare’
GO
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’collation
compatible’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’data access’,
@optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’rpc’,
@optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’rpc out’,
@optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’connect
timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’collation
name’, @optvalue=null
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’query
timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server=N’ASLOCAL’, @optname=N’use remote
collation’, @optvalue=N’true’
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’ASLOCAL’, @locallogin =
NULL , @useself = N’True’
GO

— Simple empty prediction join
SELECT dmx.ModelRegion, dmx.[Amount.$Time] As AmTime, dmx.[Amount.Amount] AS
AmAmount
FROM OPENQUERY(ASLOCAL,
‘SELECT FLATTENED ModelRegion,
PredictTimeSeries(Amount, 3) AS Amount
FROM SalesForcast
WHERE ModelRegion >= ”M200” AND ModelRegion < ”M300” ‘) AS dmx GO — Prediction join with Openquery SELECT dmx.* FROM OPENQUERY(ASLOCAL, ‘SELECT t.[LastName], t.[FirstName], [TM_DT].[Bike Buyer], PredictProbability([Bike Buyer]) AS DTPredictProbability, t.[Gender], t.[NumberCarsOwned] FROM [TM_DT] PREDICTION JOIN OPENQUERY([Adventure Works DW], ”SELECT [LastName], [FirstName], [Gender], [NumberCarsOwned] FROM [dbo].[ProspectiveBuyer]”) AS t ON [TM_DT].[Gender] = t.[Gender] AND [TM_DT].[Number Cars Owned] = t.[NumberCarsOwned]’) AS dmx GO — Clean-up USE [master] GO EXEC master.dbo.sp_dropserver @server = N’ASLOCAL’ GO