Data Transfer based on Lookup cube and Attribute Value

[David]

I am trying to transfer the labor cost from a labor cost cube to summary cube. In the labor cost cube, the cost is by employee. Each employee belongs to an Organization and each Organization has one G&A pool and one Overhead pool. In the summary cube, I want to group the labor cost by G&A pool and Overhead pool. Here are the information for the cubes and dimensions.
1. Labor Cost – has 4 dimensions: Employees, Months, Projects and Measure. Employees dimension has an attribute Org.
2. Org to Pool – has 2 dimensions: Org and Pool Types. Pool Types has two string elements – G&A and Overhead. This cube lists the G&A and OH pool numbers for each org.
3. Summary – has 5 dimensions: G&A_Pools, Overhead Pools, Projects, Months and Measure.

I tried to write a rule in the summary cube like this without success:

Code: Select all

['Base']=N:DB('Labor Cost',(DB('Org to Pool',attrs('Employees',!Employees,'Org'),'G&A')),(DB('Org to Pool',attrs('Employees',!Employees,'Org'),'Overhead')),!Months,!Projects,'Labor Cost');

Can anyone help? Thank you!

[Steve Rowe]

Hi David,
I think maybe something like this is what you mean?

Code: Select all

['Base']=N:
#If we are at an intersection where the G&A attribute and the Overhead pool attribute match the element names for those dimensions
If ( DB('Org to Pool',attrs('Employees',!Employees,'Org'),'G&A'))@=!G&A_Pools &
     DB('Org to Pool',attrs('Employees',!Employees,'Org'),'Overhead'))@=!Overhead Pools ,
#Then make the link
    DB('Labor Cost', 'Some element from the Employee dimension',!Months,!Projects,'Labor Cost'),
#No match so return 0 (use continue if you have other rule work in this area)
    0);

Anyway I wrote all this and then noticed that you don't have employee in your summary cube, so the above won't work!
(Sorry had to goto a meeting)
I think your basic design is wrong, you are trying to establish a relationship between Employee, G&A and Overhead. This really implies that you need a cube with all three of these dimensions in it. So,
If you can rebuild summary with the Employee dimension in it or
Build another cube with all three dimensions in it where you can do the matching using rules, then reference this from the summary cube.

HTH
(For future refernce you should avoid using & in dimension names if you can too.)

[Jessy]

DB('Labor Cost',(DB('Org to Pool',attrs('Employees',!Employees,'Org'),'G&A')),(DB('Org to Pool',attrs('Employees',!Employees,'Org'),'Overhead')),!Months,!Projects,'Labor Cost');

You are referencing Cube Labor Cost, but the elements are not in accordance with those in the cube, actually !Employee is missed; and the most important, there is no Dimension Employee in Summary.
I'm afraid you have to modify the cube structure.
HTH.

[David]

Steve/Jessy, thank you for the response. The purpose of the summary cube is to merge all the labor cost together and the !Employees dimension will not be in the summary.

Steve - I will try to build another cube to link the employee to the G&A/Overhead. Thanks.