Sorting, Aggregation, and Limiting

This document describes how to sort, aggregate, and limit results in Semantic Metric Queries. These operations allow you to refine and customize your metric query results to meet specific business requirements.

Sorting

You can sort the results using any of your selected dimensions. Sorting helps organize your metric query results in a meaningful order.

The following example shows how to apply simple sorting in a metric query:

1{
2  "structuredMetricQuery": {
3    "sort_orders": [
4      {
5        "simple_sort_order": {
6          "sort_by_field": {
7            "expression": {
8              "table_field": {
9                "name": "Name",
10                "table_name": "SemanticAccount_SDO"
11              }
12            }
13          },
14          "sorting_order": "ASC",
15          "null_values_position": "NULLS_LAST"
16        }
17      }
18    ],
19    "submetric_definition": {
20      "metric_api_name": "AnnualRevenueAmountPerName_md"
21    },
22    "model_api_name": "AccountSDM",
23    "semantic_context": { // context
24    }
25  },
26  "semanticModelId": "2SMxx0000004CAeGAM"
27}

Aggregation

You can aggregate the results of metric queries using time grains. Time grains allow you to group your metric data by specific time periods.

Each metric query defines a list of supported time grains in the model, and the metric query selects one of them as shown in the following example:

1{
2  "structuredMetricQuery": {
3    "time_grain": "Week",
4    "submetric_definition": {
5      "metric_api_name": "AnnualRevenueAmountPerName_md"
6    },
7    "model_api_name": "AccountSDM",
8    "semantic_context": { // context
9    }
10  },
11  "semanticModelId": "2SMxx0000004CAeGAM"
12}

Limiting

You can use limiting to constrain the number of result rows returned in the metric query results. This is useful for performance optimization and pagination.

The limit value is set as part of the SQL “LIMIT” clause. You can customize the default limit value. The Semantic Query supports limits in Run Mode - limits are applied after metric query result grouping. For example:

1{
2  "structuredMetricQuery": {
3    "limit_options": {
4      "limit": 10
5    },
6    "submetric_definition": {
7      "metric_api_name": "AnnualRevenueAmountPerName_md"
8    },
9    "model_api_name": "AccountSDM",
10    "semantic_context": { // context
11    }
12  },
13  "semanticModelId": "2SMxx0000004CAeGAM"
14}

Performance: A limit caps the number of result rows after grouping — it constrains the groups returned, not the rows scanned. Use filters to reduce the scan.

Performance: Result size is capped at 5,000 rows for interactive queries and 50,000 rows for non-interactive queries; a query that would exceed the cap fails. Narrow the result with filters or a limit.