Feeders cube to other cube

[JelleCommerce]

Dear forum members,

This might be a question that already is answered on this forum before, but I cannot seem to find the answer. For most of you this is also an easy question to answer as you have probably done this before, but I'm still quite the nembie on TM1, so I could use your help on this one.

I have a cube called Personnel, where people can give in the personnel members on their departement. this cube has the following dimensions:
- Year
- Department
- Type Worker (blue collar or white collar)
- Type Relation (internal or consultant)
- Version
- Personnel Measures (number of man days, cost per day, total cost (which is calculated by doing number of man days * cost per day), Project)

Users fill in the number of man days and the cost per day. The total cost is calculated. But users also fill in a Project by choosing a project from a picklist (which is filled by the project dimension)

Then I need the give the total cost through to another cube called Overview. This cube has the following dimensions:
- Year
- Department
- Version
- Source
- D_Project
- Overview Measures (cost)

As you can see there are a couple of dimension which are found in the Personnel, but not in the Overview cube. But there is a D_Project dimension in the Overview cube. Now I have to write a feeder which feeds the Overview taking into account that I transform the project the users chose from the picklist to a dimension.

I have taken a first attempt, so maybe you can point out the errors that I am making. On the Personnel cube I have the following rules/feeders:

['Total Type Worker','Total Type Relation','total cost']
=>
DB('Overview',!Year,!Department,!Version,'personnel source',DB('Personnel',!Year,!Department,!Type Worker,!Type Relation,!Version,'Project'),'cost');

I am not sure if this is correct. And then on the Overview cube I have made a rule/calculation like this:

['personnel source','cost']=N:IF(!D_Project @= DB('Personnel',!Year,!Department,!Type Worker,!Type Relation,!Version,'Project'),
DB('Personnel',!Year,!Department,'Total Type Worker','Total Type Relation',!Version,'total cost'),0);

But this does not seem to work. Maybe I am not writing he feeders correctly. Could you point out how you would do this? But take into account that I cannot change the layout of the cubes.

Thank you for your help. I hope I explained it a bit so you could understand, because I find it difficult to explain the problem.

[tomok]

Feeders and rules are related but not the same thing. A feeder does not have to be correct in order for a rule to work. A bad/missing feeder will only keep your rule calculated cells from consolidating or cause them to disappear when zero-suppressed. Let's start with the rule:

Code: Select all

['personnel source','cost']=N:IF(!D_Project @= DB('Personnel',!Year,!Department,!Type Worker,!Type Relation,!Version,'Project'),DB('Personnel',!Year,!Department,'Total Type Worker','Total Type Relation',!Version,'total cost'),0);

This isn't going to work because your "Project" designation is only at the individual Type Worker and Type Relation elements, not in total. Therefore you can't point to the total of each of these and expect it to move over to the Overview cube. Given your cube structure and the fact it cannot be changed I don't see a solution for you. The data could fairly easily transported over by a TI process but not via a rule.

[jim wood]

Tomok,

I guess it depends on how quickly this needs turning around. In some cases (especially within what if) a TI just isn't the answer. I do however agree that the cube structure is going to cause problems. I would however firstly ask why you are feeding from total cost? This will only work if the cost is also fed. Wouldn't you better feeding form a key driver for the cost calculation? For example number of days?

Jim.

[tomok]

I do however agree that the cube structure is going to cause problems.

It's not just going to cause problems, it's not going to work without some serious jury-rigging. That's because you have to have the elements of the Project dimension in the Personnel cube to make the reference. Absent the Project dimension, he would have to add contrived "Project" elements to either the Type Worker, Type Relation, or Measures dimension. The easiest one would be to add an element to the Measures dimension for each Project and Cost, like Project_A_Cost, Project_B_Cost, etc. Then he could use the SUBST dimension to strip out the Project element and send the correct Measure element to the correct Project. I doubt anyone would seriously consider doing this unless the Project dimension were really small.