Case management reporting delayed after archiving closed cases

BPM Appian
, , , , , , , ,
1

We implemented an automated archival process that moves closed cases to an external storage system (Azure Blob Storage) after 90 days to manage our database size. The archival process itself works fine and cases are successfully transferred with all associated documents and metadata.

However, since enabling this automated archival, our case management status reports have become significantly delayed. Reports that used to generate in 5-10 seconds now take 2-3 minutes, and sometimes timeout completely. The delay seems to occur when the report tries to aggregate data across both active cases in Appian and archived cases in external storage.

We need hybrid reporting that can query both data sources efficiently, but the current implementation is causing performance issues. Our reports need to show historical trends including archived cases for executive dashboards. Has anyone successfully configured hybrid reporting with external storage integration that maintains acceptable performance?

2

The timeout issue is likely because your report is making synchronous calls to Azure Blob Storage for each archived case during the query. External storage retrievals have much higher latency than database queries. You need to implement a caching layer or index the archived case metadata separately so reports can query summary data without fetching full case details from blob storage.

3

I’d recommend maintaining a metadata table in Appian that keeps key reporting fields for archived cases even after the full case data moves to external storage. This way your reports can query the local metadata table quickly, and only fetch full case details from Azure when users drill down into specific cases. The metadata table should include: case ID, status, dates, assigned user, and any fields used in your aggregate reports.

4

Your hybrid reporting performance issue is a common challenge when implementing external archival solutions. The problem stems from the fundamental difference in query performance between local database operations and external API calls to cloud storage. Let me walk you through a comprehensive solution that addresses all three critical components.

AUTOMATED ARCHIVAL PROCESS: Your current archival process needs to be enhanced to support efficient hybrid reporting. Modify your archival workflow to execute a two-phase commit:

Phase 1 - Metadata Extraction: Before moving case data to Azure Blob Storage, extract and persist reporting-critical fields to a local metadata repository. Create a new CDT called ‘ArchivedCaseMetadata’ with these fields:

  • caseId (primary key)
  • caseNumber, caseTitle, caseType
  • createdDate, closedDate, archivedDate
  • assignedUser, department, priority
  • finalStatus, resolution
  • blobStorageUri (reference to Azure location)
  • documentCount, totalSize

Phase 2 - Archive Transfer: After metadata is safely persisted locally, proceed with the full case transfer to Azure. This ensures your reporting layer always has fast access to summary data even if the Azure call fails or is slow.

Update your archival smart service to include a metadata write step before the external transfer. This adds minimal overhead to the archival process while dramatically improving query performance.

EXTERNAL STORAGE INTEGRATION: The integration pattern needs to support both query-time and background sync operations:

  1. Implement a metadata synchronization service that runs nightly to verify consistency between your local metadata store and Azure Blob Storage. This catches any archival failures or data drift.

  2. Create a connected system for Azure Blob Storage with connection pooling and timeout configurations:

    • Set connection timeout to 5 seconds
    • Set read timeout to 15 seconds
    • Configure retry logic with exponential backoff
    • Enable connection pooling with min 2, max 10 connections

  3. Build an integration object that provides two access patterns:

    • Fast path: Query metadata from local data store (sub-second response)
    • Detail path: Fetch full case from Azure only when explicitly requested (lazy loading)

HYBRID REPORTING CONFIGURATION: Restructure your reports to use a three-tier query strategy:

Tier 1 - Active Cases: Query your primary case management data store for all active and recently closed cases (last 90 days). This is your fast path with typical database performance.

Tier 2 - Archived Metadata: Query the ArchivedCaseMetadata data store for summary information about archived cases. This provides aggregate counts, date ranges, and basic metrics without touching Azure.

Tier 3 - Archived Details: Only query Azure Blob Storage when a user drills down into a specific archived case. Implement this as an on-demand action rather than automatic loading.

In your report grid configuration:

  • Display Tier 1 and Tier 2 data by default (merged result set)
  • Add a conditional column showing ‘View Details’ link for archived cases
  • When clicked, make an async call to fetch full case data from Azure
  • Cache retrieved archived cases in the user session for 1 hour to avoid repeated fetches

For executive dashboards showing historical trends:

  • Pre-aggregate archived data monthly and store in a summary table
  • Update this summary table as part of your nightly archival process
  • Dashboard queries hit the summary table instead of raw archived metadata
  • This provides instant trend analysis without any external storage calls

Implementation checklist:

  1. Create ArchivedCaseMetadata CDT and data store
  2. Modify archival process to write metadata before transfer
  3. Update all existing reports to query the metadata store for archived cases
  4. Implement lazy loading for full archived case details
  5. Create monthly summary aggregation job for dashboard reports
  6. Add monitoring to track report performance metrics

This architecture reduces your report query time from 2-3 minutes back to under 10 seconds for most operations, while maintaining complete access to historical data when needed. The key is separating frequently queried metadata from rarely accessed full case details.

5

Also consider implementing pagination and lazy loading in your reports. Don’t try to load all archived cases at once. Load active cases first, then offer a ‘Load Archived Data’ option that fetches archived case summaries in batches. This improves perceived performance significantly.