[PS2007] Data analysis problem with surrogate timesheets

[Pawel]

I use Data Analysis in PS 2007 extensively and once again I came across a
problem. I hope someone of you, distinguished group visitors, will shed some
light on it.

We created a DA view (basing on the Timesheet cube) to verify that everyone
filled their timesheets. it is used at the end of the month and thanks to it
we can very easily see any discrepancies from the assumed value (which
normally is a sign that someone had made a mistake in his/her timesheet or
just forgot to fill it).

Unfortunately, it works only for "standard" timesheets - if a surrogate
timesheet is used, all the hours are accounted not for the owner of the
timesheet (e.g. a programmer), but for the timesheet creator (a manager who
created the surrogate TS).

I spent many hours trying to dig out what happens (even directlyy analyzing
the OPS database and cube definitions), but I found no easy solution for the
problem and the complexity of the analysis strucures exceeded my mental
capacities

Does anyone have any clues on what is the problem? Is it an OPS bug (which
would give hope that eventually it will be corrected), or a kind of weird
feature I do not understand?
Or maybe I just use this cube in a non-standard way?

Thanks in advance,

Pawel

 

[Dale Howard [MVP]]

Pawel --

Without digging into this as extensively as you have, I can only speculate
that this is a design flaw or a simple oversight on Microsoft's part.
Logically, the surrogate timesheet hours should belong to the resource for
whom the hours were created, not for the person creating the surrogate
timesheet. I will report this to Microsoft as a possible design flaw and
will see what they say about it, if anything. Thanks for reporting this
disconcerting behavior.

 

[Pawel]

Hi again,

Few months have passed since I asked for help with this problem, but finally
I found a solution that I will share here, even if probably if is a bit too
low-level for most of us on the discussion group. But anyway - if it ever
helps even a single individual, it is worth to share I guess

The solution is actually quite simple. We use SQL Management Studio to
modify one of the ProjectServer_Reporting DB views
(MSP_TimesheetActual_OlapView ) in such a way, that instead of providing the
name/ID of the person who last changed the report, it gives the owner of the
timesheet (what could be expected).

The SQL code that works for us is pasted below, but please use it on your
own responsibility (and don't forget to make a backup of the actual
MSP_TimesheetActual_OlapView definition).

USE [ProjectServer_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MSP_TimesheetActual_OlapView]
AS
SELECT
ta.TimesheetLineUID as TimesheetLineUID,
ta.TimeByDay as TimeByDay,
t.OwnerResourceNameUID as LastChangedResourceNameUID, --This is important
ta.CreatedDate as CreatedDate,
ta.TimeByDay_DayOfWeek as TimeByDay_DayOfWeek,
ta.TimeByDay_DayOfMonth as TimeByDay_DayOfMonth,
ta.AdjustmentUID as AdjustmentUID,
ta.ActualWorkBillable as ActualWorkBillable,
ta.ActualWorkNonBillable as ActualWorkNonBillable,
ta.ActualOvertimeWorkBillable as ActualOvertimeWorkBillable,
ta.ActualOvertimeWorkNonBillable as ActualOvertimeWorkNonBillable,
ta.PlannedWork as PlannedWork,
ta.Comment as Comment
FROM
MSP_TimesheetActual AS ta FULL OUTER JOIN
dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID = tl.TimesheetLineUID
FULL OUTER JOIN
dbo.MSP_Timesheet AS t ON tl.TimesheetUID = t.TimesheetUID

 

[Shawn Everingham]

Pawel,

I am experiencing the exact same thing you describe. We have a view in Data
Analysis that shows Timesheet Actuals. We have an employee on extended leave
and their timesheet manager, filled out a surrogate timesheet on their
behalf. Now the view that we created shows the timesheet manager at 80+ hours
for that week, as if, the surrogate timesheet hours are hers.

I will dissect your solution and see if I can do something with it. Stay
tuned!!!

Thanks for posting your work-around. Microsoft...PLEASE FIX!! thanks! Shawn

 

[r.kleinkoerkamp]

Hi Pawel,

Thanks for you solution, it helps us to solve the same kind of
problem. We would like to retrieve the vacation hours from the
timesheets, but we have some surrogate timesheets. The author of the
surrogate timesheets did take many vacaction hours ;-), same issue as
Shawn.

One question: Why are you using an outer join? We now use a inner
join. The inner join does result in the same number of records as the
original MSP_TimesheetActual_OlapView query.

Regards,
Ricardo

Pawel,

I am experiencing the exact same thing you describe. We have a view in Data
Analysis that showsTimesheetActuals. We have an employee on extended leave
and theirtimesheetmanager, filled out a surrogatetimesheeton their
behalf. Now the view that we created shows thetimesheetmanager at 80+ hours
for that week, as if, the surrogatetimesheethours are hers.

I will dissect your solution and see if I can do something with it. Stay
tuned!!!

Thanks for posting your work-around. Microsoft...PLEASE FIX!! thanks! Shawn
--
Technology, like art, is a soaring stretch of our imagination...Daniel Bell.
1919

Hi again,

