# Monday, 11 March 2013

 

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.image

 

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.

 

image

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 SNAGHTML492e9aabeen incorporated into the key WorkItemHistoryBK.

The GUID fwih.WorkItemHistoryBK appends the RecordCount column onto the end of the GUID.

 

 

image

 

Here is the original SQL using WorkItemSK.

 

 

 

 

 

image

 

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



.
Tags:

Monday, 11 March 2013 10:38:36 (GMT Standard Time, UTC+00:00)  #    Comments [0]


# Friday, 08 March 2013

 

Introduction

This is a description of how to put together a report that tracks change to some data over time. This example will be a basic Product Backlog report.

image

For this work we will use Report Builder 3.0 and use the query editor in MS SQL Management Studio to write the SQL.

You need to have a way of writing, running, tuning, and testing the SQL for your reports. One way to do this is with SQL Management Studio; it provides good visibility of the structure of TFS databases and provides good tools for creating the SQL that will ultimately be used in the report. 

clip_image025clip_image002

You could use the graphical UI or SQL text editor found in Report Builder;  but to tweak and develop the SQL you will most likely discover that you need to get more control and feedback than the Report Builder editor provides. The editor in SQL Server Management Studio has Intellisense which is useful. Another good alternative would be to use Visual Studio.

For this example report we will need to read data from a couple of  tables in the TFS Warehouse; specifically DimWorkitem and FactWorkitemHistory.

So we need to set up SQL Management Studio to connect to TFS SQL server; which when connected looks like the image on the right.

We can then select DimWorkItem and click ‘New Query’.

We’ll write some SQL that will generate an array of dates for the X axis and for each of those dates sum up the effort outstanding on that date. The full SQL is appended at the end of this article.

Writing the SQL for the report.

We need a start date and end date for the array of dates. We’ll simply get the minimum and maximum dates from the workitem data; so selecting from DimWorkitem we’ll set @vDataSetStartDate and @vDataSetEndDate as below. Also we’ll limit this example to the ‘Product Backlog Item’ workitem type. No effort is made here to restrict the data to a particular Project Collection or Team Project; so be aware that the volume of data returned could be quite large.

imageimage

With the start and end date handy we can then populate an array of dates. We’ll put the dates into a temporary table called @tblDates. You could use select * from @tblDates to see the dates in this table (see the inset to the right); remember to remove the select * before using the SQL in a report.

image

For each of those dates in @tblDates we’ll get all the latest revisions of workitems up to and including the DayDate. For each date we store the data that will be used for filtering in the last step where we will sum up remaining effort based on workitems that are in the ‘not done’ state. We will need the WorkItemSK (which is the unique id for a revision) to join into the FactWorkitemHistory. The ID and the Rev columns are for reference and debug. Use select * from @tblDatesWithWorkitems to see the dates and data in this table; remember to remove it before using the SQL in the report.

imageimage

The data we want requires joining DimWorkitem with FactWorkitemHistory; INNER JOIN on the WorkitemSK. There are rows in FactWorkitemHistory that are irrelevant; so filter them out using fwih.RecordCount > -1.

Finally filter by system state for the ‘not done’ workitems from the temporary table @tblDatesWithWorkitems and SUM up Effort for each day by grouping on DayDate. So for example on 2012-10-02 join each row that matches our state criteria to FactWorkitemHistory and sum up.imageimage

image

 

So that’s the data ready to go.

Importantly it is in the exact shape and format that we need for our report. An array of Dates with associated backlog of effort.

We could enhance this data with additional information about sprints, teams and velocity.

We could add filtering for Project Collection, Team Project, Iteration, Areas and the like. 

That will be a subject for another article!

Getting the data into a report. Creating a DataSet.

Open up MS SQL Server Report Builder. Close the wizard and go straight into the application. Create a Data Source pointing at the TFS SQL Server.

image

Add a data source that points at the TFS warehouse. Here I use the ‘Use a connection embedded in my report’ option with connection type ‘Microsoft SQL Server’ and name the connection TFS2012.

imageimageimage

Create a dataset using the previously created data source.

SNAGHTML62b04c1

In the Dataset Properties dialog open the Query Designer, select ‘Edit as Text’ and paste in the SQL that was previously created in SQL Server Management Studio. Run the SQL to check that it works ok. Although of course if you are using SQL Server Management Studio to develop this type of report SQL you will have already seen that it works; so you would not normally need to do this.

imageimage

Add a table to the report so that you can view the data that is going to be charted. This is a useful way to verify that the data looks shipshape! The table can be deleted later. Lift click and drag DayDate and Effort into the table.

imageimage

Add a chart.

imageimage

Select the first Line type chart.

Then left Click drag DayDate onto the chart as a category group.

image

Then left Click drag Effort as a value.

image

Use the expression editor and edit ‘Effort’ to remove the Sum() and then OK to save the modified expression.

image

Run the chart.

That’s it. You have day by day data in a SQL Report.

image

 

  • Summary.

  • The approach taken here is a pattern used in many SQL Reports that require data to be charted against days.
  • Use SQL to generate an array of dates into a temporary table. These dates should be limited by the date range of the data being charted. For each Date get the maximum revision for the workitems with change date falling before or on the Date. Use additional filters where appropriate to define the workitems; but remember the aim is to get the latest revision of each workitem for the Date. So for that date we can look at things as they were on the day; as we were looking at them through a Query run on that day. The latest revision may not have taken place that day; it most likely will be an earlier day. Finally SUM up by grouping the data by Date.

 

The complete SQL.

SET ANSI_WARNINGS ON -- Specifies SQL-92 standard behaviour for several error conditions

SET XACT_ABORT ON -- If error, the entire transaction is terminated

SET NOCOUNT ON -- Stops the message count of the number of rows affected SQL statement

SET DATEFIRST 7 -- Sunday is first day of week -- this is actually the default

-------------------------------------------------------------------------------------------

-- Some basic Product Backlog Report Data

-- Demonstrates array of dates and attaching data into that array

-- Version March 2013 - RippleRock

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-- 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

,WorkitemSK INT

,System_State NVARCHAR(256)

)

INSERT INTO @tblDatesWithWorkitems (

DayDate

,System_ID

,System_Rev

,WorkItemSK

,System_State

)

SELECT datesArray.DayDate

,dwi.System_ID

,dwi.System_Rev

,dwi.WorkItemSK

,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 (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.WorkitemSK = fwih.WorkItemSK

AND fwih.RecordCount > - 1

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



.
Tags:

Friday, 08 March 2013 22:33:32 (GMT Standard Time, UTC+00:00)  #    Comments [0]