Allocation of Payroll if the number of dimension are mismatch in three different cubes

[dparkar]

Hi EveryOne,

I was going through a problem where I have 2 excel sheets, in 1st sheet I have Employee Salary on a weekly basis that would roll up to months & further to year, in 2nd sheet I have the same Employee salary according to there JobCode but on an annual basis.
I have created 2 Cubes and uploaded both the CSV files,
1st Cube consists of version, Weeks, EmployeeName, Region, job position, Regional Director, and a measure Dimension,
2nd cube consists of version, Employee Name, Department, job code, Regional director, sponsor and a measure dimension.
In 3rd cube, I need all the dimensions present in 1st as well as 2nd and Allocate the salary by the department and by job code on a weekly basis.
I want it to be done by Ti only as using the formula would make the model very heavy with feeders.
Note- Annual Salary in sheet 1 is equal to the annual salary in sheet 2.

Thanks in Advance.

[EvgenyT]

So what is exactly are you asking us to do?

[dparkar]

I wanted a solution on how can I build the 3rd cube which would allocate the salary by job code by weekly.
As I mentioned i don't have job code dimension in 1st cube & I don't have week i.e time dimension in 2nd cube.
I want to allocate the salary by Job code & by the week in 3rd cube using Ti only.

[EvgenyT]

What have you tried so far in terms of writting a TI or rule?

[dparkar]

Hi,
I have tried to get a ratio for 1 particular job code, For e.g in cube 2 I have an annual salary of Alex as 50000 which is allocated along with 4 job codes lets say,
Cube 1
Week1 = 5000
Week2 = 10000
Week3 = 10000
Week4 = 10000
Week5 = 15000

Cube 2
Amount Ratio
JC1 = 10000 0.2
JC2 = 20000 0.4
JC3 = 5000 0.1
JC4 = 15000 0.3

Cube 3
Week1
JC1 1000
JC2 2000
JC3 500
JC4 1500


This is my exact allocation in 3rd cube, now I have tried to bring all the data from 1st cube & 2nd cube to 3rd as the number of dimensions is a mismatch so I took a blank element where the dimension doesn't match in 3rd cube. now when I am multiplying this ratio using cellgetN it is multiplying the ratio to the blank elements and output calculation is appearing as 0.