Few months have passed since I asked for help with this problem, but finally
I found a solution that I will share here, even if probably if is a bit too
low-level for most of us on the discussion group. But anyway - if it ever
helps even a single individual, it is worth to share I guess

The solution is actually quite simple. We use SQL Management Studio to
modify one of the ProjectServer_Reporting DB views
(MSP_TimesheetActual_OlapView ) in such a way, that instead of providingthe
name/ID of the person who last changed the report, it gives theownerof the
timesheet(what could be expected).

The SQL code that works for us is pasted below, but please use it on your
own responsibility (and don't forget to make a backup of the actual
MSP_TimesheetActual_OlapView definition).

USE [ProjectServer_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MSP_TimesheetActual_OlapView]
AS
   SELECT
   ta.TimesheetLineUID as TimesheetLineUID,
   ta.TimeByDay as TimeByDay,
   t.OwnerResourceNameUID as LastChangedResourceNameUID, --This is important
   ta.CreatedDate as CreatedDate,
   ta.TimeByDay_DayOfWeek as TimeByDay_DayOfWeek,
   ta.TimeByDay_DayOfMonth as TimeByDay_DayOfMonth,
   ta.AdjustmentUID as AdjustmentUID,
   ta.ActualWorkBillable as ActualWorkBillable,
   ta.ActualWorkNonBillable as ActualWorkNonBillable,
   ta.ActualOvertimeWorkBillable as ActualOvertimeWorkBillable,
   ta.ActualOvertimeWorkNonBillable as ActualOvertimeWorkNonBillable,
   ta.PlannedWork as PlannedWork,
   ta.Comment as Comment
FROM
   MSP_TimesheetActual AS ta FULL OUTER JOIN
   dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID = tl.TimesheetLineUID
FULL OUTER JOIN
   dbo.MSP_Timesheet AS t ON tl.TimesheetUID = t.TimesheetUID- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

 

[Shawn Everingham]

Hi Pawel,

Just a quick follow up. I changed that view using your SQL provided. I went
to my Data Analysis view that I had the problem with and it all looks the
same as before. I changed the view back to the original view (made a back
up). Now I'm thinking, I have to wait till the cube gets rebuilt to actually
take advantage of the view.

Is that correct? thanks!
--
Technology, like art, is a soaring stretch of our imagination...Daniel Bell.
1919

Hi Pawel,

Thanks for you solution, it helps us to solve the same kind of
problem. We would like to retrieve the vacation hours from the
timesheets, but we have some surrogate timesheets. The author of the
surrogate timesheets did take many vacaction hours ;-), same issue as
Shawn.

One question: Why are you using an outer join? We now use a inner
join. The inner join does result in the same number of records as the
original MSP_TimesheetActual_OlapView query.

Regards,
Ricardo

Pawel,

I am experiencing the exact same thing you describe. We have a view in Data
Analysis that showsTimesheetActuals. We have an employee on extended leave
and theirtimesheetmanager, filled out a surrogatetimesheeton their
behalf. Now the view that we created shows thetimesheetmanager at 80+ hours
for that week, as if, the surrogatetimesheethours are hers.

I will dissect your solution and see if I can do something with it. Stay
tuned!!!

Thanks for posting your work-around. Microsoft...PLEASE FIX!! thanks! Shawn
--
Technology, like art, is a soaring stretch of our imagination...Daniel Bell.
1919

Hi again,

Few months have passed since I asked for help with this problem, but finally
I found a solution that I will share here, even if probably if is a bit too
low-level for most of us on the discussion group. But anyway - if it ever
helps even a single individual, it is worth to share I guess

The solution is actually quite simple. We use SQL Management Studio to
modify one of the ProjectServer_Reporting DB views
(MSP_TimesheetActual_OlapView ) in such a way, that instead of providing the
name/ID of the person who last changed the report, it gives theownerof the
timesheet(what could be expected).

The SQL code that works for us is pasted below, but please use it on your
own responsibility (and don't forget to make a backup of the actual
MSP_TimesheetActual_OlapView definition).

USE [ProjectServer_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MSP_TimesheetActual_OlapView]
AS
SELECT
ta.TimesheetLineUID as TimesheetLineUID,
ta.TimeByDay as TimeByDay,
t.OwnerResourceNameUID as LastChangedResourceNameUID, --This is important
ta.CreatedDate as CreatedDate,
ta.TimeByDay_DayOfWeek as TimeByDay_DayOfWeek,
ta.TimeByDay_DayOfMonth as TimeByDay_DayOfMonth,
ta.AdjustmentUID as AdjustmentUID,
ta.ActualWorkBillable as ActualWorkBillable,
ta.ActualWorkNonBillable as ActualWorkNonBillable,
ta.ActualOvertimeWorkBillable as ActualOvertimeWorkBillable,
ta.ActualOvertimeWorkNonBillable as ActualOvertimeWorkNonBillable,
ta.PlannedWork as PlannedWork,
ta.Comment as Comment
FROM
MSP_TimesheetActual AS ta FULL OUTER JOIN
dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID = tl.TimesheetLineUID
FULL OUTER JOIN
dbo.MSP_Timesheet AS t ON tl.TimesheetUID = t.TimesheetUID- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -