SQL Server Performance

Calculating average by hierarchy level

Discussion in 'Analysis Services/Data Warehousing' started by thirumalesh, Jul 5, 2007.

  1. thirumalesh New Member

    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/day
    Level2 ---------- >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
  2. ranjitjain New Member

    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.

Share This Page