SQL Server Performance Forum – Threads Archive
Calculating average by hierarchy level
Hi all, I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as follows The 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/day
Level4 ————————————— > E6 – (100hrs /25days) = 4hrs/day
Level4 ————————————— > E7 – (75hrs/25days) = 3hrs/day
Level4 ————————————— > E8 – (175hrs/25days) = 7hrs/day
Level3 ———————— >E5 – (75hrs/25days) = 3hrs/day
Level2 ———- >E3 – (100hrs /25days) = 4hrs/day Eg:
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#%92s 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)/4 Average of Level 2(E2) = > (5+3+5)/3= 4.333 < =avg(Level3(E4))+avg(Level3(E5)))/2 Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111
< = avg(Level3(E2))+avg(Level3(E3)))/2
Formula 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 advance
Thiru
Hi,
Instead of defining a Measure Item directly as avg in the cube,
go for separating them by creating two measures.
i.e.
one for summing up
second one to count them. Finally in your MDX, based on time/ other dimension filters,
create measure to calculate the avg by sum/count.
]]>