In AS2000 we were using following query as a basis for product report:
select {[Measures].[Stock], [Measures].[Sales Qty YTD], [Measures].[Sales Qty LW]} on columns,
NonEmptyCrossJoin([Hierarchy].[Sub Category].Members, [SKU].[SKU Name].Members, [Supplier].[Short Name].Members) on rows
from eSalesActStock
where [DOp Calendar].[OpYear].[2006].[08.February].[Week 32]
It was used to show all products either on stock, or sold this year or both.
It was getting approx 9k rows as result
The cube used was a virtual one, merging stock and sales cubes.
The where clause is only to put calculated members from sales cube in context.
Dimensions are crossjoined only because we need them for grouping and on report layout. Every SKU has a supplier and is attached to assortment hierarchy.
In AS2005 we tried to recreate the dataset, and this is what we ended up with:
SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )
Unfortunately this query only results in 1.1k rows result, with the same data and parameters.
We have played a lot around with it, even trying to use nonemptycrossjoin but the results remained the same.
We did find out, that after removing where clause, the query retrieves 9k rows, which seems to be correct amount, but the data are useless, as calculated members do not have date context.
The only thing that differs from the AS2000 is that in AS2000 we had stock figures artificially tied to a date, to see the stock figures if you chose any date dimension filters. In AS2005 this is no longer necessary, so we just left date dimension and stock measure group unrelated. Can this be the cause of problem ? It seems unlikely, as crossjoin is made on different dimensions...
Are we missing something ? It used to work as the result of crossjoin was not related to where clause on non crossjoined dimension, now it seems that date dimension actually influences the results of the cross join...
Maybe there is a way to solve or walk around this problem ?
Any feedback appreciated :)
Thanks,
Marcin
Hi Marcin,
The role of the date wrt. stock data isn't clear to me; but since the NonEmpty() returns the right results without the where clause, try adding the default date as the filter:
>>
SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS,
{[DCalendar].[DOp Calendar].DefaultMember} ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )
>>
