I have a virtual profitability cube with dimensions
Time, Customer (Parent-Child), Product (Parent-Child) and a measure
Net turnover.
I have a Sales Incentives Table that contains a rate of sales
incentives but at a higher level than sales( level year (time),parent
level (Customer) and parent Level (Product) ).I need to apply the rate
for all children of the set (time+customer+product)
I need to create a measure incentive amount=net turnover* incentive
rate. At all levels off profitability cube.
First I create a Sales Incentive Cube with measure rate but rate is
aggregated.

Anyone has an idea ?

Many thanks
Agnès

Here's some ideas - see if they match your situation:

- Create cube from Sales Incentives Table that shares same 3 dimensions,
so that it can be added to virtual cube.

- Since this fact table is not at leaf level of dimensions, some
adjustments are needed to load data in cube:

- Time disabled below year level, loaded at year level.

- Customer and Product: permit "Members with Data".

- Create calculated Incentive Rate by navigating to appropriate tuple,
where there is a single value.

- Calculate Incentive Amount by aggregating Net Turnover *
Incentive Rate over all (non-empty) leaf nodes.



- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for your response. I had already developped the first points But
could you detail how:
Create calculated Incentive Rate by navigating to appropriate tuple,
where there is a single value. (with mdx syntax, how ? , you have a
sample ?) I just begin in olap development.

- Calculate Incentive Amount by aggregating Net Turnover *
Incentive Rate over all (non-empty) leaf nodes.

I really appreciate your help !
Read you soon...




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

For navigating to the parent Incentive Rate, I'm using an idea from an
earlier post by Chris Webb - hopefully, it will work in your case:

'(Filter(NonEmptyCrossJoin(
{[Measures].[Incentive Rate]},
{Ancestor([Time].CurrentMember, [Time].[Year])},
Ascendants([Customer].CurrentMember),
Ascendants([Customer].CurrentMember)),
NOT([Customer].CurrentMember is
[Customer].CurrentMember.DataMember OR
[Product].CurrentMember is
[Product].CurrentMember.DataMember)).Item(0))'

Then [Parent Rate] is used when aggregating [Incentive Amount] from leaf
level [Net Turnover] data:

'Sum(NonEmptyCrossJoin({[Measures].[Net Turnover]},
Descendants([Time].CurrentMember,,LEAVES),
Descendants([Customer].CurrentMember,,LEAVES),
Descendants([Product].CurrentMember,,LEAVES)),
[Measures].[Net Turnover] * [Measures].[Parent Rate])'

There may be some way to optimize the aggregation without going to leaf
level, but it gets complex already.


Here's the post on how to find "Members with Data":

http://groups-beta.google.com/group/...rver.olap/msg/
0ee6852b05fec13d?dmode=source
Well, working with the knowledge that if you use the .DATAMEMBER
function on
a leaf member or a member without a datamember it will return the member
itself, you can construct a query like this:

SELECT MEASURES.MEMBERS ON 0,
NON EMPTY
GENERATE(
FILTER(EMPLOYEES.MEMBERS,
NOT(EMPLOYEES.CURRENTMEMBER IS EMPLOYEES.CURRENTMEMBER.DATAMEMBER))
, {EMPLOYEES.CURRENTMEMBER.DATAMEMBER})
ON 1
FROM HR
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi
So I created a calculated member [Rate] on my cube 'Sales Incentives'
I created a virtual cube 'profitability' based on both 'Sales' and
'Sales Incentives' cubes.
I can see my calculated member [Rate] with parent dimension 'Measures'.
But I am not allowed in the assistant of AS to create a calculated
member using another calculated member [Measures].[Rate]. Do yo have an
idea ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Not sure what the problem exactly is - did you import the [Rate]
calculated measure into the virtual cube? If so, then it should be
available in MDX builder, when defining a new calculated measure in the
virtual cube.

Also, there was a duplicate [Customer].CurrentMember entry in my
formula, which should be [Product].CurrentMember:

'(Filter(NonEmptyCrossJoin(
{[Measures].[Incentive Rate]},
{Ancestor([Time].CurrentMember, [Time].[Year])},
Ascendants([Customer].CurrentMember),
Ascendants([Product].CurrentMember)),
NOT([Customer].CurrentMember is
[Customer].CurrentMember.DataMember OR
[Product].CurrentMember is
[Product].CurrentMember.DataMember)).Item(0))'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello !

Well, my [rate] is good in my [Sales Incentives Cube], I imported the
calculated member in my virtual cube. I see it in the member list but
not in my MDX Builder.
I wonder why. I go on testing...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!