Custom defect trend reports vs Lookback API for audit-report

ALM Rally (Broadcom Agile Central)
, , , , , , , ,
1

Our audit team needs historical defect trend analysis spanning 18 months for compliance reporting. We’re hitting query timeout issues with custom reports that aggregate defect state changes over time.

I’ve been researching Rally’s Lookback API as an alternative since it’s designed for historical data queries. The documentation mentions Lookback limits and TimeboxFact objects for data aggregation, but I’m unclear on performance characteristics compared to standard WSAPI queries.

For those doing audit-level reporting with large datasets, what’s been more effective - custom reports with optimized queries or Lookback API with client-side aggregation? Particularly concerned about query timeout thresholds and data aggregation strategies.

2

We migrated all compliance reporting to Lookback API last year. Query performance improved dramatically - what took 5+ minutes with custom reports now completes in 30-45 seconds. The key is using TimeboxFact for sprint-level aggregation rather than querying individual defect snapshots.

3

I’ve implemented both approaches for audit reporting across multiple Rally workspaces, so I can provide detailed comparison:

Lookback API Advantages for Historical Analysis

The Lookback API is specifically architected for time-series queries and audit trails. Key benefits:

  1. Performance: Lookback stores daily snapshots of all Rally objects, enabling fast historical queries without reconstructing state changes from revision history

  2. Lookback Limits: The 120-day query window per request is a hard limit, but manageable:

// Query structure for 18-month analysis
for (let i = 0; i < 6; i++) {
  const startDate = addMonths(baseDate, i * 3);
  const endDate = addMonths(startDate, 3);
  queryLookback({ _ValidFrom: { $gte: startDate, $lt: endDate } });
}

Chunk your 18 months into 6 quarters, aggregate client-side. Each query completes in 20-40 seconds vs 5+ minute timeouts with standard reports.

  1. TimeboxFact for Aggregation: TimeboxFact objects pre-aggregate metrics at iteration/release level:
{
  find: { _TypeHierarchy: 'TimeboxFact', TimeboxType: 'Iteration' },
  fields: ['DefectCount', 'DefectsClosed', 'DefectsOpened']
}

This returns sprint-level defect metrics instantly without querying individual defects. Perfect for compliance dashboards showing defect trends by sprint.

  1. Data Aggregation Strategies:
  • Use $bucket operator for client-side grouping by month/quarter
  • Hydrate state fields to get readable values: `hydrate: [‘State’, ‘Priority’]
  • Filter by _ValidFrom and _ValidTo to identify exact state transition dates

Custom Reports Limitations

Standard WSAPI queries and custom reports struggle with historical analysis because:

  • They query current object state, requiring complex revision history queries for trends
  • No built-in time-series optimization leads to query timeouts above 10k records
  • Rally’s report builder has 60-second execution timeout - can’t be extended

Practical Implementation for Audit Reporting

For your 18-month compliance requirement:

  1. Initial Data Pull: Use Lookback API to extract historical snapshots
{
  find: {
    _TypeHierarchy: 'Defect',
    _ProjectHierarchy: yourProjectOID,
    _ValidFrom: { $gte: '2024-04-01T00:00:00.000Z' }
  },
  fields: ['ObjectID', 'State', 'Priority', 'CreationDate', '_ValidFrom', '_ValidTo'],
  hydrate: ['State', 'Priority']
}
  1. Handle Rate Limits: Standard Rally accounts allow 50 Lookback requests/hour. Implement exponential backoff:
  • Retry after 429 responses with 60-second delay
  • Queue requests to stay under limit
  • Consider upgrading to Rally Premium for higher rate limits if needed
  1. Client-Side Aggregation: Store Lookback results in local database (PostgreSQL, MongoDB) for complex aggregations:
  • Calculate defect aging by subtracting CreationDate from state change timestamps
  • Group by month/quarter for trend analysis
  • Join with TimeboxFact data for sprint-level context
  1. Query Timeout Mitigation: If you must use custom reports:
  • Add strict date range filters (max 90 days per report)
  • Limit to specific projects rather than workspace-level queries
  • Use report scheduling for overnight execution
  • Export to CSV and aggregate externally

Recommendation

Lookback API is the clear winner for your audit requirements. The 120-day window limit is manageable with proper chunking, and query performance is 5-10x faster than custom reports. TimeboxFact provides efficient sprint-level aggregation for executive dashboards.

Implement a nightly ETL job that pulls Lookback data into a reporting database. This avoids hitting API limits during business hours and enables complex SQL-based aggregations that would timeout in Rally’s report builder. Your audit team gets fast, reliable historical analysis without Rally query timeout issues.

4

TimeboxFact is powerful for sprint-level metrics, but it’s limited to iteration boundaries. If your audit reports need arbitrary date ranges or daily granularity, you’ll still need to query individual snapshots and aggregate manually.

We built a nightly ETL job that pulls Lookback data into a data warehouse for complex aggregations. This avoids hitting API query limits during business hours when audit teams need reports.

5

Lookback API is definitely the right tool for historical trend analysis. Standard WSAPI queries timeout because they’re not optimized for time-series data. Lookback API stores snapshots at daily intervals, making historical queries much faster.

One caveat - Lookback has a 120-day query window limit per request. For 18 months of data, you’ll need to chunk your queries into multiple date ranges and aggregate client-side.

6

Here’s a sample Lookback query for defect state changes over time:

find: { _TypeHierarchy: 'Defect', _ProjectHierarchy: 12345 }
fields: ['_ValidFrom', '_ValidTo', 'State', 'Priority']
hydrate: ['State']

The _ValidFrom and _ValidTo timestamps let you reconstruct state transitions accurately. Much more efficient than querying current state repeatedly.