MDX Query Optimization -- Insert into SQL Table

Question

All,

I'm not very familiar with MDX queries, if you are, that make become quickly apparent with this question. This query was modified off a tableau MDX query I copied. I use the query with a linked SSMS server, inserted into a table. This is necessary because of the way I can retrieve the data. The resulting dataset contains many duplicate/null columns/rows and often results in locking conflicts. Can this query be improved? I know it's not ideal way to pull the data but it's all I can do.

set @SQL = 'SELECT * INTO db_rpt.dbo.temptable1 from (select * from 
openquery(server,''
WITH MEMBER [Measures].[LEVEL INSTANCE none:Date:qk - lev00] 
AS ''''CASE WHEN IsEmpty([Dim Date].[Date].CurrentMember.MemberValue) OR [Dim Date].[Date].CurrentMember.MemberValue = null 
THEN null 
ELSE CDate([Dim Date].[Date].CurrentMember.MemberValue) 
END''''
,SOLVE_ORDER = 127

SELECT 
{[Measures].[METRIC1]
,[Measures].[METRIC2]
,[Measures].[METRIC3]
,[Measures].[METRIC4]
,[Measures].[METRIC5]
,[Measures].[METRIC6]
} 

DIMENSION PROPERTIES HIERARCHY_UNIQUE_NAME ON COLUMNS , 
NON EMPTY (Hierarchize({DrilldownLevel({[Dim Employee].[GROUP1].[All]},,,INCLUDE_CALC_MEMBERS)})
,Hierarchize({DrilldownLevel({[Dim Employee].[Unique Emp Name].[All]},,,INCLUDE_CALC_MEMBERS)})
,Hierarchize({DrilldownLevel({[Dim Employee].[GROUP2].[All]},,,INCLUDE_CALC_MEMBERS)})
,Hierarchize({DrilldownLevel({[Dim Employee].[GROUP3].[All]},,,INCLUDE_CALC_MEMBERS)})
,Hierarchize({DrilldownLevel({[Dim Employee].[GROUP4].[All]},,,INCLUDE_CALC_MEMBERS)})
,Hierarchize({DrilldownLevel({[Dim Employee].[GROUP5].[All]},,,INCLUDE_CALC_MEMBERS)}))    ON ROWS 

 FROM [Cube] 
 WHERE (StripCalculatedMembers(Filter(  [Dim Date].[Date].[Date].AllMembers
 ,(([Measures].[LEVEL INSTANCE none:Date:qk - lev00] >= cdate(''''' + @date1 + ''''')) 
 AND ([Measures].[LEVEL INSTANCE none:Date:qk - lev00] <= cdate(''''' + @date2 + ''''')))))
 ,[Dim QA Form Section].[System - Form].[Form].&[Group]&[Form1])'')) t1'

we moved away from openquery due to locking problems and now use the CLR stored procedures provided by this project olapextensions.codeplex.com — whytheq
@whytheq Thanks!, can I use this to select into a SQL table? — yeahthisisrob
we use it to insert into temporary tables e.g. INSERT INTO #BLAHBLAH; EXEC ExecuteOLAP @Server, @Database, @MDX; — whytheq

whytheq whytheq · Accepted Answer · 2017-07-13T18:50:30

CASE can be slower than IIF so this could be maybe re-written:

CASE WHEN IsEmpty([Dim Date].[Date].CurrentMember.MemberValue) OR [Dim Date].[Date].CurrentMember.MemberValue = null 
THEN null 
ELSE CDate([Dim Date].[Date].CurrentMember.MemberValue) 
END

I think this is equivalent:

IIF(
  IsEmpty([Dim Date].[Date].CurrentMember.MemberValue) 
  OR [Dim Date].[Date].CurrentMember.MemberValue = null 
 ,NULL
 ,CDate([Dim Date].[Date].CurrentMember.MemberValue) 
)

It can probably be further simplified to this:

IIF(
  IsEmpty([Dim Date].[Date].CurrentMember) 
 ,NULL
 ,CDate([Dim Date].[Date].CurrentMember.MemberValue) 
)

diagnostic

If you replace this block:

WHERE (StripCalculatedMembers(Filter(  [Dim Date].[Date].[Date].AllMembers
 ,(([Measures].[LEVEL INSTANCE none:Date:qk - lev00] >= cdate(''''' + @date1 + ''''')) 
 AND ([Measures].[LEVEL INSTANCE none:Date:qk - lev00] <= cdate(''''' + @date2 + ''''')))))
 ,[Dim QA Form Section].[System - Form].[Form].&[Group]&[Form1])

With this:

WHERE ([Dim QA Form Section].[System - Form].[Form].&[Group]&[Form1])

Does it now run faster? Filter is a function that should be used sparingly so I'm just thinking it'd be useful to try the above to see if it is the problem?

MDX Query Optimization -- Insert into SQL Table

1 Answers