Rule for different lvl dimensions.

[najczuk]

Hi,
I have following cubes as attached.

Cubes

rules.PNG (11.97 KiB) Viewed 6431 times

I've written a rule in Sales_Plan_Cube to set Margin value based on Project margin value for the same month on a leaf level but it doesn't work as expected.

Code: Select all

['Margin']=DB('Margin_Cube', !Team_Dim, !Date_Dim, 'Margin');

It assigns margin value from Margin_Cube to consolidated (Project, Month) in Sales_Plan_Cube instead of Leaf members (Day and Team).

Result

rules2.png (34.12 KiB) Viewed 6431 times

How should I correct my rule?

[rmackenzie]

Is Date_Dim the same in both cubes or do you have separate dimensions? Is one with a n-level of month and one with a n-level of date? Or, are you storing margin % at a consolidated level in Margin_Cube using string elements, or other calculations? It makes something of a difference to the answer.

[najczuk]

Date_DIM is not the same, I've changed the schema.
!EDIT

Correct Structure

rules3.png (10.28 KiB) Viewed 6396 times

So the rule placed in Sales_Plan_Cube still looks the same, but Margin_Cube Dimension name changed to Month_DIM.

Code: Select all

['Margin']=DB('Margin_Cube', !Team_Dim, !Date_Dim, 'Margin');

I still use the Date_Dim as it finds matching member by name.
Margin measure in Margin_measures and Sales_measures is numeric value.

[qml]

Your two date dimensions are different in that weeks and days don't exist in your source cube. So you need a way to map the days and weeks from date_dim to the elements in month_dim from which they should source their data (I'm assuming it's the months). One very classical way would be to add a text attribute to date_dim and populate it with the appropriate month_dim element names. Then instead of referencing !date_dim in your rule you use ATTRS to get the element you need. You also need to think what happens for years, querters etc. (i.e. if you need to populate the attribute for these and with what) - your rule is unqualified, so you need to think how it will behave on any level of aggregation.

Feeding this should be quite straightforward, I don't see the need to have an attribute to reverse the month-week-day mapping.

[najczuk]

Thank you for the answer. When using ATTRS should I write a rule for every Team_DIM leaf?

[qml]

Thank you for the answer. When using ATTRS should I write a rule for every Team_DIM leaf?

That could work, but it wouldn't be good practice as you would have to keep updating the rules as your dimensions change.
Instead, think how you can solve the issue in a generic kind of way. What about mapping teams and team groups to projects using a similar text attribute to the one we just talked about for dates? Then your rule would have two ATTRS references to map two different dimensions.

[najczuk]

Ok, but how to write a rule that will apply ATTRS function on every date/team leaf element. Syntax requires passing: dimension,ELEMENT,attribute.
So if i add Month attribute to the Date_Dim the rule in SalesPlanCube should look like:

Code: Select all

['Margin']=DB('Margin_Cube', !Team_Dim,Attrs('Date_Dim',!Date_Dim,'Month'), 'Margin');

or

Code: Select all

['Margin']=DB('Margin_Cube', !Team_Dim,ELPAR('Date_Dim',!Date_Dim,2), 'Margin');

But it still displays values only on Month lvl.

[rmackenzie]

Ok, but how to write a rule that will apply ATTRS function on every date/team leaf element.

Use the N: qualifier in the rule - this is one of the reasons I asked if the time dimensions were the same or different. Using N: will make the rule act only at the leaf level of the cube. So, you write the rule like this:

Code: Select all

['Margin']=  N:  DB('Margin_Cube', !Team_Dim,Attrs('Date_Dim',!Date_Dim,'Month'), 'Margin');

This means the Month attribute should be populated against the dd/mm/yyyy elements in Date_Dim. In Team_Dim you need to create a Project attribute and populate the relevant project for the team - same concept as dates and months.

Regarding this:

Code: Select all

['Margin']=DB('Margin_Cube', !Team_Dim,ELPAR('Date_Dim',!Date_Dim,2), 'Margin');

You should avoid using ELPAR in rules as the index may change over time as new hierarchies are built, old ones removed etc. What is the 2nd parent today may not be the same one tomorrow. It's a common re-factoring strategy to replace all use of ELPAR with attribute look-ups. ELPAR is still useful for reports and so forth, but not in the back-end logic.

[qml]

What Robin said.

And one more thing. In addition to ELPAR usage in rules not being a good / safe / sustainable / future-proof practice, this snippet of code will not do what you want either:

Code: Select all

ELPAR('Date_Dim',!Date_Dim,2)

To return the ancestor 2 levels above you would need to do something like that:

Code: Select all

ELPAR('Date_Dim',ELPAR('Date_Dim',!Date_Dim,1),1)

[najczuk]

Thank you guys. It helped me a lot to understand basics concepts.