Nonemptycrossjoin alternative

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]

>>

NONEMPTYCROSSJOIN : how does it work with linked measure groups ?

Hi,

I am experiencing a strange behaviour with both NONEMPTYCROSSJOIN() and NON(CROSSJOIN()) Mdx statements when working with linked measure groups.

Bascially, I have created a very simple "regular" MOLAP cube on SSAS2005 with one fact table and 3 dimensions. I have one single Measure Group for this cube.

Now, if I create an empty cube (no data source view) and add the measure group of my first cube as a Linked Measure Group, it seems like the Mdx function NonEmptyCrossJoin() never returns anything whereas it does on the underlying cube.

Same problem occurs when using NonEmpty(CrossJoin()).

Is there anything I need to parameter to have the NonEmpty baheviour work correclty on the linked measure group ? I've read there's a "NonEmpty behaviour" parameter for calculated members, but I'm not even using calculated measures.

Is such a behaviour a known issue ?

Do you need a more detailed example? if so, on which database ?

To give some backgroung, I have first noticed this problem after migrating a virtual cube from SSAS 2000.

Many thanks in advance

Rgds,

Francois

I eventually found an answer to this.

There was obviously some type of confusion in the migration of the virtual cube (which is based in SSAS2000 on 3 physical cubes) because not all of the sub cubes' dimensions where used.

I have recreated by linked measure groups manually, removed the unused dimension and the nonemptycrossjoin works fine now.

Hope that helps anyone having the same type of problem.

- F.

Nonadditive Measure in Standard Edition

How do I define a measure to be nonadditive in Standard Edition? If I set the AggregateFunction property to None, I get this error when trying to deploy:

Aggregate function None is not allowed in Standard server edition.

This is for a percentage measure based on a named calculation in my data source view. It obviously cannot be aggregated.

Any workaround for this?

-Larry

Actually, percentage calculations are perfomrmed best by cube calculated members - which are always non-aggregatable. If you compute percentage in DSV - then it will only be available at the leaves level if you were to use None aggregation function. With calculated member Ratio=Sales/Cost - you will get it computed correctly at every level in the cube.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Thanks, that worked great. I actually had a calculated measure defined for this but I called it Average % and didn't put 2 and 2 together that it gave me everything I needed!

So on the flip side, I have another calculated measure that is an Average # of Days, and I don't want this to show up at the leaf level because it's just the same as the # of Days measure. Is there any way to suppress the value at the leaf level but have it at the aggregation level?

|||

Yes - this is easy. You can define it like following:

CREATE [Average Number Of Days] = <your expression>;

(Date.Date.Days.MEMBERS, [Average Number Of Days]) = NULL; // This will supress it at the Days level.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

You're right, that was easy -- but only after I fully understood what it was doing. MDX is taking me a little while to get, but it's starting to sink in.

Thanks again for your help!

-Larry

Non-additive measure - Actual and Average

I am working on a project at a manufacturing client. The measure I'm having trouble with is LeadTime (the number of days from manufacturing schedule to completion). It is used in a calculation to determine over/under inventory levels.

I need it to use the actual LeadTime for the calc at the SKU level and the average LeadTime at all of the aggregate levels.

Thanks for the help,

Dave

Hello! I am not sure about what you mean with SKU level but if you have two dates in the fact table, ManufacturingScheduleDate and ManufacturingCompletionDate, you can make a named calculation in the data source view(Analysis Services 2005) or a calculation in the ETL-process with SSIS.

Use the TSQL function DATEDIFF() for that and the difference between the MScheduleDate and the MCompletionDate.

I am not sure about your problem with the averages and what you are doing averages of?

You should be able to solve this with the MDX AVG() function.

HTH

Thomas Ivarsson

|||

SKU is the lowest level of data...the individual product being manufactured and sold. The LeadTime does not need to be calculated it is already determined for each product.Here is a table that I hope helps:

LeadTime

Product

Actual

Average

1001

3

4

1002

4

4

1003

5

4

Sub-total Sum

12

Sub-total Avg

4

When I set the measure LeadTime to Sum the calculation that uses LeadTime is correct at the product level, but wrong at the aggregate level.If I set the measure to Average then the calc is wrong at the product and right at the aggregate level.

I need to show the cube data with LeadTime as actual at the product level and average at the aggregate level.

Dave

|||

Hello! Can you also indicate the expected values in the table for Actual and Average Lead time?

From what I have seen with other clients is that you have a manufacturing order id for each production of a product.

It is not a part of your table but can it be a part of the problem?

Regards

Thomas Ivarsson

|||

Here is a little description of the table from my last post.Three product ID’s (1001, 1002, 1003) with actual LeadTime for each product in days (3, 4, 5 – respectively).

The LeadTime measure is used with other measures (On-hand, Daily demand, Safety stock, etc.) in an inventory over/under calculation.But, LeadTime is the only non-additive measure.Because of that the calculation is correct at the Product ID level (3, 4, 5), but wrong at the aggregate level (12).If I set the measure to Average LeadTime at the Product level is wrong (4, 4, 4), but the aggregate is correct (4).I’m looking for a solution that gives me the correct calculation at all levels

This project is only dealing with inventory levels.Orders are not a part of the calculation except as a part of the Daily Demand figure.