Automated traceability matrix sync to Power BI dashboards cuts manual reporting by 75%

ALM Azure DevOps
, , , , , , , ,
1

Wanted to share our implementation of automated traceability reporting that dramatically reduced our compliance reporting overhead.

Background: Our medical device software requires FDA compliance with full requirements-to-test traceability. Previously, we spent 20+ hours per release cycle manually extracting data from Azure DevOps, building Excel matrices, and validating coverage. With monthly releases, this was consuming nearly 25% of our QA manager’s time.

Solution: We built an automated pipeline using OData Analytics views, Power Automate flows, and Power BI DirectQuery with row-level security to generate real-time traceability dashboards. The system automatically tracks requirement-to-test-case linkages, test execution results, and coverage gaps, with automated alerts when coverage drops below thresholds.

Impact: Manual reporting time dropped from 20 hours to 5 hours per cycle (75% reduction). Compliance auditors now have real-time access to traceability data instead of waiting for monthly reports. We caught coverage gaps 3 weeks earlier on average, preventing last-minute scrambles before release validation.

2

Row-level security with OData can be tricky. You need to create roles in Power BI that filter based on Azure DevOps area paths or teams. The challenge is mapping Azure AD users to Azure DevOps team memberships dynamically. I’ve found it easier to create a separate dimension table in Power BI that maps users to area paths, then apply RLS filters on that dimension rather than trying to filter the OData feed directly.

3

Sure! We created custom Analytics views for three main entities: WorkItems (filtered to Requirement and Test Case types), WorkItemLinks (for the traceability relationships), and TestRuns (for execution results). The key was using the WorkItemLinks entity to join requirements to test cases through the ‘Tests’ link type. We also added custom fields for regulatory tags and risk levels to the Analytics view so we could filter by compliance category in Power BI.

4

This is exactly what we need! Can you share more details about the OData Analytics views setup? Specifically, which entities did you query to get the requirement-to-test linkages?

5

What about the Power Automate flows? Are you using those for the coverage alerts you mentioned? We’re trying to implement something similar but unsure how to trigger alerts based on Power BI data.

6

Let me provide the complete implementation details covering all the technical components.

1. OData Analytics Views Configuration

First, create custom Analytics views in Azure DevOps (Analytics > Views > New View):

Requirements-Test Traceability View:

  • Entities: WorkItems, WorkItemLinks
  • Work Item Types: Requirement, Test Case
  • Link Types: Tests, Tested By
  • Fields: ID, Title, State, Area Path, Tags, Custom.RegulatoryCategory, Custom.RiskLevel
  • Filter: State != Removed
  • History: Last 90 days (for trend analysis)

The OData endpoint URL format:


https://analytics.dev.azure.com/{org}/{project}/_odata/v4.0-preview/WorkItemLinks?
$filter=LinkTypeName eq 'Tests'
&$expand=SourceWorkItem,TargetWorkItem

Test Execution Results View:

  • Entities: TestRuns, TestResults
  • Fields: TestRunId, TestCaseId, Outcome, CompletedDate, Configuration
  • Filter: CompletedDate >= @StartOfMonth
  • Aggregations: Count of Passed/Failed/Blocked tests

2. Power Automate Flows

We implemented three flows:

Daily Coverage Monitoring Flow:

  • Trigger: Recurrence (daily at 6 AM)
  • Action 1: HTTP request to Analytics API to get requirement counts by area path
  • Action 2: HTTP request to get linked test case counts
  • Action 3: Calculate coverage percentage (LinkedTests / TotalRequirements * 100)
  • Action 4: Condition - if coverage < 85%
  • Action 5: Create Azure DevOps work item (Bug type with custom tag ‘TraceabilityGap’)
  • Action 6: Send email notification with coverage details

The flow uses a service principal with Analytics Reader permissions for API authentication.

Real-time Link Validation Flow:

  • Trigger: When a work item is created or modified (webhook)
  • Action 1: Check if work item is type ‘Requirement’
  • Action 2: Query Analytics for linked test cases
  • Action 3: If no links exist and requirement is in ‘Active’ state, add warning comment to work item

