I have a scenario where I am returning 52 weeks of data, but for one calculation I only want to return the latest week. I am getting poor performance because it is scanning all partitions in the 52 week result set. I would like it to only scan the last partition if possible.
I tried writing something like
MEMBER [Measures].[Number Locations] AS ( iif([Date Dimension].[Fiscal Year].CURRENTMEMBER.MemberValue = [FiscalLastDay].Item(0).Parent.Lag(1).Item(0).MemberValue , [Measures].[Locations Sold] ,Null) )
But it didn't seem to help.
Locations sold is a calculated member
SCOPE([Measures].[Locations Sold]); THIS = IIF(ISEMPTY(measures.units),NULL, COUNT(NONEMPTY([Locations Sold].[Locations Sold].[Location Dimension ID].members,measures.units))); END SCOPE;
FiscalLastDay is calculated as follows
CREATE HIDDEN TodayDate = Format(Now(), "yyyyMMdd"); CREATE HIDDEN SET [FiscalToday] AS Filter([Date Dimension].[Fiscal Year].[Day Name].Members, [Date Dimension].[Fiscal Year].MemberValue = StrToValue(TodayDate)); CREATE HIDDEN SET [FiscalLastDay] AS [FiscalToday].Item(0).Lag(1);
Please look into the following articles:
MDX and partitioning: http://sqlblog.com/blogs/mosha/archive/2007/12/18/mdx-and-partitioning.aspx
Query Performance Tuning in Microsoft Analysis Services: http://www.packtpub.com/article/query-performance-tuning-microsoft-analysis-services-part1
TechNet Community Support