Jonathan, I'm big on OLAP (as in user... haven't gotten into the 'how it
works' side of things) but I found too many limitations in the current cube
specific to timesheets. Apparently there's no way, without extending the
cube' to include the project and/or resource custom fields into the cube and
it makes reporting too constrained for us.
I've created a SQL Server view that extracts our timesheet data and layers
in our custom fields. From this I created a view that I can query with any
ODBC tool. My primary use is to tie this into an Excel pivot table and I get
all the power of an OLAP-like tool and exactly the data I really need
specific to timesheets.
SELECT dbo.MSP_TimesheetActual.ActualWorkBillable AS ActualWork,
dbo.MSP_TimesheetClass.ClassName, dbo.MSP_TimesheetActual.TimeByDay AS Date,
dbo.MSP_TimesheetPeriod.PeriodName AS Week, CASE
DATEPART(month, MSP_TimesheetActual.TimeByDay)
WHEN '1' THEN 'Jan' WHEN '2' THEN 'Feb' WHEN '3' THEN
'Mar' WHEN '4' THEN 'Apr' WHEN '5' THEN 'May' WHEN '6' THEN 'Jun' WHEN '7'
THEN 'Jul'
WHEN '8' THEN 'Aug' WHEN '9' THEN 'Sep' WHEN '10'
THEN 'Oct' WHEN '11' THEN 'Nov' WHEN '12' THEN 'Dec' END AS Month,
dbo.MSP_EpmResource_UserView.Position,
dbo.MSP_EpmProject_UserView.Program, dbo.MSP_TimesheetProject.ProjectName,
dbo.MSP_EpmProject_UserView.ProjectCreatedDate,
dbo.MSP_EpmProject_UserView.ProjectUID,
CASE MSP_EpmProject_UserView.ProjectType WHEN '0' THEN
'Project' WHEN '4' THEN 'Activity' ELSE 'Unknown' END AS ProjType,
dbo.MSP_EpmResource_UserView.RBS,
dbo.MSP_EpmResource_UserView.ResourceIsActive,
dbo.MSP_TimesheetResource.ResourceName,
dbo.MSP_EpmResource_UserView.ResourceUID,
dbo.MSP_EpmProject_UserView.RPS, CASE DATEPART(q,
MSP_TimesheetActual.TimeByDay)
WHEN '1' THEN 'Qtr 1' WHEN '2' THEN 'Qtr 2' WHEN '3'
THEN 'Qtr 3' WHEN '4' THEN 'Qtr 4' END AS Qtr, DATEPART(yyyy,
dbo.MSP_TimesheetActual.TimeByDay) AS Year,
dbo.MSP_TimesheetPeriodStatus.Description AS PeriodStatus,
dbo.MSP_TimesheetTask.TaskName,
dbo.MSP_TimesheetTask.TaskUID,
dbo.MSP_TimesheetStatus.Description AS TimesheetStatus,
/*
project and resource custom fields - replace this
section with your field definitions
if you don't add this section you'll need to remove
the comma at the end of the line before this
dbo.MSP_EpmProject_UserView.[Effort Category] AS
EffortCategory, dbo.MSP_EpmProject_UserView.[Effort Driver] AS EffortDriver,
dbo.MSP_EpmProject_UserView.[Effort Lead] AS
EffortLead, dbo.MSP_EpmProject_UserView.[Effort Short Name] AS
EffortShortName,
dbo.MSP_EpmProject_UserView.[Effort Type] AS
EffortType, dbo.MSP_EpmProject_UserView.[Strategic Initiative] AS
StrategicInitiative,
dbo.MSP_EpmResource_UserView.[Resource VP] AS
ResourceVP, dbo.MSP_EpmResource_UserView.[Resource Mgr] AS ResourceMgr,
dbo.MSP_EpmProject_UserView.[Proj Mgr Status] AS
ProjectMgrStatus, dbo.MSP_EpmProject_UserView.[Budget Code] AS BudgetCode
*/
FROM dbo.MSP_TimesheetProject INNER JOIN
dbo.MSP_TimesheetActual INNER JOIN
dbo.MSP_TimesheetLine ON
dbo.MSP_TimesheetActual.TimesheetLineUID =
dbo.MSP_TimesheetLine.TimesheetLineUID ON
dbo.MSP_TimesheetProject.ProjectNameUID =
dbo.MSP_TimesheetLine.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetResource ON
dbo.MSP_TimesheetActual.LastChangedResourceNameUID =
dbo.MSP_TimesheetResource.ResourceNameUID INNER JOIN
dbo.MSP_TimesheetTask ON
dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID INNER
JOIN
dbo.MSP_Timesheet ON
dbo.MSP_TimesheetLine.TimesheetUID = dbo.MSP_Timesheet.TimesheetUID INNER JOIN
dbo.MSP_TimesheetStatus ON
dbo.MSP_Timesheet.TimesheetStatusID =
dbo.MSP_TimesheetStatus.TimesheetStatusID INNER JOIN
dbo.MSP_TimesheetClass ON
dbo.MSP_TimesheetLine.ClassUID = dbo.MSP_TimesheetClass.ClassUID INNER JOIN
dbo.MSP_EpmResource_UserView ON
dbo.MSP_TimesheetResource.ResourceUID =
dbo.MSP_EpmResource_UserView.ResourceUID INNER JOIN
dbo.MSP_TimesheetPeriodStatus INNER JOIN
dbo.MSP_TimesheetPeriod ON
dbo.MSP_TimesheetPeriodStatus.PeriodStatusID =
dbo.MSP_TimesheetPeriod.PeriodStatusID ON
dbo.MSP_Timesheet.PeriodUID =
dbo.MSP_TimesheetPeriod.PeriodUID LEFT OUTER JOIN
dbo.MSP_EpmProject_UserView ON
dbo.MSP_TimesheetProject.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE (dbo.MSP_TimesheetActual.ActualWorkBillable > 0)
--
William Busby
Project Management
Jonathan H said:
Hmmm... okay. I know very little about this, being a PM who has been roped
into doing a bit of set-up.
I'm getting this error when building the cube:
[26/10/2007 12:27] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: The Application Server needs to have
Analysis Services DSO Component installed.
I understood I'd carried out a full PS installation, so any ideas on what
this is? A quick Google has revealed lots of hits and a bit of confusion.
Is there a straight-forward answer?
(and I realise I'm now probably straying from the point of this forum - so
feel free to tell me to do my own research if that's what is needed)
Thank you,
Jon
:
Jonathan --
You might want to try creating a custom Data Analysis view based on the
Timesheet cube for this purpose. Hope this helps.
Hello,
I wonder if anyone can help or point me in the right direction?
I'm having trouble getting data back out of PS 2007. I want to see
summary
data for all timesheets and/or Tasks booked by all resources across all
projects and admin tasks.
This feels like it should be a common enough request to have a 'push
button'
solution, but...
I can't seem to figure this out by trial and error (my normal approach),
the
Technet articles don't help me (and in fact some of the fields mentioned
just
don't seem to exist in my implementation) and I'm out of places to look.
If someone could help me out, or even point me in the right direction, it
would be much appreciated.
Regards,
Jonathan
