Test Cases Statistics with SSRS and TFS Data Warehouse

I’m proud to present to you the first guest blog post on Automate The Planet. Its theme will be how to setup beautiful and useful test cases statistics using SSRS and TFS Data Warehouse. The author of the article is my colleague – Kristina Bankova, who is a Quality Assurance Engineer at Telerik.

About TFS Data Warehouse

Team Foundation Server includes a data warehouse where data about work items and builds is stored. Team Foundation uses this data for its built-in reporting functionality. The warehouse includes both a relational and an OLAP database. The relational database is organized in an approximate star schema, and the OLAP database is a data cube derived from the relational database.

Tfs_Warehouse is the relational warehouse that is used in the statistics with SSRS explained below.
Due to performance reasons, the OLTP database Tfs_DefaultCollection should not be used for reporting purposes. Tfs_Warehouse data is populated from the OLTP database, so if you make changes to your test cases in Microsoft Test Manager (MTM), there may be a delay up to 2 hours in order the changes to be reflected in your reports.

For more information about the Team Foundation Databases and Tfs_Warehouse schema, please refer to this MSDN article.

What is SSRS?

Reporting services (SSRS) is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting services are fully integrated with SQL Server tools and components.

Test Cases Statistics Report Examples

Various reports can be prepared, according to the needs, we choose this approach because of the flexibility of the reports. We needed test cases statistics for their automation status and distribution by priority.

Status “Not automated” means that the test case has no associated automation in Microsoft Test Manager (MTM). “Planned” status is for test cases that should be automated but are not automated. “Automated” are those with associated automation test case in MTM. You could also have this information in the form of a table.

Instead of priority 1,2,3,4 we wanted to have “Very High”, “High”, “Medium” and “Low” respectively, so that is the reason behind the CASE statement.

CASE cwiv.microsoft_vsts_common_priority
WHEN 1 THEN '1 - Very High'
WHEN 2 THEN '2 - High'
WHEN 3 THEN '3 - Medium'
WHEN 4 THEN '4 - Low'
ELSE 'Error'END

Deleted test cases in Microsoft Test Manager are still present in the Tfs_Warehouse, but they do not have a test suite and filtering should be made in such a way to exclude them:

trv.testsuitesuitename IS NOT NULL

Below, you can find a couple of test cases statistics reports, created using Tfs_Warehouse and SSRS:

You have to setup a data source and data set in SSRS with data from that data source in order to prepare your reports.

How to Setup a Data Source in SSRS?

1. Open existing or create a new report in Report Builder, you can download it from here.
2. Right Click and choose “Add Data Source”.

   

3. Type data source name. For example, Tfs2010ReportDS (could be added as shared data source also).
4. Choose “Use a connection embedded in my report”.
5. Select connection type: “Microsoft SQL Server”.
6. Build data source connection string: Data Source=YOURSQLSERVERNAME;Initial Catalog=Tfs_Warehouse;Integrated Security=SSPI;
7. Choose data source credentials: Username: myuser Password: mypassword. The checkbox “Use as Windows credentials” should be checked.

   

8. Test the connection.

   

How to Setup a Dataset?

1. Select Tfs2010ReportDS data source.
2. Use the sample query below.

The data for the dataset used in the reports was obtained via querying TestResultView and CurrentworkItemView. We choose to use the views, because it is the recommended approach, as the table structure could be a subject to frequent changes.

SELECT DISTINCT trv.testcaseid, 
                Max(DISTINCT trv.testsuitesuitename) AS [TestSuiteName], 
                cwiv.system_title, 
                cwiv.microsoft_vsts_tcm_automationstatus, 
                cwiv.system_state, 
                trv.testplanname, 
                cwiv.microsoft_vsts_common_priority, 
                Priority= CASE cwiv.microsoft_vsts_common_priority 
                            WHEN 1 THEN '1 - Very High' 
                            WHEN 2 THEN '2 - High' 
                            WHEN 3 THEN '3 - Medium' 
                            WHEN 4 THEN '4 - Low' 
                            ELSE 'Error' 
                          END 
FROM   [TFS_Warehouse].dbo.currentworkitemview cwiv 
       JOIN testresultview trv 
         ON trv.testcaseid = cwiv.system_id 
WHERE  cwiv.teamprojectcollectionsk = 99 
       AND cwiv.system_workitemtype = 'Test Case' 
       AND trv.testplanname = 'MyTestPlan' 
       AND trv.testsuitesuitename IS NOT NULL 
GROUP  BY cwiv.system_title, 
          cwiv.microsoft_vsts_tcm_automationstatus, 
          cwiv.system_state, 
          trv.testcaseid, 
          trv.testplanname, 
          cwiv.microsoft_vsts_common_priority, 
          trv.resultoutcome, 
          trv.testsuitesuitename

In order to execute the query, you need to have valid credentials for accessing Tfs_Warehouse.

So Far in the TFS API Series

1. Connect to TFS Team Project C# Code
2. Manage TFS Test Plans C# Code
3. Manage TFS Test Cases C# Code
4. Manage TFS Test Suites C# Code
5. TFS Associate Automated Test with Test Case C# Code
6. Test Cases Statistics with SSRS and TFS Data Warehouse
7. SSRS SQL Server Reporting Services- Subscriptions for Reports

If you enjoy my publications, feel free to SUBSCRIBE
Also, hit these share buttons. Thank you!

References

• https://msdn.microsoft.com/en-us/library/ms244696%28v=vs.90%29.aspx
• https://msdn.microsoft.com/en-us/library/ff730837%28v=vs.100%29.aspx
• https://msdn.microsoft.com/en-us/library/ms159106.aspx

CodeProject

The post- Test Cases Statistics with SSRS and TFS Data Warehouse appeared first on Automate The Planet.