Hi,

I have seen lots of posts around this topic - but am not sure if they
apply to this specific situation.

I have a dimension with 100 members against which fact data can be
loaded. For each of these members I have two formula members in the
same dimension. The formulas for which are as follows

iif ( [Dimension].&[x] > 0, [Dimension].[x], NULL)
iif ( [Dimension].&[x] < 0, [Dimension].[x], NULL)

So, for each 'input' member, there are two members - one showing the
value if it is positive otherwise NULL, and the other showing the value
if it is negative, otherwise NULL.
Below I will refer to them as OrginalX, PositiveX, NegativeX


The problem occurs when these members are rolled up against another
dimension. Instead of the rollup being the sum of PositiveX or
NegativeX - I am seeing the formulae above being applied to OriginalX.

I have played with the solve_order (but I dont think they are relative
in this context) and the ordering of the dimensions within the cube -
with no success.

I guess I can check of ISLEAF and do the aggregations in the
calculation - but given the total number of dimensions (11) and the
fact that we are using unary operators, this seems to be a no-go.

Any ideas?

thanks

How about defining a dimension consisting of OrigianalX, PositiveX, and
NegativeX.

Ohjoo


<noel.phillips (AT) gmail (DOT) com> wrote

The dimension does consist of those members - as below

Anchor
-> Input
-> OriginalX
-> OriginalY
->Positives
->PositiveX [iif(OriginalX>0, OriginalX, NULL)]
->PositiveY [iif(OriginalY>0, OriginalY, NULL)]
->Negatives
->NegativeX [iif(OriginalX<0, OriginalX, NULL)]
->NegativeY [iif(OriginalY<0, OriginalY, NULL)]

When the positive/negative members are aggregated against a
parent/child dimension - I see the problems as explained in my original
post.

thanks

sorry - I should have mentioned...I am aware that I could manipulate
the fact table to actually load data against those members rather than
using a formula - but I am looking for a way that does not require
that...

thanks

I hope I understand what you mean rightly.

If there are next two facts in the fact table, what do you want the result
finally?

OriginalX, 100
OriginalX, -60

If OriginalX = 40, PositiveX = 40, and NegativeX = NULL, we can consider
Custom Member Formual and unary operator as following,

Anchor
-> Input (~)
-> OriginalX (+)
-> OriginalY (+)
->Positives (~)
-> PositiveX (+) [iif(OriginalX>0, OriginalX, NULL)]
-> PositiveY (+) [iif(OriginalY>0, OriginalY, NULL)]
->Negatives (~)
-> NegativeX (+) [iif(OriginalX<0, OriginalX, NULL)]
-> NegativeY (+) [iif(OriginalY<0, OriginalY, NULL)]


If OriginalX = 40, PositiveX = 100, and NegativeX = -60, the facts need to
be manipulated before being loaded to the cube. For example,

OriginalX, 100
PositiveX, 100
OriginalX, -60
NegativeX, -60

In this case, only unary operator will be enough.

Anchor
-> Input (~)
-> OriginalX (+)
-> OriginalY (+)
->Positives (~)
-> PositiveX (+)
-> PositiveY (+)
->Negatives (~)
-> NegativeX (+)
-> NegativeY (+)


Ohjoo

"nep" <noel.phillips (AT) gmail (DOT) com> wrote

Hi Ohjoo,

I fear you have misunderstood (or I have not explained very well!)

This dimension does not have any aggregation - it is another, standard
parent-child dimension where the aggregation issue is happening.

I am seeing this.....

OrginalX PosX NegX OrginalY PosY NegY
Parent 5 5 NULL -5 NULL -5
Child 1 7 7 NULL 2 2 NULL
Child 2 -2 NULL -2 -7 NULL -7


What I want to see is this.....

OrginalX PosX NegX OrginalY PosY NegY
Parent 5 7 -2 -5 2 -7
Child 1 7 7 NULL 2 2 NULL
Child 2 -2 NULL -2 -7 NULL -7


thanks...





Ohjoo Kwon wrote:

I guess you already tested as following,

PosX = IIF(IsLeaf(PC.CurrentMember), IIF(OriginalX > 0, OriginalX, NULL),
Sum(Descendants(PC.CurrentMember,,LEAVES), PosX))

But there are too many dimensions to do so.

So my idea is to have regulare members such as PosX, PosY, NegX, NegY, and
so on, and to apply calculated cells.

For example, calculation cell definition for PosX, PosY, and so on.

Calculation Subcube: [PC].[Level 02].Members
Calculation Condition : Left(Anchor.CurrentMember.Name, 3) = "Pos" AND
StrToMember("Original" + Right(Anchor.CurrentMember.Name, 1)) > 0
Calculation Value: StrToMember("Original" + Right(Anchor.CurrentMember.Name,
1))

If you use AS 2005, I believe you can do the same things using Scope.

I did not test this fully, but hope the help.

Ohjoo

"nep" <noel.phillips (AT) gmail (DOT) com> wrote

Hi Ohjoo,

Many, many thanks - that is exactly what I was after! Hope this also
helps other people having the same issue.

Once again, thank-you!