SCSM DW Query - Billable Time

Adam_Dzyacky

The Time Tracker App rocks. So in the event you're using it and still messing around with SQL queries (or just looking to have a starting point) I figured this was worth sharing. The following SQL query is to be run against your DWDataMart and will retrieve the BillableTime against Incidents, Manual Activities, Review Activities, and Change Requests. It probably goes without saying, but said query could then be dropped into Report Builder and then published into the SCSM Console. That way you could have a pie chart for the analyst/date range that you pick to show where most of their work is going. I think combined with the awesomesauce fantastic post answer made by @joivan_hedrick on integrating the Time Tracker App into the portal you stand to gain some worthwhile data to mine.

The following takes 3 parameters:
StartDate (datetime)
EndDate (datetime)
Username (string)

SELECT Activity.id AS [Id]
  , Activity.Title AS [Title]
  , CASE 
   WHEN Activity.Id LIKE 'MA%' THEN 'Manual Activity'
   WHEN Activity.Id LIKE 'RA%' THEN 'Review Activity'
   ELSE 'Other Activity'
  END AS [Work Item Type]
  , ActivityAreaStrings.DisplayName [Class/Area]
  , AssignedUser.DisplayName AS [Worked By]
  , BT.TimeInMinutes AS [Minutes Worked]
FROM  ActivityDimvw AS Activity
INNER JOIN WorkItemDimvw AS WI ON Activity.EntityDimKey = WI.EntityDimKey
INNER JOIN WorkItemHASBillableTimeFactvw AS WIBT ON WI.WorkItemDimKey = WIBT.WorkItemDimKey
INNER JOIN BillableTimeDimvw AS BT ON WIBT.WorkItemHASBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey
INNER JOIN BillableTimeHasWorkingUserFactvw AS BTWU ON wibt.WorkItemHASBillableTime_BillableTimeDimKey = btwu.BillableTimeDimKey
INNER JOIN UserDimvw AS AssignedUser ON BTWU.BillableTimeHASWorkingUser_UserDimKey = AssignedUser.UserDimKey
INNER JOIN  ActivityAreavw AS ActivityAreaOutrigger ON ActivityAreaOutrigger.ActivityAreaId = Activity.Area_ActivityAreaId
 INNER JOIN DisplayStringDimvw ActivityAreaStrings ON ActivityAreaStrings.BaseManagedEntityId = ActivityAreaOutrigger.EnumTypeId
WHERE Activity.CreatedDate > @StartDate
   AND Activity.CreatedDate < @EndDate
   AND bt.TimeInMinutes IS NOT NULL
   AND AssignedUser.UserName = @Analyst
   AND ActivityAreaStrings.LanguageCode = 'ENU'
UNION
SELECT CR.Id
  , CR.Title
  , 'Change Request'
  , CRAreaStrings.DisplayName
  , AssignedUser.DisplayName
  , bt.TimeInMinutes
FROM  ChangeRequestDimvw CR
INNER JOIN WorkItemDimvw WI ON WI.EntityDimKey = CR.EntityDimKey
INNER JOIN WorkItemHASBillableTimeFactvw WIBT ON WI.WorkItemDimKey = WIBT.WorkItemDimKey
INNER JOIN BillableTimeDimvw BT ON WIBT.WorkItemHASBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey
INNER JOIN BillableTimeHASWorkingUserFactvw BTWU ON wibt.WorkItemHASBillableTime_BillableTimeDimKey = BTWU.BillableTimeDimKey
INNER JOIN UserDimvw AssignedUser ON BTWU.BillableTimeHASWorkingUser_UserDimKey = AssignedUser.UserDimKey
INNER JOIN ChangeAreavw CRAreaOutrigger ON CRAreaOutrigger.ChangeAreaId = CR.Area_ChangeAreaId
 INNER JOIN DisplayStringDimvw CRAreaStrings ON CRAreaOutrigger.EnumTypeId = CRAreaStrings.BaseManagedEntityId
WHERE CR.CreatedDate > @StartDate
  AND CR.CreatedDate < @EndDate
  AND BT.TimeInMinutes IS NOT NULL
  AND AssignedUser.UserName = @Analyst
  AND CRAreaStrings.LanguageCode = 'ENU'
UNION
select ir.id, WI.Title, 'Incident', irClassStrings.DisplayName, AssignedUser.DisplayName, bt.TimeInMinutes
from incidentdimvw AS ir
 INNER JOIN WorkItemDimvw AS wi ON wi.EntityDimKey = ir.EntityDimKey
 INNER JOIN WorkItemHASBillableTimeFactvw AS wibt ON wi.WorkItemDimKey = wibt.WorkItemDimKey
 INNER JOIN BillableTimeDimvw AS bt ON wibt.WorkItemHASBillableTime_BillableTimeDimKey = bt.BillableTimeDimKey
 INNER JOIN BillableTimeHASWorkingUserFactvw AS btwu ON wibt.WorkItemHasBillableTime_BillableTimeDimKey = btwu.BillableTimeDimKey
 INNER JOIN UserDimvw AS AssignedUser ON btwu.BillableTimeHASWorkingUser_UserDimKey = AssignedUser.UserDimKey
 INNER JOIN IncidentClassificationvw AS irClassOutrigger ON ir.ClASsificatiON_IncidentClASsificatiONId = irClassOutrigger.IncidentClASsificatiONId
  INNER JOIN DisplayStringDimvw AS irClassStrings ON irClassOutrigger.enumtypeid = irClassStrings.BaseManagedEntityId
