I have simply query in MDX to OLAP cube, like below, and I would like to use this query with a T-SQL stored procedure:
SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS,
NON EMPTY { ([Basic].[Payment Method].[Payment Method].ALLMEMBERS ) } ON ROWS
FROM [SummaryCube]
How can I create stored procedure in T-SQL which using this query?
T-SQL and MDX are completely different languages (although they have superficial similarities) so I'm afraid you can't just stuff some MDX into a stored procedure. A quick Google turned up this (making use of linked servers), which looks like it should work for what you want, although you may want to reconsider what you want to achieve and why, before going down this route.
As mentioned in your previous question here: Stored procedure in MDX
Install the addin and then you can do the following:
DECLARE @Server NVARCHAR(30) = 'SummaryCubeServerName';
DECLARE @Database NVARCHAR(50) = 'SummaryCubeDatabaseName';
DECLARE @MDX NVARCHAR(MAX) = '
SELECT
NON EMPTY { [Measures].[Revenue] } ON COLUMNS,
NON EMPTY { ([Basic].[Payment Method].[Payment Method].ALLMEMBERS ) } ON ROWS
FROM [SummaryCube];
'
CREATE TABLE #Results(
PaymentMethod AS VARCHAR(250),
Revenue AS FLOAT
);
INSERT INTO #Results
EXEC ExecuteOLAP @Server, @Database, @MDX;
SELECT *
FROM #Results;
This approach is a lot simpler than using linkedServers and OPENQUERY.
