Rules with virtual hierarchies

[Andero]

Hi all,

I need to retrieve values from a cube using virtual hierarchies, but i'm struggling to write the DB function.

What I have is as below:



I want to get data from cube 1 to cube 2.
And i need to use the virtual hierarchy i created in the dimension Products

According to this guide https://cubewise.com/blog/how-to-work-w ... -in-rules/

my rule should be DB('cube1', .. , .. , 'Category':!Dashboard_Products,'value')

But i think it's wrong because i should reference the first dimension, can someone explain me how the syntax should be?

Many thanks

[MarenC]

Hi,

I think the first issue is that it isn't a one to one relationship. I.e. you have multiple products with the same category.

So how do you know which product value you need to retrieve?

ps you might want to be a bit more specific about the exact issue you are getting, is the value unexpected or is the rule erroring etc

Maren

[gtonkin]

Have you looked at CellValueN?
Probably the right function to use:
https://www.ibm.com/docs/en/planning-an ... cellvaluen

[JohnO]

Have you looked at CellValueN?
Probably the right function to use:
https://www.ibm.com/docs/en/planning-an ... cellvaluen

My favourite topic. I'm happy for someone to correct me here but this is my experience.

The great thing about CellValueN is that you can omit dimensions entirely. This can be very beneficial when writing rules for multiple hierarchies in simple cases as rather than specifying all of the applicable hierarchies you can omit the dimension reference entirely. This is very beneficial as you can then add extrac hierarchies without impacting on rules. But there is a downside. When you write a CellValuN with a missing dimension you get warnigns in the server log - ALL THE TIME! I raised this with IBM and it went back to development who said it is as per design, I didn't agree .........

[lotsaram]

Did you create a hierarchy? Becasue from the picture you included it doesn't really look like it. It isn't enough to have an attribute in Product dimension matching an element name in Product_Dashboard dimension - you must have a consolidation which exactly matches the name.

[Andero]

Hi All,

Yes, In the first dimension I already created a virtual hierarchy so I can roll up the number at category level.
Please see screens below





So i can see the values in cube 1, and in dimensions 2 i have those codes.. I know there is a way to read those numbers represented through a VH, but the rule doesn't work

[MarenC]

Hi,

when you say the rule doesn't work, any chance of you expanding exactly what you mean by that?

Have you tried hardcoding the category into your rule, and if so does that retrieve a value or fix whatever it is that is wrong with the rule?

e.g. DB('cube1', .. , .. , 'Category':'1101','value')

Maren

[lotsaram]

RFAG #5 Post the actual code! https://www.tm1forum.com/viewtopic.php?t=1037

Show the actual rule that isn't working, not just a snippet. Show the WHOLE rule, including the left hand side. Show the structure of BOTH cubes.

If you want help, put in the required minimum effort that people can actually help you.

[gtonkin]

A little gotcha for anyone following this or reading on CellValueN.
Had an issue on a rule not workingas expected. A silly typo, but rules editor does not warn of invalid hierarchies or elements.
Best to try use the intellisense/Ctrl+space (in PAW) to avoid silly mistakes...

[Andero]

Hi all,

sorry if I didn't give more details about it but I have good news!!

I was finally able to make the rule work, actually it was correct from the start but for some reason the result was always 0.. so i decided to write it from scratch and it worked.
I'm giving more details for future reference

cube 1: AV_5100_Budget_Segmento
AV_SCN_Scenario_BDG
AV_TIME_Mesi
AV_PDV_PuntodiVenditaBDG
AV_PRD_Prodotto
AV_MAG_Magazzino
AV_VAR_AdjustemntsBDG
AV_VAR_VenditeBDG

cube2: AV_6000_Cruscotto
AV_SCN_Scenario
AV_TIME_Cruscotto
AV_MAG_Cruscotto
AV_PRD_Cruscotto
AV_VAR_TipoData
AV_ADJ_Cruscotto
AV_VAR_Cruscotto


AV_PRD_Prodotto, which contains the products, has an attribute "Category" indicating the product in the AV_PRD_Cruscotto.
So i cretaed a VH because i need a consolidated element that groups the AV_PRD_Prodotto elements

The rule i wrote:

Code: Select all

##Category
['UL_BDG_Totale_Perc','Base', 'Totale_magazzino'] = N: if (ELISANC('AV_PRD_Cruscotto', 'Totale Category',!AV_PRD_Cruscotto) > 0
,
DB('AV_5100_Budget_Segmento', ATTRS('AV_SCN_Scenario', !AV_SCN_Scenario, 'Scenario_BDG_Confronto'), 'Totale Anno', 'Totale PDV','Category':!AV_PRD_Cruscotto,!AV_MAG_Cruscotto,'Totale','UL_Totale_Perc')
, CONTINUE
);

thanks to all
regards

[lotsaram]

Excellent. Thanks for the update! Glad that you got it to work.

The real code rather than the pseudo code makes it much easier to see.