Dynamic MDX query? 1

[TheBugSlayer]

Hi all. Is there a way to make the query below dynamic? The date is constant, but is also needs to be passed to the query.

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=RPTSRV00; Initial Catalog=CatalogAx;','SELECT [Client].[Main].&[XYZ] on rows, [Measures].[Pulse] on columns FROM [PulseABC] WHERE [Calendar].[Date].&[2010-10-21T00:00:00]') as a

Something along the lines of

SELECT C.ClientID, 
(SELECT * FROM OpenQuery(OLAP_LINKED_RPTSRV00,
'SELECT [Client].[Main].&['' + C.Main + ''] on rows,
 [Measures].[Pulse] on columns 
FROM [PulseAccession] WHERE [Calendar].[Date].&[2010-10-21T00:00:00]'))
FROM Client C

which returns NULL where I am expecting the pulse value. The original MDX query returns two columns (...[MEMBER_CAPTION] and [Measure.Pulse]...maybe that' s the problem. I really have not gotten to studying MDX yet. This simply query works on its own...once I can make it dynamic I will devote time to studying BI...

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

Try this:

SELECT C.ClientID,
D."[Measure].[Pulse]"
FROM Client C
INNER JOIN
	(SELECT * FROM OpenQuery(OLAP_LINKED_RPTSRV00,
	'SELECT [Client].[Main].MEMBERS on rows,
	 [Measures].[Pulse] on columns
	FROM [PulseAccession] WHERE [Calendar].[Date].&[2010-10-21T00:00:00]')) D
ON C.ClientID = CONVERT(INT, D."...[MEMBER_CAPTION] ")

 

[TheBugSlayer]

Thanks RiverGuy. The code above produces error OLE DB provider "MSOLAP" for linked server "OLAP_LINKED_RPTSRV00" returned message "Parser: The following syntax error occurred during parsing: Invalid token, Line 1, Offset 50, ].".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT [Client].[Main].MEMBERS on rows, Measures].[Pulse] on columns
FROM [PulseAccession] WHERE [Calendar].[Date].&[2010-10-21T00:00:00]" for execution against OLE DB provider "MSOLAP" for linked server "OLAP_LINKED_RPTSRV00".
. I still haven't been able to figure out why. The date will have to be passed dynamically, but that I will deal with afteer I am able to run the query successfully.

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

For the immediate error, one thing I see is that you are missing an opening bracket on [Measures].[Pulse]

 

[TheBugSlayer]

Thanks RiverGuy. It was that plus a couple of things. This

SELECT D."[Client].[Main].[Main].[MEMBER_CAPTION]" AS MDXMain,
       "[Measures].[Pulse]" AS MDXPulse 
FROM Client C
INNER JOIN (SELECT * 
      FROM OpenQuery(OLAP_LINKED_RPTSRV00, 
          'SELECT [Client].[Main].MEMBERS on rows,  [Measures].[Pulse] on columns    
           FROM [PulseAccession] WHERE [Calendar].[Date].&[2010-10-21T00:00:00]')) D 
ON C.ClientID = CAST(D."[Client].[Main].[Main].[MEMBER_CAPTION]" AS VARCHAR(MAX))

works.

Now I am working on being able to pass the date as a parameter. Reading an example that I found...but any suggestions are welcome. Will post final solution.

Thanks much.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

You might have to return all date members (non empty ones of course if you have a large date dimension with past and future dates) and join on the date as well.

Or, if you are using SSRS to make this report, you could do your string concatenation within the query expression of the SSRS data set.

 

[TheBugSlayer]

Thanks for your suggestion RiverGuy. Returning all date members is not a good solution as there is a very large number...data is aggregated on a daily basis. However, I figured how to make it dynamic. Here is how:

DECLARE @TSQL VARCHAR(MAX) 
DECLARE @Yesterday CHAR(19)
SELECT @Yesterday = CONVERT(CHAR(19), DATEADD(D, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) ), 126)
SELECT @Yesterday
SELECT @TSQL = 
'SELECT D."[Client].[Main].[Main].[MEMBER_CAPTION]" AS MDXMain,
       "[Measures].[Pulse]" AS MDXPulse 
FROM Client C
INNER JOIN (SELECT * 
      FROM OpenQuery(OLAP_LINKED_RPTSRV00, 
          ''SELECT [Client].[Main].MEMBERS on rows,  [Measures].[Pulse] on columns    
           FROM [PulseAccession] WHERE [Calendar].[Date].&[' + @Yesterday + ']'')) D 
ON C.ClientID = CAST(D."[Client].[Main].[Main].[MEMBER_CAPTION]" AS VARCHAR(MAX))'
EXEC (@TSQL)

This has reignited my interest in BI. I just needed an opportunity like this one.

Thank you very much for your help.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

The final query:

CREATE TABLE #PulseByMain
(
  MDXMain VARCHAR(10),
  MDXPulse DECIMAL(10, 3)
) 

DECLARE @TSQL VARCHAR(MAX) 
DECLARE @Yesterday CHAR(19)
SELECT @Yesterday = CONVERT(CHAR(19), DATEADD(D, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) ), 126)
SELECT @TSQL = 
'SELECT D."[Client].[Main].[Main].[MEMBER_CAPTION]" AS MDXMain,
       "[Measures].[Pulse]" AS MDXPulse 
FROM Client C
INNER JOIN (SELECT * 
      FROM OpenQuery(OLAP_LINKED_RPTSRV00, 
          ''SELECT [Client].[Main].MEMBERS on rows,  [Measures].[Pulse] on columns    
           FROM [PulseAccession] WHERE [Calendar].[Date].&[' + @Yesterday + ']'')) D 
ON C.ClientID = CAST(D."[Client].[Main].[Main].[MEMBER_CAPTION]" AS VARCHAR(MAX))'
INSERT #PulseByMain EXEC (@TSQL)
SELECT * FROM #PulseByMain
DROP Table #PulseByMain

I had trouble using a CTE so a temporary table will do for now.

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

MDX is a fun language. Hopefully, you won't always have to write dynamic queries from within the SQL Server relational engine. I know the debugging can be a pain!