# lookup value dependant on hierarchy

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # lookup value dependant on hierarchy

Hi there,

hope somebody can help me with my problem.
I have a sales calculation cube with the following (simplified) set up:

Dim1: Measures
-net price
-list price
-rebate

Dim2: Products
- Total Summer
- standard
- high performance

Dim3: Customers
- Channel 1
- Customer A
- Customer B
- Channel 2
- Customer C
- Customer D
- Customer E
...

Each channel should have a different rebate.
The rebate for each customer depends on its belonging to a channel.
Rebate differ also in the different product groups.

Question: How can I create rule which puts in the correct rebate on each customer depending on its belonging to a channel?

I would be very grateful for any hint!

Greetz
Harry
• Just to understand, the rebate depends on 2 dimensions?

Channel A, Product A, 10%
Channel A, Product B, 5%
Channel B, Product A, 7%
Channel B, Product B, 20%

Is that right?
• Yes exactly!
It's a little simplified. A time dimension has to be added too.
• There are fubnctions like e.g. PALO.EISCHILD that you can use in rules. So you can check if current customer is child of channel A or B. and set the right rebate.

Like (simplified):
rebate=IF(PALO.EISCHILD("DB","Customers",!'Customer,"Channel A")=1,50,60)

Holger
• Hi,

I was thinking maybe you could have your own rebate cube with the dimensions "Customer Channels" and Products.

In your "normal" cube you could set the Channel as a attribute of the customer and you could get the right rebate with a rule (simplified):
PALO.GETDATA("DB","Rebate","Customer Attribute Channel", !'Product',!'Time')

This way your rebate can change and your rules would stay the same.

I hope you can understand what I'm trying to explain.
• Thanks Holger,
but this way I have to set a rule for every combination of channel and product and I have to put in fixed values, but the rebates can change

At the end there should be a planning sheet where I can manipulate the basic discounts f.ex. for channel car dealer and for each product group. This should then be calculated for all customers.
• Morning dibe0014,
I also thought of a second cube and setting up an attribute in the main cube.
With this formula palo simply puts the value an all customers. I could not really restrict the target. There has to be some sort of a condition I think.
• Hi again,

Originally posted by bluenote
Morning dibe0014,
I also thought of a second cube and setting up an attribute in the main cube.
With this formula palo simply puts the value an all customers. I could not really restrict the target. There has to be some sort of a condition I think.

Well, you could substitute the hard coded percentage values with a PALO.DATA that looks up the rebate in a different cube with got

Channel Dimension (only Channels)
- Channel 1
- Channel 2
...

and measure dimension
- rebate
- rebate 2
or something like this

You can set up a view to edit them there.
The origin cube rule would be like
IF(PALO.EISCHILD("DB","Customers",!'Customer,"Channel A")=1,PALO.DATA(DB,Rebates,Channel 1, rebate),10)

where 10 is the standard rebate if customer is not child of Channel 1.

Here is antother one. Set up an string attriubte channel for customer dimension. Set up a rule that automatically fills the attribute values with parent name of customer (e.g. for Customer A its Channel1). In origin cube then use a rule that looks up this attribute and sets the rebate as per rebate cube.

You also could add an attribute to channel dimension and set a rebate for every Channel element. You can do this by using string attributes and convert them to number when you use them in rules. This is not a very elegant.

Many possibilties there.

Sometimes it helps remodelling from scratch too. I've thrown away many data models in my life .

Hope this contains some ideas for you. Good luck.

Holger

The post was edited 2 times, last by h_decker ().