Skip to content

How To Guide: Traceability Report Using the Data Warehouse

Understanding how test cases are linked to EA requirements

In order for test to be associated with EA requirements each requirement needs to have a hyperlink based link which follows the format: https://ea.requirements.com/id/GUID

*note: the Gherkin import utility produces links from feature files that comply with this format

Importing the work items data

There is a service hook set up which triggers whenever a work item is created/updated and posts a message into a queue storage. A function app is deployed which gets triggered on this queue and parses the data and updates it to cosmos db

The data captured by the function app is stored in the azdo-metrics-cosmos Cosmos DB account The name of the database is azdo-metrics-db-prod and the container is called workitems-type

Importing the ea requirements data

A utility has been created which gets triggered when the ea requirements file is checked into carelink repo. This utility will parse the file and insert data into the cosmos db. Details on how to use this can be found here

The data captured by the utility is stored in the azdo-metrics-cosmos Cosmos DB account The name of the database is azdo-metrics-db-prod and the container is called specifications-feed

Importing the data into Power BI

Here is a snippet adapted from Power BI Guide in this folder on how to import data from the data warehouse

  1. Click "get data" in the home tab from the data section of the top ribbon
  2. Select Azure Cosmos DB and click "connect"
  3. Enter the database URL and access key
    • Find your Azure Cosmos DB account in the Azure portal here
    • Go to "keys" and copy the DB URI click "ok". Enter the DB URI into the power bi wondow
    • If asked to provide the access key go to above url and go to Read-only keys and copy the primary key. Enter the key into the power bi window
    • List of dbs will be displayed. Select the db azdo-metrics-db-prod
    • Choose the db and container/s you want. (workitems-type and specification-feed)
    • Click on Transform

Creating the Report

  1. Get data from workitems-type container to fetch the test cases details.
    • Select workitems-type from Queries pane at left
    • Click on expand button at the top right corner of the table
    • Choose the columns of interest (include "relations" which has the EA Url)
    • Expand relations column two times - first into rows and then into columns
    • Filter out the data in Document.Relations.url which doen't start with EA url (Please modify this step as per the report requirement. steps mentioned here are for the purpose of this guide)
    • Click the button at the top right corner of the column
    • Select text filters and select Begins with
    • Provide string "https://ea" and click ok
    • Create a new custom column with the GUID stripped out from the EA url
    • Click on Add column option in tool bar
    • Select Extract option from From Text section in tool bar after selecting the column Document.Relations.Url
    • Select option "Text after Delimiter" and provide "https://ea.mdtproductdevelopment.com/id/" without quotes and click on OK
    • Rename the columns as needed
  2. Similarly get data on EA reuirements from specifications-feed container
    • Select specifications-feed from Queries pane at left
    • Click on expand button at the top right corner of the table
    • Choose the columns of interest (include "guid" which has the requirement id)
    • Rename the columns as needed
  3. Create a merged query to create a relationship between specifications-feed and workitems-type
    • Click on Home button in tool bar
    • Click on "Merge Queries" in the Combine section of the tool bar
    • Select the guid column in specifications-feed table
    • Select workitems-type table from the dropdown
    • Select the custom column created for EA guid in the workitems-type table
    • Select join kind as needed
    • Left Outer (for all data ea requirements with and without related test cases)
    • Click on OK. Rename columns/tables as needed
    • Click on Close and Apply in the tool bar. Report view will be displayed
  4. Building the report visuals
    • Create report by pulling required columns from the Field pane at the right side
    • Add slicers as required
      • For example to add a slicer on requirement type
      • Click on slicer from visualizations in the reports view
      • Add Requirement Type column into it
    • The EA requirements and test case counts
      • Use the count (distinct) option to get the count of test cases and reuirement
      • To find count requirements with test cases
      • Click on New Measure in the Calculations section of tools bar
      • Enter following in the opened window
        • Requirements with test cases = DISTINCTCOUNT('All EA'[EAGuid])
        • where 'ALL EA' is the name of the table and EAGuid is the name of the column with EA guid values in the workitems-type data
        • Click on tick mark. A new column will be listed in the table. Include that table in the report and at the end of the column you can find the total number of EA requirements with test cases -To find count requirements without test cases
      • Click on New Measure in the Calculations section of tools bar
        • Enter following in the opened window - Total Requirements = DISTINCTCOUNT('All EA'[Requirement ID]) - where 'ALL EA' is the name of the table and Requirement ID is the name of the column with EA guid values in the specifications-feed data
        • Click on tick mark. A new column will be listed in the table. Include that table in the report and at the end of the column you can find the total number of EA requirements
        • You can calculate requirements without test cases = [Total Requirements] - [Requirements with test cases]
  5. To create a new page with Requirements without test cases
    • Go to transform data page by clicking on transform data
    • Duplicate the merged query you created using steps till 3 in this section. Right click on query in the left pane and click on duplicate
    • Rename as needed
    • Go to the step merged queries in the right pane
    • Click on settings. Change the join kind to Left Anti. Click OK
    • Click on close and apply
    • Click on plus sign (to create a new page) at the bottom of the report page
    • From the data you created from left anti join select the fields you want display (from Fields pane at the right end)

Use an existing Power BI Desktop(.pbix) Report

  1. Download the Traceability-Report v2.zip file from the devops-import-workitems repository
  2. Unzip the file to extract Traceability-Report.pbix Power BI report
  3. Open Power BI Desktop application. Login using the Medtronic credentails
  4. Navigate to File -> Open Report -> Browse Reports. Choose the downloaded Power BI report(.pbix file)
  5. Choose 'Refresh' option from the Home Menu item. The latest data will be drawn from the Cosmos database on refresh.
  6. If a pop-up appears, re-enter the database details.
  7. Please enter Database URL as - https://azdo-metrics-cosmos.documents.azure.com:443/
  8. Find Connection Key on the Azure Portal here (Find your Azure Cosmos DB account in the Azure portal here
  9. Wait untill data is completely loaded and the reports are updated.
  10. This is a sample traceability report. You can add more reports or modify the existing ones are required.

The Example Traceability Report

An example traceability report has been published in the CRHF DevOps workspace. It is called example-traceability-report and has some test cases mocked using data from CareLink https://app.powerbi.com/groups/64b65c03-1aba-4e57-a4f5-5c60f53cfdf5/reports/ecd0d3cb-0144-41c0-8f88-408ba176b4b0/ReportSection