SQL Server Performance

Calculating avg based on Levels.

Discussion in 'SQL Server 2005 Analysis Services' started by arulbalan, Jul 12, 2007.

  1. arulbalan New Member

    Hi Mates,

    How to calculate Avg based on Levels, But Analysis server is doing normal avg.

    But I want Avg on Avg. for example

    I have an Employee Hieararchiye by Levels (CTO, PM, PL, TL, TM)

    I have daily data of lunch time taken by each employee.


    Lunch Hrs and Day Present are Different Measures.

    DayPresent Measure is created by No of Rows Formula

    Fact Table Design

    Date EmployeeId LunchHrs

    LEVEL 5

    TM1 = Feb Hrs / Days Present in Feb

    TM2 = Feb Hrs / Days Present in Feb

    TM3 = Feb Hrs / Days Present in Feb (if this value is =NULL, should not be considered in Level 4)

    LEVEL 4

    When I calculate Avg Lunch Time taken By TL1 in February

    TL1 = Feb Hrs / Days Present in Feb

    TL1 = (TL1 + TM1 + TM2) /3 (TM values should be from Level 5)

    Note: TM3 not considered here, since value is NULL

    LEVEL 3

    PL in February =

    PL1 = Feb Hrs / Days Present in Feb

    PL1 = (PL1 + TL1 + TL2) / 3 (TL values should be from Level 4)

    ... up to LEVE 1

    How to resolve this issue.

    I am just a beginner in Analysis service.

    Thanks in Advance.

  2. dineshasanka Moderator

    when you defining the dimension, you need to introduce hierarchies

    Contributing Editor, Writer & Forums Moderator

    Visit my Blog at
  3. arulbalan New Member

    hi dineshasanka

    this is very very urgent need, please do help me as soon as possible.

    even if this is not clear, i am ready to explain in detail....

    i have already defined Employee dimension with Hiearhchies. (LEVEL 5, LEVEL 4 ...)

    When i calculate last level employee's avg lunch time for a particular month, it is giving result as i expect, bz it is leaf node.

    But It is not working for parent nodes as i expect.

    fact table structure.
    employeeid ##date##lunchtime

    where lunchtime is defined as avg measure in aggreatefunction.

    if employee x lunch time records for month of jan as follows

    jan1 60 mints
    jan2 40 mints
    jan3 50 mints

    we consider he is last level employee, doen't have children employees.
    so the avg should be 150/3. i am getting this.

    but when i calculate his boss lunch time avg,
    1. if boss having lunch time records then boss avg must be calcualted as done for employee x.
    2 . then boss avg = (boss avg + sum(sub-ordinate avg values)) / count(sub-ordinate) + 1 (boss).

    but analsis service is doing normal avg.

    for example

    x jan1 60 mints
    x jan2 40 mints
    x jan3 50 mints
    y jan1 60 mints
    y jan2 40 mints
    boss jan1 10 mints
    boss jan2 20 mints

    analysis service returns 60 + 40 + 50 + 60 + 40 + 10 + 20 / 7

    but i expect value of boss must be
    1. xx = (60 + 40 + 50) / 3
    2. yy = (60 + 40) / 2
    3. bb = 10+ 20 / 2
    4. result = xx + yy + bb /3 (here i am computing low level emp avg and moving that to next level computing).

    thanks in advance.


Share This Page