If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Data issue?

Started by Preethi, 15 Mar 2010 01:07:06 AM

Previous topic - Next topic

Preethi

Regarding GTP

branko

Hi Preethi,

well there are 2 ideas that i would started to consider.

1st - create someting like there is for "depreciation simulation" in standard installation lib. with analyst - go_capex_contributor - look at that 4 cubes (asset_purchases, depreciation & depn police should be enough to have a look at). You can have one cube (depn police) where you can set annual salaries for each salary bracket. You can have the other one - where can be details about empoyes (e.g. names ?, their salary bracket, date of entering the company.... - quite a lot of data, but without the timescale dlist - that will be used as a 1 dlist formated items - like in asset_purchases cube). And the 3rd cube could look like "depreciation" and will have a timescale & will combine data from all the previosly mentioned cubes - that could work fine (just have a look on that..)

2nd approach - is to solve it in your own way - trying to use "zeroes & ones" (0 & 1) in the timescale, the filnal salary will be a formula multiplying the "0&1 row" with the row for the whole years salary (something like you mentioned - a row filled allways from january to dec.) but after appropriate calculation (*0 or *1) only relevant month remains.

I think the 1st approach could work good and will be better (i will start with that) - the second option - i would use only if there were some unexpected & serious reasons why not to use the first one. (The second onlo requries much more trying & testing circles.. :-) & more work..)...


lav4you

#2
Hi Preethi,
It can be easily implemented with introduction of one calculation item using @Last BiF and two D-links
from HR to Finance for data


following is the example

HR Cube (New item with BiF): --
MonthsJanFebMarAprMayJunJulAugSepOctNovDecTotal
Salary/Month0000006000000006000
@Last-BiF0000006000600060006000600060000

Finance Cube (After running 1st link to fetch the @Last-BiF data):--
MonthsJanFebMarAprMayJunJulAugSepOctNovDecTotal
Salary Cost00000060006000600060006000600036000


Finance Cube (After running 2st link to salary data value for break-back):--
MonthsJanFebMarAprMayJunJulAugSepOctNovDecTotal
Salary Cost0000001000100010001000100010006000

I hope this serves ur purpose..

Preethi

Hi Jitesh,

Thanks for sharing your ideas.

But the problem is we are not using the 4months dimension in HR Cube. We have a field called DOJ (Date of Joining) in calculation dlist.

:-(

Looking forward to your response.

Preethi


lav4you

#4
Hi Preethi,

I was trying to work out some solution for the given situation..
Here I have created 2 cubes. first HR Info cube with assumption that it has dimensions like Employees, DOJ & Salary Info Input, etc.. 
I have also created a cube name Salary Calc to calculate salary based on month of joining.
finance cube will get the final salary values from this cube
following is the detail of structure & calculation explanation I hope now this time it solves ur issues... :D


HR Cube
(With additional salary per month calculation)
Cube Example:-

             Date of JoiningSalary/YearSalary/Month
Employee 1             31/12/20106000500
Employee 2             15/06/20101200100
Employee 3             01/07/20102400200
Employee 4             -00
Employee 5             -00

Salary calc

(to calculate salary and will flow the final salary amount to Finance )
following is the calculation for this cube
Item NameFormatCalcuationData Source
Date of JoiningDate, Blank If Zero-From HR Info Cube
Period Start DateDate, Blank If ZeroBiF@Time(3)-
Period Start EndDate, Blank If ZeroBiF@Time(5)-
Date ValidationNoneIF Date>={Period Start Date} AND Date<=({Period End Date}-1) THEN 1 ELSE 0-
Salary ConditionNoenBiF@Last(Date Validation)-
Salary/MonthNone-From HR Info Cube
Final Salary AmountNone{Salary Condition}*{Salary/Month}-

Cube Example:-
JanFebMarAprMayJunJulAugSepOctNovDecTotal
Date of Joining31/12/201031/12/201031/12/201031/12/201031/12/201031/12/201031/12/201031/12/201031/12/201031/12/201031/12/201031/12/2010
Period Start Date01/01/201001/02/201001/03/201001/04/201001/05/201001/06/201001/07/201001/08/201001/09/201001/10/201001/11/201001/12/2010
Period Start End01/02/201001/03/201001/04/201001/05/201001/06/201001/07/201001/08/201001/09/201001/10/201001/11/201001/12/201001/01/2011
Date Validation0000000000011
Salary Condition0000000000011
Salary/Month5005005005005005005005005005005005006000
Final Salary Amount00000000000500500

through this apporch u can also calculate salary for each day if u have working day based calculation...
the only overhead is that there will be an annual maintenance activity to change time scale for Salary Calc cube

I hope it helps you this time...

Preethi

#5
Hi Jitesh,

First of all, sorry for the delay!

You are really superb and thanks for helping.


lav4you

#6
Nice to hear that it worked for you this time(uuufffff)... ;D

Now the question for which you had aksed for...
if ur company does planning for Jun 2010 -Jul 2011, no issue same logic will work for it just start ur month dimension with Jun 2010 as 1st month and change accordingly the timescale info....

about calculating salary for the person who joined in Apr 2010...you can not do this with same logic.. as its only for calculating salary for new joinees of budgeted year..

now there are several ways to handle it...
1> you create month dimension which has more than one year like Jun 2009 - Jul 2011. this will calculate salary for Apr 2010 joinee.

2> which I feel is the correct case is that this Apr 2010 employee is an old employee of a company and will have entire years salary so calculate his salary for entire year blindly...... may be you can put condition in ur HR cube like
IF date of joining < starting date of period(1st Jun 2010 in over example) then calculate entire years salary else 0
and then flow this salary amount directly to Finance cube
Or
transfer monthly salary for all months to Finance cube


3> Tweak the existing condition for Data validation in Salary Calc cube
something like below
IF Date of Joining <=({Period End Date}-1) THEN 1 ELSE 0
this will removing the opening condition and will calculate salary for older employee as well...

But Preethi if you are building a proper HR module(seems from looking at ur requirement) which involves calculation of salary for old employee, new joinees and retired employees... then this is not the perfect model to work with...
I don't know know ur exact requirement so can not say much...

let me know how this time it works...

(I wont be accessing this forum for next few days, but you can send me email on my personal id(mentioned is profile details) i will reply back as soon as possible)

Preethi

#7
Hi Sir,

Thanksssssssssssssssssssssssssssssssssssssssssssssssssssssss for sharing the valuable suggestions and ideas...You are right, there was some confusion on the way users told us. Now, its corrected.



Thanks once again... :)


Preethi