Calculating avg based on Levels. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Calculating avg based on Levels.

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. Note: 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.
when you defining the dimension, you need to introduce hierarchies —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
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. arul
]]>