David Beavonn


The aggregate "Bad Agg" in the query below is causing no results to be returned.

Notice that there are two members in the set of "Bad Agg". If I remove one or the other from the set (and create a set of just one member) then I get valid results.

Does anybody know what might be happening We are on the latest SP (9.00.3042).

Note that SP/LU below are the "product type". And POLYSTYRENE is the "product species". It is the same species within both product types.

Code Snippet

WITH MEMBER [Product].[Product Species].[Bad Agg] AS

'Aggregate ( {

[Product].[Product Species].[Type].&[SP].[POLYSTYRENE],

[Product].[Product Species].[Type].&[LU].[POLYSTYRENE] } )'

SELECT

NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product Indexing].[All]})})) ON COLUMNS

FROM [Sales Budget and Activity]

WHERE ([Measures].[Sales],

[Product].[Product Species].[Bad Agg],

[Time].[Time F].[Year].[Y 2007])





Re: Results from Aggregate() are wrong

Deepak Puri


Does the scenario in this thread apply (like cell calculations on measure) - if so, you could try setting SCOPE_ISOLATION=CUBE







Re: Results from Aggregate() are wrong

David Beavonn

I don't think mine is related. I don't get an error, just invalid results.

I suppose it would help if I can repro in Adventureworks.

I was hoping somebody would say that Aggregate() used as a slicer in a WHERE clause is not defined to return valid results or something like that.







Re: Results from Aggregate() are wrong

Deepak Puri

As explained in Mosha's blog, Aggregate() used as a slicer in a WHERE clause will effectively be replaced by a set of the same members (in AS2005). So the classic multi-select problem that could arise is when HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product Indexing].[All]})})) includes a calculated member which uses (in your case) [Product].[Product Species].CurrentMember directly.




Re: Results from Aggregate() are wrong

David Beavonn

The measure I am using is just a simple base measure, not a calculation. CurrentMember doesn't become a concern in this example unless the CurrentMember of [Product].[Product Indexing] is being thrown off for some internal reason.

The Product Species hierarchy is unnatural with two attributes.

The Product Indexing hierarchy is natural with two different attributes.

As I understand this, the slicer (multiselect on the second level of Product Species) is somehow throwing off the Product Indexing hierarchy which is used on rows. Attributes in Product Species are not related to attributes in Product Indexing - unless you go all the way back to the dimension key attribute which is not present in either hierarchy.

I've spent quite a lot of time attempting to reproduce this in AdventureWorks but am not able to since I can't find a meaningful pattern behind the problem in my own cubes. I will open a support case and see if I can get somebody to look behind the curtain.

Thanks,
David






Re: Results from Aggregate() are wrong

David Beavonn

I opened a case with Microsoft related to our inaccurate results from Aggregate().

It appears that there is a bug in SP2 that causes queries to return inaccurate results when a cube dimension has unnatural hierarchies in it.

I know that unnatural hierarchies have performance limitations but I thought they were a well-established feature of SSAS 2005 nevertheless.

My understanding of the problem is still limited. Has anybody else heard anything more about the potential they have to corrupt query results






Re: Results from Aggregate() are wrong

Deepak Puri

Interestingly, this recent thread in the SQL Server OLAP newgroup discusses another problem with unnatural hierarchies in SP2:

Message from discussion SP2 SQL 2005 breaks Unnatural Hiearchies

JimBolleb...
View profile
View profile
May 29, 2:52 pm
Newsgroups: microsoft.public.sqlserver.olap
From: Jim Bolleboom
Date: Tue, 29 May 2007 11:52:56 -0700
Local: Tues, May 29 2007 2:52 pm
Subject: SP2 SQL 2005 breaks Unnatural Hiearchies
Hi Vilas

I have been struggling with this one for the last couple of months. I have had a call open to MS Support but it seems that this issue has been ported (along with all others) to the next version of SQL. That's the next version not the next SP! SP2 might have been a little more than what you would consider a standard SP. There were some fundamental changes and one of these is causing your porblem. If this is the same issue as mine then you need to ensure that you do not have any Unnatural Hiearchies. OWC cannot handle them. They send MDX that causes your problem. Make all Unnautal Hiearchies into nautural ones and the porblem goes away.

Obviously the problem with that is that some Hiearchies just are not natural. I had to redesign about 50 % of my data warehouse to ensure that I forced each Hiearchy into natural.

I'm looking for a third party alternative to the OWC sooner than I had expected. If you know of any please let me know.

Regards
Jim