Page 1 of 1
Map excel data to dimension attribute
Posted: Tue May 03, 2011 3:21 am
by 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?
Re: Map excel data to dimension attribute
Posted: Tue May 03, 2011 3:31 am
by 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
Re: Map excel data to dimension attribute
Posted: Tue May 03, 2011 6:16 am
by lkyow
chesteronrada wrote: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
Re: Map excel data to dimension attribute
Posted: Tue May 03, 2011 6:20 am
by lkyow
lkyow wrote:chesteronrada wrote: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
Re: Map excel data to dimension attribute
Posted: Tue May 03, 2011 8:12 am
by 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
Re: Map excel data to dimension attribute
Posted: Tue May 03, 2011 1:05 pm
by 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?
Re: Map excel data to dimension attribute
Posted: Wed May 04, 2011 2:35 pm
by lkyow
tomok wrote: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
Re: Map excel data to dimension attribute
Posted: Wed May 04, 2011 8:32 pm
by 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.