I have an MDX query referencing a SQL Server Analysis Services 2008 R2 Enterprise Edition cube that from a functional standpoint returns the data that is needed accurately and correctly. It takes 5-10 minutes however to execute this query which is unaccaptable for our end users executing this query within an SSRS report.
The query is listed below:
With Member [Measures].[CurrentPaidAmount] as
Sum (
Tail(nonempty(descendants([Valuation Date].[DateHierachy].currentmember,4),[Measures].[SaleCount]),1),
[Measures].[CurrentTotalPaidAmt]
)
Member [Measures].[PreviousPeriodPaidAmount] as
Sum (
Tail(descendants([Valuation Date].[DateHierachy].currentmember.prevmember,4),1),
[Measures].[CurrentTotalPaidAmt]
)
Member [Measures].[YTDCurrentPaidAmount] as
([Measures].[CurrentPaidAmount] - [Measures].[PreviousPeriodPaidAmount])
select non empty
( {[Valuation Date].[DateHierachy].[Year]}, {[Valuation Date].[Year].children},
{[Valuation Date].[Month].[All]}, {[DimLocation].[State].[All]},
{[DimSeller].[Store].children}, {[DimProduct].[ProductCode].children},
{[DimProgram].[ProgramLine].children ) on rows,
[Measures].[YTDCurrentPaidAmount] on columns
from
[CB_Sales]
This underlying fact table stores the [total paid amount] measure in a cumulative fashion meaning on the last day of the month, the total dollars paid for a particular product since the beginning of time are calculated. This would imply that at the end of each month, the month's total paid dollars are added to the previous month's amount in order to create a grand total of all paid dollars for the product in question since the beginning in time.
My requirements are to calculate the total amount paid for each product solely for each individual year.
Consider the following sample dataset in which the month to date total paid dollars are listed for a particular product:
12/2010 Product A: $25.00
11/2011 - Product A: $50.00
12/2011 - Product A: $100.00
1/2012 - Product A: $150.00
2/2012 - Product A: $160.00
3/2012 - Product A: $200.00
If I wanted to obtain the total dollars paid solely for the 2011 calendar year for Product A above, I would obtain the total calendar to date paid dollars for Product A as of 12/2010(which is $25.00) and subtract this amount from the total paid dollars valued as of 12/2011 (which is $100.00).
Subtracting the 2 values yields a difference of $75.00:
($100.00 - $25.00) = $75.00
This would indicate that a total of $75.00 was paid for Product A solely within the 2011 calendar year.
With this said, getting back to the MDX query above, I have created 2 calculated members to perform this calculation.
Member (1):
[Measures].[CurrentPaidAmount]
This calculated member gets the last, non empty total dollars paid amount logged for the current calendar year in question based on the currentmember of the [Valuation Date].[DateHierachy].[Year] attribute. Being that for the current 2012 calendar year the last month to have total dollars logged is June 2012, the calculated member would need to be smart enough to identify that the [June] 2012 month member within the date hierarchy is the specific month the total paid dollars should be taken from.
It is important that I return the [Year] attribute of the of the date dimension in question as they would like this member displayed within a tabular report.
Hence, I have included the following set within the calculated measure that will take the current year value based on what is returned back to the client within the resultset, and navigate down the date hierarchy until I get the last day within the calendar year in question that has a valid total paid amount as shown below:
Tail(nonempty(descendants([Valuation Date].[DateHierachy].currentmember,4),[Measures].[SaleCount]),1),
My reasoning was that if the user selects all years within the connected report, for each year this particular calculated member would obtain the last non empty total dollars paid amount for the calendar year in question and allow me to subtract that from the previous year to get the total paid dollars that were incurred solely within the calendar year in question.
Now, the complete MDX query I listed above does return the correct data when executed. It takes forever to execute however taking more that 5-10 minutes to execute. The underlying fact table contains 64 million records.
What is strange, is that if I remove the nonempty() clause from the set within the first calculated member like so, the modified query only takes 30 seconds to execute:
With Member [Measures].[CurrentPaidAmount] as
Sum (
Tail(descendants([Valuation Date].[DateHierachy].currentmember,4),1), [Measures].[CurrentTotalPaidAmt]
)
I can only assume that the nonempty() clause is bogging down performance of the query due to the calculated member needing to evaluate each intersection of the date member with the [Measures].[SaleCount] measure.
Is there any way I can rewrite the query above to perform better, faster, and more efficiently preferably in under 30 seconds instead of minutes?
I have default aggregations applied to the cube and it is partitioned into several cube partitions.
I am a newbie to MDX and so I would appreciate your suggestions.
Thank you for your time!
