Follow below mentioned steps:
-Set Profiler with Query Begin and Query End Event, Run Profiler Trace and Execute MDX which you need to tune.
-Stop Profiler trace and save trace to table in RDBM
-What you need to do is run the following SQL query against the table in which you had saved Profiler Trace
Select * from <table_name> where eventclass=9 or eventclass=10 order by connectionid, currenttime
Once you’ve got that and have identified the long running query or queries, run the following:
Select sum(duration) as SE_TIME from <table_name> where eventclass=11 and connectionid = <Target_connectionid> and rownumber > <rownumber_for_eventclass=9> and rownumber < <rownumber_for_eventclass=10>
Compare the SE_TIME value with the duration value for Eventclass=10 on the long running query. If it’s greater than 30% of the duration, the bottleneck is in SE otherwise it’s in FE.
Select * from master.dbo.cxtable where eventclass=9 or eventclass=10 order by connectionid, currenttime
RowNumber EventClass Duration ConnectionID
1 9 NULL 36
5055 10 216516 36
Select SUM(duration)as SE_TIME from master.dbo.cxtable where eventclass=11 and connectionid = 36 and rownumber>1 and rownumber<5055
if setime >30%of eventid 10 Duration, then culprit is SE
30% of event id 10 (30% of 216516) = 64954.8
96203 > 64954
In this case culprit Storage Engine.