Transferring picklist items from one cube to another

[vaonlydrb]

Hi Guys,

Wanted to check how best the rule should be written based on the below cube design;

Source cube has four dimensions;

1) Employee numbers (rows empl 01, empl 02.......)
2) Employee Input (columns Employee, Type, Location - these are picklist items)
3) Departments
4) Versions

Source cube;

Version Dept A

Employee Type Location
TOTAL
Empl 01 Julianna Ausmus Full-Time On-Site
Empl 02 Lora Mancia Part-Time Off-Site
Empl 03
Empl 04

Target cube has five dimensions;
1) Version
2) Departments
3) Employee
4) Months (column)
5) Employee Hours (rows)

Target cube;

Version Dept A Employee

Jan Feb Mar - - Dec
Hours
Location On-Site On-Site On-Site On-Site On-Site On-Site
Rate 10% 10% 10% 12% 12% 12%



In the source cube, employee, type and locations are picklist. The target cube has Employee list as a dimension. I would like the "Location" in the source cube update the Location item in the target cube based on the employees selected. Location drives the "Rate" item which changes by month & whether on/off site. How do I go about creating this rule?

Any help on the best approach is much appreciated.

Regards

PS: I have also attached the above query in excel format for easier understanding)

[tomok]

Assuming the Employee No dimension is not the same as the Employee dimension ,what you are attempting to do is possible but not really feasible unless the number of elements in the Employee No dimension is really small. This is because you would need a separate rule statement for each element in the Employee No dimension:

Code: Select all

['Location'] = N:IF(!Employee @= DB('Source_Cube', !Version, !Departments, 'Empl 01', 'Employee'),
	DB('Source_Cube', !Version, !Departments, 'Empl 01', 'Location'),
	CONTINUE);
['Location'] = N:IF(!Employee @= DB('Source_Cube', !Version, !Departments, 'Empl 02', 'Employee'),
	DB('Source_Cube', !Version, !Departments, 'Empl 02', 'Location'),
	CONTINUE);	
['Location'] = N:IF(!Employee @= DB('Source_Cube', !Version, !Departments, 'Empl 03', 'Employee'),
	DB('Source_Cube', !Version, !Departments, 'Empl 03', 'Location'),
	CONTINUE);
.......
['Location'] = N:IF(!Employee @= DB('Source_Cube', !Version, !Departments, 'Empl n', 'Employee'),
	DB('Source_Cube', !Version, !Departments, 'Empl n', 'Location'),
	CONTINUE);	

In your case I would substitute the Employee No dimension in Source_Cube with Employee. Who cares if you have a spot for every employee, even if not needed. This is relational DB type thinking which is not the same as OLAP. Then your rule could be:

Code: Select all

['Location'] = N:DB('Source_Cube', !Version, !Departments, !Employee', 'Location'),

[vaonlydrb]

Thanks Tom!

Was thinking if there's a better/easier way of achieving it than writing multiple lines. I understand having employees as a dimension rather than employees lines would work better, but I wanted to check if this is the design how would this be accomplished.

We have similar design for capital purchases & depreciation calculation and hence the question.