Reporting on Test Cases

8 11 2013

For a customer I am creating reports related to Test Cases. In order to retrieve the data, I use the Tfs_Warehouse database (relational warehouse). For several situations, Microsoft has conveniently created SQL Server Views on top of the tables available in the database. For Test Cases the View “vDimTestCaseOverlay” is available. In my case however, this View had some “problems”…

Read the rest of this entry »





Delta-N wins Western European ALM Partner Award

10 10 2013

Originally posted on The Road to ALM:

ALMaward_kleinerThis week I attended the Western European ALM Partner Summit in my role as Business Unit Manager of the ALM unit within Delta-N.

The summit was 2 days and was held in Haarlem. The summit is meant for partners who are active in the ALM space and provide them with information about the business and the Microsoft proposition.

At the end of the 2 days traditionally awards are given to the companies who excelled the last year in providing ALM Services. Based on a set of criteria you can earn the award.

I am proud to say that Delta-N won this award for the sixth time in a row. For us this means a great reward of the things we are doing and an extra push to continue our business.

I’d like to mention my great colleagues Jasper, Fokko and Mark. Without their efforts the last year this…

View original 8 more words





SDN June 2013 conference – Additional links

17 06 2013

During my talk at the SDN conference, I told about misconceptions regarding unit testing. Some people were looking at me with a frown when I told this. I would like to refer to the blog of Steve Fenton who wrote about this subject and I rate his post on this topic quite high;

Part 1: http://www.stevefenton.co.uk/Content/Blog/Date/201305/Blog/My-Unit-Testing-Epiphany/

Part 2: http://www.stevefenton.co.uk/Content/Blog/Date/201305/Blog/My-Unit-Testing-Epiphany-Continued/

During my talk I didn’t deep-dive into this topic because it was not my goal to tell people about unit testing.

Apart from that I got some questions about supported technologies and platforms for Coded UI testing. More information can be found here: http://msdn.microsoft.com/en-us/library/dd380742.aspx





SDN June 2013 conference slidedeck – Getting started with automated testing

14 06 2013

Today I presented at the SDN conference about getting started with automated testing with Visual Studio 2012 and Team Foundation Server 2012. I want to thank the attendees for visiting my session. The slidedeck is available here: SDN 201306 – Fokko Veegens – Geautomatiseerd testen.





TFS 2010 Build Performance Report

26 03 2013

For a customer I am analyzing the performance of the builds. They have Gated Check-in builds which take more than 30 minutes. The amount of Visual Studio Solutions built varies from 8 to 12. I need to know which part of the Build takes most time.

Using the standard Build log in Visual Studio makes this task a hard one, having to scroll up and down, finding the parts of the process taking most time. Also the hierarchy in the log is hardly visible when scrolling through an endless list of build messages.

Visual Studio Build Report

To work my way around this, I came up with the idea to create a custom SQL Server Reporting Services report based on top of the data in the Collection database. Although I do not recommend to report based on the operational database of TFS, sometimes there is no other way. The build results are available in the reporting databases, but not at the level of detail I need, so I took a dive into the collection database. I also wanted the hierarchy which TFS creates in the build report to be collapsible/expandable for easy navigation and drilldown experience. The final result is downloadable at the bottom of this post.

First off, a datasource on the report server is needed pointing to the collection database. The name of the collection database depends on the name of the collection. If the name is “DefaultCollection” (which it is by default) then the name of the database is Tfs_DefaultCollection. In my case, the datasource uses the default Report Services user used by TFS;

Datasource

Then some simple queries were created to fill the parameters which would be the Build Definition to use and the actual Build to show;

List of all Build Definitions (named “BuildDefinitions”):

