Introduction
In many organisations a TFS installation will comprise of more than one project collection. The data from all project collections is stored in the same TFS warehouse.
This is useful in that it means cross organisation reports can be written; Views of whole organisation products and backlogs are feasible.
The data from multiple project collections sits in the same tables; so keys such as WorkItemSK, System_ID and System_Rev are not unique across collections.
When multiple collections were introduced for TFS 2010, GUIDs were introduced into the TFS tables. Where SQL is not scoped to a particular collection, GUIDs must be used in joins. We need to use these GUID keys in joins; rather than integer keys such as WorkItemSK.
Updating SQL to use the GUID key; WorkItemBK
So looking back at the ‘How to create a Day by Day based SQL Report’ example from my previous blog; ReportsHowToCreateADayByDayBasedSQLReport the following steps can be followed to update that SQL query for a multi collection installation.
The WorkItemBK must be used in the join between DimWorkItem and FactWorkitemHistory.
A couple of changes need to be made for this to happen. This change would need to be made to any SQL that makes joins using WorkItemSK; where the join is not scoped to the project collection.
Basically we replace WorkItemSK with WorkItemBK.
WorkItemBK is a GUID; so provides the uniqueness that is needed to make joins between tables in the multiple collection scenario.
Here is the original SQL that we used to get the last revision of workitems for each day date; collecting WorkitemSK as a key to be used later in the join to FactWorkItemHistory.
Here is the same SQL updated to collect the GUID; replacing the WorkItemSK with WorkitemBK.
Note that we still need to use the System_ID and System_Rev to get the latest revision; so to ensure that this is unique we scope the sub query to the project collection by adding the TeamProjectCollectionSK into the sub query join criteria.
Lastly the SQL that sums up the Effort for Product Backlog must be updated to use the collected GUID WorkItemBK in the join to FactWorkItemHistory.
There is an interesting trick involved in using the GUID as can be seen below.
Notice that previously I used fwih.RecordCount > –1 in the join.
This is no longer required as the record count has 
been incorporated into the key WorkItemHistoryBK.
The GUID fwih.WorkItemHistoryBK appends the RecordCount column onto the end of the GUID.
Here is the original SQL using WorkItemSK.
Here is the SQL updated to use WorkItemBK.
Note how ‘|1’ has been appended onto WorkItemBK in the join.
Summary
Use the GUID keys (e.g. WorkItemBK) in joins rather than the integer keys (e.g. WorkItemSK).
You may want to review existing reports where WorkItemSK is used.
The example SQL will now list an organisation product backlog (for Product Backlog Items) where multiple project collections are used.
Product Backlog SQL updated to use GUIDs
| SET ANSI_WARNINGS ON -- Specifies SQL-92 standard behaviour for several error conditions
SET XACT_ABORT ON -- If a Transact-SQL statement raises a run-time error, the entire transaction is terminated
SET NOCOUNT ON -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement
SET DATEFIRST 7 -- Sunday is first day of week -- this is actually the default -------------------------------------------------------------------------------------------
-- Some basic Burndown Report Data
-- Demonstrates array of dates and attaching data into that array
-- Version March 2013 - RippleRock
-- Updated to use GUID keys in joins
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
-- Get the minimum date and maximum date from the data
-------------------------------------------------------------------------------------------
DECLARE @vDataSetStartDate AS DATE
DECLARE @vDataSetEndDate AS DATE SELECT @vDataSetStartDate = CONVERT(DATE, MIN(dwi.System_ChangedDate), 112)
,@vDataSetEndDate = CONVERT(DATE, MAX(dwi.System_ChangedDate), 112)
FROM dbo.DimWorkItem dwi
WHERE dwi.System_WorkItemType = 'Product Backlog Item' -------------------------------------------------------------------------------------------
-- Create an array of dates; this will be the basis of the report x axis
-- into @tblDates
-------------------------------------------------------------------------------------------
DECLARE @tblDates AS TABLE (DayDate DATE NOT NULL); WITH ReleaseDates (ReleaseDate)
AS (
SELECT ISNULL(@vDataSetStartDate, GETDATE()) AS ReleaseDate
UNION ALL
SELECT DATEADD(d, 1, ReleaseDate) AS ReleaseDate
FROM ReleaseDates
WHERE DATEADD(d, 1, ReleaseDate) <= ISNULL(@vDataSetEndDate, GETDATE())
)
INSERT INTO @tblDates (DayDate)
SELECT CONVERT(DATE, rd.[ReleaseDate], 112)
FROM ReleaseDates rd
OPTION (MAXRECURSION 0) -------------------------------------------------------------------------------------------
-- Join the last revision of each workitem under the DayDate into the dates
-------------------------------------------------------------------------------------------
DECLARE @tblDatesWithWorkitems AS TABLE (
DayDate DATE NOT NULL
,System_ID INT
,System_Rev INT
,WorkItemBK NVARCHAR(128)
,System_State NVARCHAR(256)
) INSERT INTO @tblDatesWithWorkitems (
DayDate
,System_ID
,System_Rev
,WorkItemBK
,System_State
)
SELECT datesArray.DayDate
,dwi.System_ID
,dwi.System_Rev
,dwi.WorkItemBK
,dwi.System_State
FROM @tblDates datesArray
,dbo.DimWorkItem dwi
WHERE dwi.System_Rev IN (
SELECT MAX(System_Rev) -- we want the last revision of each workitem under the DayDate
FROM dbo.DimWorkItem wil
WHERE wil.System_ID = dwi.System_ID AND wil.TeamProjectCollectionSK = dwi.TeamProjectCollectionSK
AND (CONVERT(DATE, wil.System_ChangedDate, 112) <= datesArray.DayDate)
)
-- capture max rev
-- so the last change up to that day for each of those Product Backlog Items
-- keep any data that we will be filtering by in the next stage; so in this case System_State
AND dwi.System_WorkItemType = 'Product Backlog Item'
ORDER BY DayDate -------------------------------------------------------------------------------------------
-- Return the data we are interested in back to the report
-- Summed effort of Product Backlog Items for each day (which we filtered out previously)
-- that are in a 'not done state'
-------------------------------------------------------------------------------------------
SELECT datesArray.DayDate
,ISNULL(SUM(fwih.Microsoft_VSTS_Scheduling_Effort), 0) AS Effort
FROM @tblDatesWithWorkitems datesArray
INNER JOIN dbo.FactWorkItemHistory fwih ON datesArray.WorkItemBK + '|1' = fwih.WorkItemHistoryBK
WHERE
-- filter by system state to get the 'not done' effort for that date
(
datesArray.System_State <> 'Done'
AND datesArray.System_State <> 'Removed'
AND datesArray.System_State <> 'Resolved'
)
GROUP BY DayDate -- we sum up the filtered effort for each day
ORDER BY DayDate |
.