Assume the following parent-child dimension:

ALL_XY
------Leafmember1
------Leafmember2
------Leafmember3
------Leafmember4
------Leafmember5

To sum to the [ALL_XY]-level, we can use the following MDX formula:
[X] + [Y] in the 'All Member Formula' section.

No problem here...

------

B U T:

how will I sum X and Y if I want to say the following (in other words
both X and Y sums to certain weights of their leaf members):

X=(Leafmember1 + (0.67*Leafmember2)) --- Equation 1, say

*Note: by default the formula X = (Leafmember1 + Leafmember2) always
applies due to the hierarchy. I don't want this formula. I want to
apply equation 1 and equation 2 (below).

and say

Y=(Leafmember3 + (0.5*Leafmember4) + (0.2*Leafmember5)) --- Equation 2,
say

=====
THUS:
=====
1. How can I have different (weighted) rollup formulas for X and Y?
2. Where can I do it in the MS-OLAP dimension editor (for a certain
dimension)?
3. Can it be done in the 'Custom Rollup Formula'? I thought that any
formula here will apply to X and Y. I want distinct rollup formulas for
both X and Y. So, can I even hope to get equation 1 and 2 done in the
dimension editor?


Any help in this regard will be greatly appreciated.

If you're using AS 2005, try adding a "Unary Operator" column to the
dimension table - an example is in the Organization dimension of
Adventure Works, as discussed in Mosha's blog:

http://sqljunkies.com/WebLog/mosha/a...3/27/9723.aspx

In the scenarios of corporate reporting it is typical to have
organizational structure where child organization reports up to its
parent only percentage of its value, because they are only partially
owned by the parent. In Analysis Services 2000 this would be best
implemented by defining the following cell calculation

Aggregate(Ogranization.CurrentMember.Children,
Val(Ogranization.CurrentMember.Properties(”Weight” ))*Measures.CurrentMem
ber)

Where Weight is defined as member property of the Organization which
holds the value of percentage of ownership to the parent. In Analysis
Services 2005 this can be done natively. What we have done is to extend
the notion of unary operators. Previously, unary operator could have
been only one of the following: +, -, *, / and ~.

In Analysis Services 2005, it is possible to put inside unary operator
any numeric value, which will interpreted as weight to multiple by
before aggregating up. Typically this will be a number between 0 and 1,
but it doesn't have to. Now, the unary operator “+” is just a private
case of weight “1”, unary operator “-” private case of weight “-1”, and
unary operator “~” is private case of weight “0”. Sample database
Adventure Works has example of weights as unary operators on the
Organization dimension. Take a look how nice they look in UI, you don't
really even need to browse unary operators binding, because UI is smart
enough to automatically show the weights right next to the icon.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak

Thank you for this reply. Just a few things:

1. I'm not on AS 2005. I'm on SQL Server 2000 and Analysis Services
2000. Therefore, I don't think I can use this functionality ("unary
operators") but I may be wrong.

2. The users won't want to have a weight assigned to their buckets (as
shown in your GUI). They will still want to see the full value there
when lookin at the cube (i.e. Unary Operator 1 throughout for all leaf
members of the hierarchy). Only when the summing into the non-leaf
father node happens, do they want the weight to be effective (i.e. in a
formula). It is this formula (and specifically WHERE it can be be
implemented), that I'm battling with.

Any ideas?

I'll be very grateful for ANYTHING useful.

Rgds,
Graeme

I am not completely sure how to solve your problem, but you CAN use
Unary Operators in AS2K. Just open up your dimension editor, click on
a level in your dimension, and the go to Advanced properties. Unary
Operators is the last property.

V

Hi Graeme,

To answer the 2 points that you raised:

1) Unary operators are supported in AS 2000, but since weights can't be
assigned to them, they may not serve your scenario well. However, Mosha
also suggested (in the same blog entry) an approach for AS 2000, using
calculated cells, which would work (assuming you have AS 2000 Enterprise
Edition). This entails a "Weight" member property, stored in a dimension
table column.

2) Maybe there's some confusion about what the GUI is showing (which is
dimension developer info) - end-users would not see these weights, and
the weights would only apply when leaf values roll up to parent members.
The leaf values themselves should appear unmodified to end-users.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

What about using custom rollup and iif() function to branch calculation for X
and Y.

"graemevn" wrote: