Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thirumalesh
Starting Member
2 Posts |
 Posted - 2007-07-05 : 03:29:19
|
| Hi all, I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as followsThe following data explains the average value of each employee in corresponding level.Level1 - > E1 – (25hrs /25days) =1 hrs/dayLevel2 ---------- >E2 – (125hrs /25days) = 5 hrs/day Level3 ------------------------ >E4 – (150hrs /25days) = 6hrs/dayLevel4 --------------------------------------- > E6 – (100hrs /25days) = 4hrs/dayLevel4 --------------------------------------- > E7 – (75hrs/25days) = 3hrs/dayLevel4 --------------------------------------- > E8 – (175hrs/25days) = 7hrs/dayLevel3 ------------------------ >E5 – (75hrs/25days) = 3hrs/dayLevel2 ---------- >E3 – (100hrs /25days) = 4hrs/dayEg: I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & head and divides with number of records (normal avg). My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.The following calculation gives average value at each level.Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day < = (E6+E7+E8+E4)/4Average of Level 2(E2) = > (5+3+5)/3= 4.333 < =avg(Level3(E4))+avg(Level3(E5)))/2Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111 < = avg(Level3(E2))+avg(Level3(E3)))/2Formula for average of level : : (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).Thanks in advanceThiru |
|
|
|
|
|
Calculating average by hierarchy level