Team System Reporting - A scary, but powerful environment

Why scary ... well, not being a SQL OLTP and OLAP guru, the exploration through the TFS Reporting engine room has proven to be a scary one. This blog contains the notes I made for myself in an attempt to summarise the environment and I am hoping that the SQL subject experts will come to the rescue where necessary. JPF "alias Professor" loves this ecosystem and if all goes well, he will contribute and clarify as well.

Basic TFS Reporting Architecture

As illustrated the TFS Architecture is composed of four core components:

1. Operational Stores
   • Operation stores collect data from each Team System component, such as build, version control and work items.
2. Adapters
   • Adapters manage a number of tasks such as adapting and customising data warehouse when template changes are made, managing cube processing cycles and the movement of data to the TFS data warehouse. The movement of data from operational stores to the relational database is configured to occur at an interval of 3600 seconds by default. See http://dotnet.org.za/willy/archive/2007/12/06/tfs-qa-how-do-i-change-the-processing-interval-for-the-data-warehouse.aspx if you want to change this default interval. The adapters are also synchronised to ensure that data is only moved to the cube once the movement of data to the data warehouse relational database is complete.
3. Relational database (fact tables)
   • The relational database correlates and normalises the data from each operational store, containing measures, dimensions and details. (See below for an explanation of these cryptic terms)
4. OLAP Cube
   • Has nothing to do with the most frustrating invention ever, the rubi cube. The OLAP cube aggregates and optimises the team project data to be processed in reports.

OLAP ... what is it and what are all the strange terms floating around in cubes?

• Cube
  • OLAP database structure that enables end users to navigate data in a prescribed manner
• OLAP
  • Online Analytical Processing
• Measure
  • Quantity in a cube that can be summarised
• Dimension
  • Structure in a cube used to summarise measures. It contains members as shown below:
     
     
• Dimension Member
  • Particular instance of a dimension
• Hierarchy
  • Structured view on dimensions
• Row Axis
  • Set of dimension members that comprise the row labels in a pivot table
• Column Axis
  • Set of dimension members that comprise the column labels in a pivot table
• Filter
• Set of dimension members that constrain the query in a pivot table
• Snowflake versus Star schema
• TFS data warehouse uses a star schema ... but what is it and what is a snowflake?!?
• Star Schema
• Simplest data warehouse schema resembling a star, with points radiating from a centre. The centre of the star consists of one or more fact tables and the points of the star are the dimension tables.
  
• Snowflake Schema
• More complex schema than the star schema, with normalised dimensions tables normalized.
  

TFS Reporting Architecture ... closer up

Now we know the basic TFS reporting architecture and the (snow)flaky OLAP terminology, we can have a brief peek at the TFS Process Template, which amongst other TFS Team Project artifacts, defines reporting aspects as well.

Process Templates

A work item field can be decorated with three attributes, that define the way the field makes its way into the magical cube.

• Measure
• Defines a quantity that can be summarised.
• Dimension
• A field that is used to summarise measures.
• Detail
• Defines that the field is included in the data warehouse, but not the cube.

The following view of a pivot table relates back to the above attributes:

Process Template XML Exploration

You guessed it, the Process Template is defined by a set of XML files ... ooohhh, how we love XML. Fortunately Microsoft has shipped a Process Editor tool with the Power Tools, making the tedious notepad editing of the process template files a bit more enjoyable. Scrounging through the work item definition files in the ...Scrum - v2.0\WorkItem Tracking directory and its subdirectories of the Light Weight Scrum Process template, I found an example for each:

<FIELD name="Remaining Work" refname="Microsoft.VSTS.Scheduling.RemainingWork" type="Double" reportable="measure">
    <HELPTEXT>An estimate of the number of hours remaining to complete the BackLogItem</HELPTEXT>
</FIELD>

<FIELD name="Start Date" refname="Microsoft.VSTS.Scheduling.StartDate" type="DateTime" reportable="dimension">
    <HELPTEXT>The date to start the BackLogItem</HELPTEXT>
</FIELD>

<FIELD reportable="detail" type="String" name="Test Name" refname="Microsoft.VSTS.Test.TestName">
    <HELPTEXT>The name of the test that found this Defect</HELPTEXT>
</FIELD>

Using the more GUI focused Process Editor, we get the following view:

Sample journey ... "Which area seems to be the most active in terms of work items?"

Let's talk about cubes again and look at the following relationship of fact and dimension tables. This perspective or data view, allows us to produce a pivot table and answer many questions, such as how many active tasks are assigned to projects and how many are assigned to each stakeholder. We can also answer our question "which area seems to be the most active in terms of work items".

PS: The data schema does not show non-key fields .... else we would have to squash the illustration and attach a magnifying glass to this post.

Using the Current Work Item Count measure and the dimensions making up the current work item perspective allow us to summarise and filter the measures, producing the following graph, which at a glance shows that the sample Home Loans project is probably the top ranking area in our demo environment. The beauty of these visual reports is that you do not have to look at all the detail, to understand the "state of the nation" at a glance.

Finally ... for today ... Report Management

The topic of report creation and customisation is a topic on its own and at this point we will merely list the possibilities and tools. In terms of the tools the main battle ships are Microsoft Excel Pivot Tables, SQL Reporting Services Report Designer and Report Builder. The possibilities are vast, from pivot tables and pivot chart reports, to drilldown, navigation, area path breakdown and calculation reports. In essence you need to be comfortable with the tools, the OLAP world and the TFS data warehouse artifacts before you embark on the daunting journey of report creation. It is, however, astounding what reports can be built and how the value-add of TFS can be extended for the management stakeholders of software solutions, such as the following slick overview of work items from the Microsoft Deep-Dive training material:

This was a brief excursion through the wonderful world of TFS reporting. Next post will be on process customisation, where the reports should find their way back again.

If your brain hurts as much as mine does at this stage, then at least we know that we are not alone. Just remember ... dimension=summary of measure and measure=quantity summary...