Compensating Records

What are compensating records?

First a little background. Whenever a work item is updated a new revision is created. These revisions are moved over to the Warehouse and stored in the various Work Item Dimension and Fact tables. Each revision will be in the Warehouse – however each date will not have a revision. Something like (ignoring actual schema for now):

Now if you wanted to ask a question about work items on a specific date then one way you can answer your question is to get the latest revision of the work item created before that date.

For example say you want to know the Remaining Work for a Work item on 4/22/2009. You could get all the work items whose Date is <= 4/22/2009 (Revisions 1, 2, and 3); then from these find the one with the largest Revision (Revision 3). This gives you a Remaining Work of 30.

However this requires you to do a MAX in your query and doesn’t work particularly well in the Cube. So we have added compensating records. These records are primarily used for running sum computations. Each one effectively negates, or compensates, the previous revision of that work item.

For example, using the same data from above:

1. 4/10/2009 – Work Item 1 is added.

2. 4/15/2009 – Work Item 1 is revised.

3. 4/20/2009 and 4/25/2009 – Work Item 1 is revised.

Now you can answer the same question as above by getting all the revisions of work items older than 4/22/2009 (Revisions 1, 1’, 2, 2’, and 3) and summing their Remaining Work. This gives you a Remaining Work of (50 + -50 + 40 + -40 + 30) = 30. This should be faster than without the compensating records (and it makes the Cube happy!).

What about that Record Count column?

You can apply the same running sum logic to the Record Count to get the number of work items as of a date. So you could ask how many work items were there on 4/22/2009. Again we sum up the values for all rows with a Date <= 4/22/2009 and we get (1 + -1 + 1 + -1 + 1) = 1. Not terribly exciting with our data, but you can see how if we had more work items this could be useful.

Again – with TFS

Alright so let’s look at some example using the TFS 2008 schema. Say I want to get the number of Active and Resolved Work Items assigned to me as of a given date. First thing I need to know is what tables I should look at:

Putting this together I see I need [Work Item History], [Work Item], and [Person]. So let’s build the query:

I can change the query slightly to get a better query plan (please send any further optimizations along – I’ll add them):

One thing I should note that can be confusing is that I am passing in 2009-03-19 00:00:00.000 for the Date. Most people would consider this not to be 19^th, but instead the 18^th (or so barely the 19^th that we shouldn’t use it for determining how many bugs were Active on the 19^th). However in the Warehouse we truncate the times off of the Date field. So if a work item was marked active at 6 PM on the 19^th the Date field in the Warehouse will have 2009-03-19 00:00:00.000. This is a long way of saying that 2009-03-19 is the 19^th, not the 18^th.

Alright getting the counts for someone on a given day was fairly straight forward. You may want to add in some logic to return Active and Resolved counts of 0 when the query returns NULLs – but this depends on how you will build your report.

Trending

How about a trend report? You basically need to execute the queries from the section above over a range of dates. We can use a Cross Apply here (again send along your optimized queries):

There is a lot of ways to get data out of the TFS Reporting Warehouse and into a report. Things like Excel and Reporting Services RDL Reports work well for people. Typically people use these tools to pull data from the Cube and display it in a consumable format. Of course these aren’t the only way to get data and display. Here I will look at using the relational Warehouse as a source and use simple XSL transforms to pretty up the data. This method will not work for everyone – in my opinion it is really only suitable for fairly simple reports.

Here I will create a report that displays my current work items (task, bugs, etc…). This is something like a “what am I supposed to be doing” report.

