Hi,
Trying to filter out measure group rows for various
conditions of different dimensions to do a sum of a measure
This is to be used in a cube calculation (mdx expression), not a mdx select statement.
The conditions for the date dimension is <>certain date and <=certain date, and hence the filter returns all member except the matching ones, which is a large set.
sum(
nonemptycrossjoin(
(filter(descendants([Purchase Date].[Fiscal Hierarchy].Members, , leaves),
([Purchase Date].[Fiscal Hierarchy].CurrentMember.Name <> "yyyy-dd-dd"
and [Purchase Date].[Fiscal Hierarchy].CurrentMember.Name <= "yyyy-dd-dd")))
,
(filter(descendants([Ship Date].[Fiscal Hierarchy].Members, , leaves),
([Ship Date].[Fiscal Hierarchy].CurrentMember.Name = "yyyy-dd-dd"
or [Ship Date].[Fiscal Hierarchy].CurrentMember.Name > "yyyy-dd-dd")))
))
,[Measures].[Test Measure]
The nonemptycrossjoin takes a long time to return results.
Is there a better way to do a not(conditions).
Or a better approach to filter measure group rows for
different conditions(and, or) of dimension memebers.
Using exists(filter1, filter2, 'Measuregroupname') does not return the expected results.
Also there are more than 2 dimensions to be filtered.
If the above statement is put in a subselect, it works faster,
but to use it in a cube calculation, can't use the 'create subcube' with the
subselect query inside a create member statement.
How can 'create subcube' be used in the Script in Cube calculations?
Regards
A couple of clarifications - is there a single "certain date" or multiple, and is the name pattern "yyyy-mm-dd", rather than "yyyy-dd-dd"?
Assuming that your Date [Fiscal Hierarchy] is chronologically ordered, it seems more efficient to directly compute the desired range of dates, rather than using Filter() - if the "certain date" is always going to be a member of the hierarchy.
|||Thanks for the quick reply.
Sorry that was a typo.....it is in yyyy-mm-dd format.
'certain date' is like the fiscal year end date.
So the [Test Measure] must be added for all measure group rows
where the above conditions for Purchase date and Ship Date are met.
Regards
|||Here's my interpretation of your requirement in the Adventure Works context, using [Date], [Ship Date] and [Sales Amount]. The date selected is "July 1, 2002", which is the fiscal year start:
>>
With
Member [Measures].[CertainDate] as "July 1, 2002"
Member [Measures].[SalesTest] as
Sum({NULL:StrToMember("[Date].[Fiscal].[Date].["
+ [Measures].[CertainDate] + "]").PrevMember} *
{StrToMember("[Ship Date].[Fiscal].[Date].["
+ [Measures].[CertainDate] + "]"):NULL},
[Measures].[Sales Amount])
select {[Measures].[CertainDate], [Measures].[SalesTest]} on 0
from [Adventure Works]
--
CertainDate SalesTest
July 1, 2002 $157,608.37
>>
Based on this entry in Mosha's blog, there's no need for NonEmptyCrossJoin():
http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx
>>
...
We have put logic inside the MDX query optimizer to recognize the cases when the empty cells can be eliminated from the set automatically. So instead of using AS2000 style of formula
SUM(NonEmptyCrossJoin(Descendants(Product.CurrentMember, Product.[Product Name]), Descendants(Customer.CurrentMember, Customer.[Customer Name]))in AS2005 it should be written as
SUM(Descendants(Product.CurrentMember, Product.[Product Name]) * Descendants(Customer.CurrentMember, Customer.[Customer Name])))...
>>
|||Hi:
I don't have an easy answer to your question for general performance improvement. However, I did not one thing about your filter statement. You specify the set to fileter on using the DESCENDANTS() function:
descendants([Purchase Date].[Fiscal Hierarchy].Members, , leaves)
I don't think you need to use the DESCENDANTS() function to specify your set. Perhaps using a set definition which doesn't use DESCENDANTS() will help perfromance. You might try a set definition which refers to all the members at the leaf level with the correct level reference such as:
[Purchase Date].[Fiscal Hierarchy].<leaf level name>.Members
Hope this helps - PaulG
|||
That worked great.
The combination of using * instead of nonemptycrossjoin
and avoiding descendants works well and query returns results very quick.
There is one problem though.....on running the query for
another measure (amounts), it gives the following error.
"The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
Is there a known limitation with sum()."
The first measure was the row count measure.
Regards
|||
Hmm - this error was mentioned before in the forum - don't know its current status. But you can try replacing * with nested Sum(), like:
>>
With
Member [Measures].[CertainDate] as "July 1, 2002"
Member [Measures].[SalesTest] as
Sum({NULL:StrToMember("[Date].[Fiscal].[Date].["
+ [Measures].[CertainDate] + "]").PrevMember},
Sum({StrToMember("[Ship Date].[Fiscal].[Date].["
+ [Measures].[CertainDate] + "]"):NULL},
[Measures].[Sales Amount])),
FORMAT_STRING = 'Currency'
select {[Measures].[CertainDate], [Measures].[SalesTest]} on 0
from [Adventure Works]
>>
