Building executive dashboards that pull data from AEC 2021 Reporting API. We need some metrics that aren’t available as standard (like “pipeline velocity by region” and “weighted opportunity value by product line”). I can calculate these as custom metrics in the API queries, but I’m concerned about performance.
Standard metrics like “total opportunities” and “win rate” return in under 2 seconds for large datasets. My custom metric queries that do aggregations and calculations are taking 15-20 seconds for the same date ranges. This makes the dashboards feel sluggish.
Is this the expected trade-off? Do custom metrics inherently perform worse because they’re calculated on-the-fly? Or am I doing something wrong in how I’m structuring the API queries? Would love to hear how others balance the need for custom business metrics against query performance requirements.
For frequently-used custom metrics, create them as calculated fields in AEC’s data model rather than computing them in API queries. Navigate to Analytics > Calculated Fields and define your metrics there. AEC will pre-compute them during data processing cycles and they’ll perform like standard metrics. The limitation is they’re static definitions - you can’t parameterize them per API call. But for core business metrics that don’t change (like weighted pipeline value), this is the right approach. Save dynamic custom calculations for ad-hoc analysis only.
Consider a hybrid caching strategy. For executive dashboards that don’t need real-time data, cache custom metric results in your application layer (Redis, database, etc.) and refresh on a schedule. We cache our complex custom metrics every 4 hours and serve dashboard requests from cache. This gives us the flexibility of custom calculations without the performance hit on every page load. Only trigger live API queries when users explicitly request fresh data. This pattern is especially effective for metrics that involve historical trend calculations where the underlying data rarely changes.
Yes, custom metrics perform worse by design. Standard metrics are pre-aggregated in AEC’s data warehouse - they’re calculated during nightly batch jobs and stored as materialized values. When you query them, you’re reading pre-computed results. Custom metrics require on-the-fly calculation across raw data, which means full table scans and runtime aggregations. For large datasets, this is always going to be slower. The 15-20 second response times you’re seeing are actually pretty normal for complex custom calculations.
Dashboard performance isn’t just about API query speed - it’s also about how you’re fetching data. Are you making separate API calls for each custom metric? That’s a common mistake. Use the Reporting API’s batch query feature to request multiple metrics in a single call. We consolidated 8 separate API requests into one batch query and reduced total dashboard load time from 45 seconds to 12 seconds. The API can parallelize internal calculations when metrics are requested together, which is way more efficient than sequential individual queries.