posted 5/25/2012 9:21:04 AM by suhas kudekar - Views: [2276]
USE AdventureWorksDW2008R2GOEXEC 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 1FROM [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
suhas kudekar (Member since: 4/16/2012 1:53:42 PM) Recived microsoft community contributor award 2012. https://www.microsoftcommunitycontributor.com/logon.aspx
View suhas kudekar 's profile
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"
Thanks Raunak for you point...!!!
I found this simplest of all(Using OpenRowSet):
SELECT * INTO zzz_temp_tableFROM OPENROWSET('MSOLAP','DATASOURCE=OLAPServername;Initial Catalog=Cube1_DEV;','MDX Query here' )
http://www.bidn.com/blogs/artuladhar/ssas/2719/dump-mdx-olap-query-output-to-a-relational-table-from-ssms
Thank you,
Anish
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 comment