如何在Time Series Insights预览版中按层级聚合多电表能耗数据？

Great question! Let's break this down clearly:

First, the short answer: The current preview version of TSI's Aggregate Series API does NOT support direct aggregation across time series by hierarchy nodes (like Building or Area). As you noticed, this API requires specifying a single timeSeriesId or timeSeriesName, so it's designed for per-meter calculations rather than cross-meter grouping by hierarchy levels.

But don't worry—there are several feasible workarounds, both within TSI and using external Azure tools:

Workarounds within TSI #

1. Use Query Events API for filtered, grouped aggregation #

This is the most efficient built-in solution. Instead of using Aggregate Series, leverage the Query Events API to filter all meters under a specific Building/Area, then aggregate and group by day in a single query.

Here's a simplified example of what the request body might look like:

{
  "timeRange": {
    "from": "2024-05-01T00:00:00Z",
    "to": "2024-05-08T00:00:00Z"
  },
  "filter": {
    "tsx": "properties['Area'] = 'Downtown' AND properties['Building'] = 'OfficeTower1'"
  },
  "aggregates": [
    {
      "tsx": "sum($event['ElectricityUsage'])",
      "alias": "DailyTotalEnergy"
    }
  ],
  "groupBy": [
    {
      "by": "tsx: floor($event['timestamp'], duration('1d'))",
      "alias": "Date"
    }
  ]
}

This query will return the total daily energy usage for all meters in the specified Area-Building combination in one go. Just adjust the filter clause to target different hierarchy levels as needed.

2. Fetch hierarchy members first, then aggregate individually #

If you need to stick with the Aggregate Series API, you can:

• Use the Get Series Hierarchies API to retrieve all timeSeriesId (MeterId) values under a specific Building or Area node.
• Batch calls to the Aggregate Series API for each retrieved MeterId to get per-meter daily totals.
• Sum the results client-side to get the total for the Building/Area.

Note: Be mindful of TSI's API rate limits when batching calls—you might need to add delays or use parallel requests within allowed limits.

External Workarounds #

1. Export data to Azure Data Explorer (ADX) or Synapse Analytics #

Set up a continuous export from TSI to ADX or Synapse. Once the data is in these tools, you can use their powerful query languages (Kusto for ADX, SQL for Synapse) to easily group and aggregate by Area/Building:

For example, a Kusto query in ADX might look like:

TSIData
| where Area == "Downtown" and Building == "OfficeTower1"
| summarize DailyTotalEnergy = sum(ElectricityUsage) by bin(Timestamp, 1d)
| sort by Timestamp asc

This approach is ideal if you need to run frequent, complex aggregations or combine TSI data with other datasets.

2. Use Azure Functions for custom aggregation logic #

Create an Azure Function that runs on a schedule (e.g., daily) to:

1. Fetch all meters under target Area/Building nodes via TSI APIs.
2. Retrieve their daily energy usage data.
3. Calculate the total for each Building/Area.
4. Store the aggregated results in a database (like Azure SQL DB) or send them to a BI tool (like Power BI) for visualization.

This gives you full control over the aggregation process and integrates well with other Azure services.

内容的提问来源于stack exchange，提问作者Peter Hut