3. Power BI DirectQuery Setup

Connect Power BI Desktop to Azure DevOps Analytics:

  • Data Source: OData feed
  • URL: Your custom Analytics view OData endpoint
  • Authentication: Organizational account (Azure AD)
  • Connection Mode: DirectQuery (for real-time data)

Data Model:

  • Fact Table: WorkItemLinks (traceability relationships)
  • Dimension Tables: Requirements, TestCases, TestRuns, AreaPaths
  • Relationships: WorkItemLinks.SourceWorkItemId → Requirements.WorkItemId
  • Calculated Measures:
    • Coverage % = DIVIDE(COUNT(LinkedTestCases), COUNT(Requirements), 0)
    • Untested Requirements = CALCULATE(COUNT(Requirements), FILTER(Requirements, NOT(Requirements[WorkItemId] IN VALUES(WorkItemLinks[SourceWorkItemId]))))

4. Row Level Security Implementation

Create RLS roles in Power BI Desktop:

  • Role: ComponentTeamA
  • Filter: AreaPaths[AreaPath] = “Project\ComponentA”
  • Applied to: Requirements, TestCases, WorkItemLinks tables

Map Azure AD users to roles using Power BI Service:

  • Workspace Settings > Security
  • Add user emails or security groups to appropriate roles
  • Users only see data matching their area path filters

For dynamic RLS based on Azure DevOps team membership:

  • Create a separate Azure Function that queries Azure DevOps API for team members
  • Store mapping in Azure SQL Database
  • Import mapping table into Power BI as a dimension
  • RLS filter: UserMapping[UserEmail] = USERPRINCIPALNAME()

5. Coverage Alerts Configuration

Power Automate alert flow logic:


// Pseudocode for coverage calculation:
1. Query Analytics API: GET WorkItems filtered by Type='Requirement' AND State='Active'
2. Store count as totalRequirements
3. Query Analytics API: GET WorkItemLinks filtered by LinkType='Tests'
4. Count distinct SourceWorkItemIds as linkedRequirements
5. Calculate: coveragePercent = (linkedRequirements / totalRequirements) * 100
6. If coveragePercent < 85:
   - Create work item with title "Traceability Gap: {componentName} coverage at {coveragePercent}%"
   - Set priority based on gap severity (>20% gap = Priority 1)
   - Assign to area path owner (query Teams API)
   - Add tags: TraceabilityGap, ComplianceRisk

Alert emails include:

  • Current coverage percentage
  • List of untested requirements (IDs and titles)
  • Link to Power BI dashboard filtered to the affected component
  • Trend chart showing coverage over last 30 days

Implementation Timeline & Lessons Learned:

  • Week 1-2: Analytics views setup and OData query optimization
  • Week 3: Power BI report development and testing
  • Week 4: Power Automate flows and alert logic
  • Week 5: RLS configuration and user testing
  • Week 6: Training and rollout

Key lessons:

  • Start with read-only Analytics access to avoid query performance issues
  • Use incremental refresh in Power BI for historical test run data (reduces load times)
  • Document the OData queries thoroughly - the syntax is not intuitive
  • Test RLS extensively before production rollout - security bugs are critical in compliance scenarios
  • Create a “Traceability Admin” role with full access for troubleshooting

The system now runs automatically with minimal maintenance. We review the dashboards weekly and adjust alert thresholds quarterly based on team feedback. The 75% time savings freed up our QA manager to focus on test strategy and process improvements rather than manual data compilation.

7

How did you handle the row-level security? In our organization, different teams should only see traceability for their components, but we’re struggling to implement that with DirectQuery to Azure DevOps Analytics.

8

Yes, Power Automate handles the alerting. We have a scheduled flow that runs daily and queries the Analytics API directly (not through Power BI) to calculate coverage percentages. When coverage for any component drops below 85%, it creates a work item in Azure Boards and sends email notifications to the component owner and QA lead. This gives us proactive monitoring rather than discovering gaps during the manual review process.