Hi,

I have encountered the following problem:

In a dimension with parent-child relation I have define an unary
operator column.
But I don't get the expected results.

The dimension looks like this:

ID Parent ID Unary operator
---------------------------------------------------------------
Parent ~
Child1 Parent +
Child2 Parent -


In the cube I get results like these:

Level 1 Level 2 Amount
-------------------------------------------
Parent Chlid1 10
Child2 8
-----------------------------------------
SUM 2
---------------------------------------
SUM 18


Expected results would be:
Level 1 Level 2 Amount
-------------------------------------------
Parent Chlid1 10
Child2 8
-----------------------------------------
SUM 2
---------------------------------------
SUM 2

It seems like the unary operator is considered only at leaf level
sums. When rolled-up it is ignored (like it is allways '+')


This only happens when the dimension with parent-child relation is
involved in many-to-many relationship.
My case looks like this: DimReportItem (PK ItemID, ParentItemID) is
connected to intermediate fact table FactReportItemAccount(FK ItemID,
FK AccountID) which is connected to dimension Account(PK AccountID)
which is finally connected to fact table AccountData(FK AccountID).

SQL 2005 Developer, SP1.

Regards, Janez.

I guess AS ignores the many-to-many relationship during calculating the all
member of the MM dimension.
As a result, AS does not cosider the MM dimension (DimReportItem).

There is more detailed information on MM dimension at
http://msdn2.microsoft.com/en-us/library/ms345139.aspx.

I am also interested in this issue and hope someone has more good
information on this.

Ohjoo



"Janez" <janez.trost (AT) gmail (DOT) com> wrote