NonEmpty(Crossjoin) + Format(Now(), "ww")

Question

NonEmpty(Crossjoin) + Format(Now(), "ww")

MartinIsti

Mr or Mrs. 500

Points: 540
More actions
August 16, 2007 at 2:33 am

#118284

Dear fellow AS2005 users,
I ran into a performance issue while migrating from AS2000 to AS2005. I have a calculated member which gave different results in 2000 and in 2005.
filter(NonEmptyCrossjoin(descendants([Product].[Brand].currentmember, [Product].[Brand].[Product_name])), [Measures].[Volume]>0).count
I found some documents on the internet (on the Technet MS site, too) and I replaced the NonEmptyCrossjoin function with the NonEmpty. In the first place it is more or less good. Good, because the values are now correct, but the query runs much-much slower:
Query:78 rendering:16531 ms
Query:263203 rendering:47 ms
I tried to figure out how I can optimize that MDX expression but so far I haven't succeeded. I have read through Mosha Pasumansky's article about it (http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx), but cannot use its content effectively.
Another thing is a problem with the Format VB function. I had an expression in AS2000 which does not work in 2005:
Format(CStr(Now()), "yyyy")
the result in 2000: 2007, but in 2005 it gives yyyy
I removed CStr from the expression:
Format(Now(), "yyyy") - so it gives me 2007, seems everything's ok, but when it comes to weeks, it does not work any more
Format(Now(), "ww") - ww (I tested this function in Visual Basic directly and that "ww" parameter is okay.)
If anyone has an idea about them, please let me know!
Thanks in advance!
MartinIsti

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 1

First, is it improtant that you filter out negative values (are they present??) If you're simply looking for products with Volume, try:

NonEmptry ( Descendants( [Product].[Brand].currentmember, [Product].[Brand].[Product_name] ), [Measures].[Volume] ).Count

Assuming this returns items negative values as well (which isn't good), try this form:

Filter ( NonEmptry ( Descendants( [Product].[Brand].currentmember, [Product].[Brand].[Product_name] ), [Measures].[Volume] ) , [Measures].[Volume] > 0&nbsp.Count

Notice I overspecified [Volume], since I wasn't sure what your default measure was for your cube, and you may have been evaluating different facts.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

On the formating side, have you tested with different casing on the parms (e.g. YYYY instead of yyyy, same with weeks)?

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Not sure if I'm over simplifying the requirement, or maybe assuming AS2K5 is smarter than it is when dealing with empties but, is the Non empty even required? if you're using a filter, then by definition, an empty won't match the filter criteria e.g.

(filter (DESCENDANTS( [Product].[Brand].currentmember, [Product].[Brand].[Product_name]), [Measures].[Volume] > 0)).COUNT

Steve.