SELECT   DefinitionId, 
  REPLACE(REPLACE(DefinitionName, '>', '_'), '\', '') DefinitionName
FROM  tbl_BuildDefinition
ORDER BY DefinitionName

As you can see, TFS replaces the underscore in the Build (Definition) name with a bigger-than sign and all of them are ending with a backslash.

List of all Builds of the selected Build Definition (named “Builds”):

SELECT  BuildId,
  REPLACE(REPLACE(BuildNumber, '>', '_'), '\', '') BuildNumber
FROM  tbl_Build
WHERE  DefinitionId = @BuildDefinitionID
       AND Deleted = 0
ORDER BY FinishTime DESC

When the second dataset is added to the report, it automatically creates a parameter, whose settings need to be updated in order to show the list;

Parameter settings

Then the last and most important query must be written (named “BuildDetails”); the one retrieving the data based on the two parameters;

SELECT bi.NodeId, 
       bi.ParentId, 
       bity.TypeName, 
       bi.LastModifiedDate, 
       bi.LastModifiedBy, 
       dpt.FieldValue DisplayText, 
       CONVERT(DATETIME2, st.FieldValue) StartTime, 
       CONVERT(DATETIME2, ft.FieldValue) FinishTime,
       sp.FieldValue ServerPath
FROM tbl_BuildInformation bi
       INNER JOIN tbl_BuildInformationType bity on bi.NodeType = bity.NodeType
       LEFT OUTER JOIN (SELECT NodeId, 
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'DisplayText') dpt ON bi.NodeId = dpt.NodeId
       LEFT OUTER JOIN (SELECT NodeId, 
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'StartTime') st ON bi.NodeId = st.NodeId
       LEFT OUTER JOIN (SELECT NodeId,
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'FinishTime') ft ON bi.NodeId = ft.NodeId
       LEFT OUTER JOIN (SELECT NodeId, 
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'ServerPath') sp ON bi.NodeId = sp.NodeId
WHERE BuildId = @BuildID
      AND bity.TypeName NOT IN ('BuildMessage', 
                                'BuildWarning',
                                'BuildError', 
                                'ExternalLink', 
                                'ConfigurationSummary')
ORDER BY NodeId

Some explanation;

  • tbl_BuildInformation is a table with a relationship to itself in order to create a hierarchy of build log entries. It contains the same amount of rows as displayed in the Visual Studio Build Report (except for the first and last one which are metadata)
  • tbl_BuildInformationType contains the types of rows which can appear in the Build Log, e.g. ActivityTracking or BuildError
  • tbl_BuildInformationField contains the information fields per row in tbl_BuildInformation. The amount of fields varies per BuildInformationType

After saving this dataset, another parameter is created which should use the “Builds” dataset as the list source;

Parameter settings

Then it is time to create the report itself:

  • Insert a table on the report
  • Add the necessary fields
  • Edit the row group properties and add “NodeId” as the “group on” field
  • Choose the visibility tab of the row group properties and set the initial visibility to “Hide”. Also set the display to be toggle by the name of the first textbox of the detail row
  • Use a DateDiff to calculate the time between the start and finish date/time of the row

Then, upload the report using ReportBuilder, SQL Server Business Intelligence Studio, the web interface of SQL Server Reporting Services and set the DataSource correctly. This should result in a report like this:

Custom Build Report

Disclaimer:

  • The report which can be downloaded below is only designed for SQL Server 2008 or SQL Server 2008 R2 in combination with TFS 2010. Other combinations have not been tested. I am planning to publish a TFS 2012 version as well soon!
  • Of course much more is possible, by applying some gold-plating, but the essence is a working report giving essential information

The report can be downloaded by using this link: http://sdrv.ms/14qAFmS





TechDays 2013 NL Slidedeck

7 03 2013

Today I held my session about customizing TFS 2012 and as promised you’ll find the downloadlink to my slidedeck below. For the non-Dutch speakers I have to disappoint you; the presenation is in Dutch. It contains however some information which is not language-specific.

Techdays 2013 – Customizing TFS 2012

Edit: On request I created an English version of my presentation;

Techdays 2013 – Customizing TFS 2012 – English





Speaker @ TechDays NL 2013

22 01 2013

I will speak about customizing TFS 2012 at the Microsoft TechDays 2013 The Netherlands (http://www.techdays.nl).

http://www.microsoft.com/netherlands/techdays/SpeakerDetail.aspx?speakerId=1996

After the session, the slidedeck will be available for download on this blog. If you have any questions about my talk, please visit the Delta-N stand afterwards, I will be available there to answer them.

See you at TechDays 2013!








Follow

Get every new post delivered to your Inbox.