How to transfer data when target dimension elements are in data portion of source cube

Sometimes you may have to transfer data from source to target in such a way that source cube contain some items which are not data items but need to be used as dimension elements.  Based on these items mapping should be performed and data should be transferred.

For example, consider an employee cube:

 

The above cube contains employee information. We want to transfer the data from this cube to a target cube:

As you can see that Sex and Age, which were in data portion of source cube are in dimension portion of target cube.

Using Rules:

In such a scenario you need to write rule in following way to transfer the data:

[] = S: IF(!Sex @= DB(‘EmployeeInfo’, !Serial, ‘Employee Sex’) & NUMBR(!Age) = DB(‘EmployeeInfo’, !Serial, ‘Employee Age’),DB(‘EmployeeInfo’, !Serial, ‘Employee Name’),continue);

 

Here is the output that you’d get:

Using TI process:

1. Take 2 new variables of type element in TI. Set their values to:

vSex=CellGetS(‘EmployeeInfo’,Serial,’Employee Sex’);

vAge=NumberToString(CellGetN(‘EmployeeInfo’,Serial,’Employee Age’));

2. Some code for metadata and data portion will automatically be generated in Advanced tab.

3. Now remove the ‘Update Cube’ selection. (so that we could remove the default CellGetS/N command which was added to TI.

4. Put following to update the cell:

If(EmployeeInfo @= ‘Employee Name’);
    CellPutS(VALUE,’Employee2′,Serial,vAge,vSex);
EndIf;

This was to ensure that we process each row only once, instead of processing it thrice (1 for each name/sex/age in source cube)

5. Now save and run the TI. You’ll see your data updated as shown above in rules section.

 

In above, i have assumed that you have basic understanding of TI hence I’ve not put a line by line commentary of what we need to do.

If you still have doubts, let me know in comments section.