Map excel data to dimension attribute

[lkyow]

Hi,

Year Branch EmployeeName Grade Base Salary
2012 Branch KL A Chris Grade 1 2000
2012 Branch KL B Richard Grade 2 3000
2012 Branch Pahang A Katie Grade 3 4000

I have above data in excel, I would like to upload the data to cube using TI

My cube have dimension

Time
Branch
Grade
Base Salary Measure

Time dimension have hierarchy
2012
-Jan 12
-Feb 12
-Mar 12
etc

I would like to map the data Year '2012' to the time dimension
2012 --> Jan 12
2012 --> Feb 12
2012 --> Mar 12

Any advice on how can I do this? Can i map the excel data to the dimension attribute?

[chesteronrada]

Do you have months on your data or you want it to be distributed to all months?
If so just add 12 lines for Jan to Dec...
or use loop

[lkyow]

Do you have months on your data or you want it to be distributed to all months?
If so just add 12 lines for Jan to Dec...
or use loop

Hi Chesteronrada,

i want it to be distributed to all months

Thansk for the advice,i will try it..hope it works

[lkyow]

Do you have months on your data or you want it to be distributed to all months?
If so just add 12 lines for Jan to Dec...
or use loop

Hi Chesteronrada,

i want it to be distributed to all months

Thansk for the advice,i will try it..hope it works

Abyway,what do you mean by loop? I have no idea on doing this

[chesteronrada]

Code: Select all

#Add this on your prolog
v1 = 'Jan';
v2 = 'Feb';
v3 = 'Mar';
v4 = 'Apr';
v5 = 'May';
v6 = 'Jun';
v7 = 'Jul';
v8 = 'Aug';
v9 = 'Sep';
v10 = 'Oct';
v11 = 'Nov';
v12 = 'Dec';

#on your data tab
x=1;
WHILE(x <= 12);
   VarS = EXPAND ( '%v' | NUMBERTOSTRING(x) | '%' );
   CELLPUTN(Value, CubeName, VarS | SUBST(<year var>,3,2), Branch, Grade, Base Salary Measure)
x = x+1;
END;

if its to much just add 12 lines hehehe for Jan, Feb, etc...
just be careful with using LOOPS

[tomok]

I would question why you feel the need to "map" the annual salary data to a cube that has a time dimension including all the months in the first place. If the annual salary does not change then why include the months in the time dimension? You only need months if the salary may change during any particular monthly but from the description of your data file that is not the case. Why blow out this HR data by month if each month is just going to be the same amount? If each month has the potential of being different then why do you need to map the annual salary to each month?

[lkyow]

I would question why you feel the need to "map" the annual salary data to a cube that has a time dimension including all the months in the first place. If the annual salary does not change then why include the months in the time dimension? You only need months if the salary may change during any particular monthly but from the description of your data file that is not the case. Why blow out this HR data by month if each month is just going to be the same amount? If each month has the potential of being different then why do you need to map the annual salary to each month?

Hi Tomok

Because i need to fetch the HR salary data to Profit and Loss where in my profit and loss, i have month dimension...
where the customer need to anaylse P&L by monthly

[Martin Ryan]

If that's the only reason, then your P&L could simply divide the annual figure in the HR cube by 12 when it pulls it across.

Of course that means that the feeder from your HR cube will need to feed all 12 months, but that's not going to create any extra overhead than what you've got now.