TM1 Forum

Getting Data from another cube:

I have Cube A and Cube B.
Cube A has two dimensions Position and Position Measures with PayGrade (Numeric) and Benefit Band (String) elements:

Sample Data for Cube A

Position PayGrade (N) Benefit band (S)
Position A 10 A
Position B 10 A
Position C 12 B


Cube B has two dimensions Pay Grade/Benefit Band and Pay Grade/Benefit Band Measures with Pay Grade count (Numeric).

Sample structure

Pay Grade Pay Grade count (N)
10 ?
12 ?

Or

Benefit Band Benefit Band count
A ?
B ?

I know the answer is 2 and 1. But how can I write a rule to get the count from Cube A? Is it possible even though Pay grade 10 and 12 are the N elements in the Cube B and they are values of a Measure in the Cube A? Same exercise required for Benefit band. Only difference is the Benefit band is the String element in the Cube A.

Any help will be appreciated.

Thanks.
MB

mbillah wrote:Getting Data from another cube:

I have Cube A and Cube B.
Cube A has two dimensions Position and Position Measures with PayGrade (Numeric) and Benefit Band (String) elements:

Sample Data for Cube A

Position PayGrade (N) Benefit band (S)
Position A 10 A
Position B 10 A
Position C 12 B


Cube B has two dimensions Pay Grade/Benefit Band and Pay Grade/Benefit Band Measures with Pay Grade count (Numeric).

Sample structure

Pay Grade Pay Grade count (N)
10 ?
12 ?

Or

Benefit Band Benefit Band count
A ?
B ?

I know the answer is 2 and 1. But how can I write a rule to get the count from Cube A? Is it possible even though Pay grade 10 and 12 are the N elements in the Cube B and they are values of a Measure in the Cube A? Same exercise required for Benefit band. Only difference is the Benefit band is the String element in the Cube A.

Any help will be appreciated.

Thanks.
MB

In the Position dimension define a consolidation
All Positions being consolidation of Positions A B C

Similarly define an All Pay Grades consolidation in Pay Grade and an All Benefit Bands consolidation in Benefit Band

Create a Cube C with dimensions

Position
Pay_Grade
Benefit_Band
C_Measures
Count

Write a rule in Cube C that says

['Count'] = N:
IF( DB('Cube A',!Position,'Pay Grade') @= !Pay_Grade
&
DB('Cube A',!Position,'Benefit Band') @= !Benefit_Band
,
1
,
0
) ;

Write a feeder in Cube A that says

[] => DB('Cube C', !Position, DB('Cube A', !Position, 'Pay Grade'), DB('Cube A', !Position, 'Benefit Band') ) ;

I haven't tested this out, so if you get any syntax / design glitches come back to me.

You don't need Cube B.

In Cube C is you want the number of people in Benefit Band A, select Benefit Band A and select All Positions and All Pay Grades. The total Count should give you the right answer.

The other alternative, is of course to load the data into Cube C in the first place. However, the rule based approach from Cube A is sometimes needed when users enter directly, and it does ensure that the cube can only have one set of values for a position at any one time.

Regards

Paul

Hi Paul,

Thank you very much for your reply. I have managed to save the rule. But there is no result coming up. I cannot trace the feeder. That means feeder is not working. Am I correct?

Is there any else need to be done?

I appreciate your replies.

Regards,
Masum