First we need to understand how to get the data from the relational warehouse. I will not cover all the ins and outs of the warehouse schema here. If you are looking for further information on this front take a look at the MSDN documentation (http://msdn.microsoft.com/en-us/library/ms244691.aspx) describing the various tables (Facts, Dimensions, and Fact Links) and their columns. I will look at getting Work Item data so here are the relevant tables:

Person – People’s names, Email, etc…
Work Item – Work item dimension information. This includes things like the Work Item ID, Revision, Title, etc…
Current Work Item – Current Work Item Fact information. This includes fact information and links to the appropriate dimensions. We will join this to the Work Item and person table to get the data we need in our report.

To get our report data our query would look like (alright we don't need CreatedData, but I am going to leave it in there):

SELECT * FROM
(
   SELECT wi.[System_ID]
  ,wi.[System_State]
  ,wi.[System_WorkItemType]
  ,wi.[System_Title]
  ,cwi.[System_CreatedDate]
  ,DATEDIFF(d, cwi.[System_CreatedDate], GETDATE()) AS [Age]
FROM [dbo].[Person] AS p
LEFT JOIN [dbo].[Current Work Item] AS cwi
  ON cwi.[Assigned To] = p.[__ID]
LEFT JOIN [dbo].[Work Item] AS wi
  ON cwi.[Work Item] = wi.[__ID]
WHERE p.[Person] = 'Nick Ericson' AND
  wi.[System_State] <> 'Closed'
) AS WorkItem
ORDER BY WorkItem.[System_State], WorkItem.[System_CreatedDate]
FOR XML AUTO, ROOT('WorkItems')

Which results in XML that looks like:

<WorkItems>
  <WorkItem System_ID="1" System_State="Active" System_WorkItemType="Task" System_Title="Set up: Set Permissions" System_CreatedDate="2008-02-21T00:00:00" Age="110" />
  <WorkItem System_ID="2" System_State="Active" System_WorkItemType="Task" System_Title="Set up: Migrate Source Code" System_CreatedDate="2008-02-21T00:00:00" Age="110" />
…
  <WorkItem System_ID="19" System_State="Active" System_WorkItemType="Bug" System_Title="Test work item 3 created: 3/19/2008 9:39:05 AM" System_CreatedDate="2008-03-19T00:00:00" Age="83" />
  <WorkItem System_ID="20" System_State="Active" System_WorkItemType="Bug" System_Title="Test work item 4 created: 3/19/2008 9:39:05 AM" System_CreatedDate="2008-03-19T00:00:00" Age="83" />
</WorkItems>

So now we need to apply the XSLT to transform this to HTML.  It is fairly simple to apply a transform via some managed code. One could use a web services to deliver the report to users with relatively little effort.  Another method would be to return the XML to the browser along with the xml-stylesheet tag (i.e. the two lines below added to the head of the XML).  This could also be done via a web service or ISAPI DLL.  I will not go into the details of how this would work here - if there is interest let me know. 

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="WorkItems.xsl"?>

where WorkItems.xsl is your XSL file.

Whichever method you choose, once the XSL (which is included below) is applied you will get the following report:

Bugs

Tasks

Now this is a fairly simple example but you can see that you can develop some reports fairly quickly using this method.

Below is the XSL. It isn’t the prettiest example out there but it is fairly straight forward to follow:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method='html' version='1.0' encoding='utf-8' indent='yes'/>

  <xsl:template match="/">
    <html>
      <body>
        <p>
          <h3 style="color: #3300CC">Bugs</h3>
        <table style="border-collapse:collapse;" width="100%">
          <tr style="background-color: #3300CC; color: #FFFFFF; font-weight: bold">
            <td colspan="2">ID</td>
            <td colspan="2">State</td>
            <td colspan="2">Title</td>
            <td>Age (days)</td>
          </tr>
          <xsl:for-each select="WorkItems/WorkItem[@System_WorkItemType='Bug']">
            <tr>
              <xsl:attribute name="style">
                <xsl:if test="position() mod 2 = 0">
                  background-color: #9999FF
                </xsl:if>
              </xsl:attribute>
              <td>
                <xsl:value-of select="@System_ID"/>
              </td>
              <td>&#x20;</td>
              <td>
                <xsl:value-of select="@System_State"/>
              </td>
              <td>&#x20;</td>
              <td>
                <xsl:value-of select="@System_Title"/>
              </td>
              <td>&#x20;</td>
              <td>
                <xsl:value-of select="@Age"/>
              </td>
            </tr>
          </xsl:for-each>
        </table>
        </p>
        <p>
          <h3 style="color: #3300CC">Tasks</h3>
          <table style="border-collapse:collapse;" width="100%">
            <tr style="background-color: #3300CC; color: #FFFFFF; font-weight: bold">
              <td colspan="2">ID</td>
              <td colspan="2">State</td>
              <td colspan="2">Title</td>
              <td>Age (days)</td>
            </tr>
            <xsl:for-each select="WorkItems/WorkItem[@System_WorkItemType='Task']">
              <tr>
                <xsl:attribute name="style">
                  <xsl:if test="position() mod 2 = 0">
                    background-color: #9999FF
                  </xsl:if>
                </xsl:attribute>

                <td>
                  <xsl:value-of select="@System_ID"/>
                </td>
                <td>&#x20;</td>
                <td>
                  <xsl:value-of select="@System_State"/>
                </td>
                <td>&#x20;</td>
                <td>
                  <xsl:value-of select="@System_Title"/>
                </td>
                <td>&#x20;</td>
                <td>
                  <xsl:value-of select="@Age"/>
                </td>
              </tr>
            </xsl:for-each>
          </table>
      </p>
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>