where ((ir.CreatedDate >= @StartDate) and (ir.CreatedDate < @EndDate + 1))
 and AssignedUser.UserName = @Analyst
 and irClassStrings.LanguageCode = 'ENU'
 AND BT.TimeInMinutes IS NOT NULL
ORDER BY [Id]

AJ_Wittenbrink

Nice work!

I have been using a more, aggregate approach (I port data to a staging etc..) based on each work item.  Within the pull I use a sub-select query to pull aggregate time and number of analysts against a work item (I use WorkItemDimvw view (alias of WI) to tie it in).

I will probably steal your code once the requests for more detail comes in.

<div>--Billable Time	</div><div>(SELECT&nbsp;</div><div>	SUM(CAST(BT.TimeInMinutes AS DECIMAL(10,3))) / 60</div><div>	FROM BillableTimeDimvw BT</div><div>		LEFT OUTER JOIN WorkItemHasBillableTimeFactvw WI2BT
			ON WI2BT.WorkItemHasBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey</div><div>	WHERE WI2BT.WorkItemDimKey = WI.WorkItemDimKey</div><div>	GROUP BY WI2BT.WorkItemDimKey</div><div>		) AS 'Analyst Effort',</div><div></div><div>
<br></div><div>--Billable Time	# people Worked on Sev 1

</div><div>(SELECT&nbsp;</div><div>	COUNT( DISTINCT BT2U.BillableTimeHasWorkingUser_UserDimKey)</div><div>	FROM BillableTimeDimvw BT</div><div>		LEFT OUTER JOIN WorkItemHasBillableTimeFactvw WI2BT</div><div>		ON WI2BT.WorkItemHasBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey</div><div>		LEFT OUTER JOIN [BillableTimeHasWorkingUserFactvw] BT2U</div><div>		ON BT2U.BillableTimeDimKey = BT.BillableTimeDimKey</div><div>		WHERE WI2BT.WorkItemDimKey = WI.WorkItemDimKey</div><div>		) AS 'Analyst Count',
</div>

Daniel_Polivka1

I am going to breathe life into this thread in the hopes that someone has been more successful than I have been. Has anyone successfully figured out how to get billable time information from the ServiceManagement DB? I'm at a point where I'm spinning my wheels trying to figure out what property to pivot on to join these two tables. My ultimate goal is to be able to produce something like this:

Here is the chicken scratch I have so far, in case anyone wants to take a crack at it, or can see what I've done wrong:

select *

--WIIR.Base_Name as 'Name',

--WIIR.System_WorkItemHasBillableTime_Count as 'BTCount',

--WI.AssignedUser as 'AssignedUser'

from workitem as WI

inner join cachert.csnCached_MT_System_WorkItem_Incident as WIIR on WI.WorkItemId = WIIR.Base_Name

inner join dbo.cpex_SmRelationship as rel on wi.Id = rel.SourceEntityId

where WIIR.System_WorkItemHasBillableTime_Count IS NOT NULL

and rel.relationshiptypeid = '33835caa-05f9-f881-7bbe-e407dfdb6c55' -- WI has bill time rel class id

--and wi.WorkItemId = 'IR8507'

select *

from cachert.csnCached_MT_System_WorkItem_BillableTime as billabletime

where billabletime.LanguageCode = 'ENU'

--and billabletime.TimeInMinutes = '80'

Adam_Dzyacky

Oh hey I remember this thread!

Anyway. Super close here. I think you're looking for something like this:

select ir.Base_Name,
    ir.Title,
    time.TimeInMinutes,
    smuser.DisplayName
from cachert.csnCached_MT_System_WorkItem_Incident as ir
    inner join dbo.cpex_SmRelationship as workitemrelationships on ir.Guid = workitemrelationships.SourceEntityId
    inner join cachert.csnCached_MT_System_WorkItem_BillableTime as time on workitemrelationships.TargetEntityId = time.Guid
    inner join CI$User as smUser on time.System_WorkItem_BillableTimeHasWorkingUser_BaseManagedEntityId = smuser.Id
where ir.LanguageCode = 'ENU'
and time.LanguageCode = 'ENU'

What this hinges on as it looks like you've already observed is the cpex_SmRelationship table.

• First, all of the relationships where the Source is an Incident
• Second, all of the Billable Time entries whose Source is the previously joined Incident
• Finally, joining the User table so you can pull other properties beyond the name such as the title, department, etc.