Grouping

Overview

Grouping aggregates a large result set into meaningful, manageable subsets. When you mark one or more fields in the fields array as grouping fields, the query collapses rows that share the same values in those fields into a single result row, and every non-grouped measurement in the query is aggregated within each group. Grouping is therefore what turns a detailed, row-per-record query into a summarized one: the grouped fields become the axes of the result, and the measurements become the aggregated values at each intersection.

Use grouping when you want to analyze data at a level of detail coarser than the raw records — for example, total revenue per account, or record counts per city. You control grouping per field: each entry in fields can declare whether it is a grouping field and, if so, whether it groups along the row axis or the column axis. Fields that are not marked as grouping fields and carry an aggregation method are aggregated within each group.

The single most important thing to know: adding a grouping field changes the shape of the result for the whole query. Once any field groups, the query returns one row per distinct combination of grouped values rather than one row per record, and all other selected measurements are summarized accordingly.

Metadata in the model

Grouping is set per query — it is not defined in the Semantic Data Model. Any dimension, measurement, or calculated field you select can be turned into a grouping axis at query time by marking it in the request. The fields you group by are defined in the model as Semantic Data Object Fields (and calculated fields), but whether and how they group is a query-time choice.

For how the underlying fields are defined, see Semantic Data Object Field in the Authoring API.

Query usage

Row and column grouping

Each QueryField can declare a grouping axis with the grouping field, which takes ROW_GROUPING or COLUMN_GROUPING. Row grouping lays out grouped values down the rows of the result; column grouping pivots grouped values across columns. Use ROW_GROUPING for a standard grouped list and COLUMN_GROUPING to build a matrix (cross-tab) layout.

1{
2  "structuredSemanticQuery": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Name",
8            "table_name": "SemanticAccount_SDO"
9          }
10        },
11        "grouping": "COLUMN_GROUPING"
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "AnnualRevenueAmount",
17            "table_name": "SemanticAccount_SDO"
18          }
19        },
20        "grouping": "ROW_GROUPING"
21      }
22    ]
23  },
24  "semanticModelId": "2SMxx0000004CAeGAM"
25}

Performance: Accurate relationship cardinality (OneToOne or ManyToOne) lets the engine skip a deduplication pass for grouped measures.

Group by dimensions

Grouping by dimensions is the common case. Mark each dimension you want as a grouping axis with row_grouping: true (the boolean shorthand for grouping: "ROW_GROUPING"), and give any measurement you select a semantic_aggregation_method. The query returns one row per distinct combination of the grouped dimension values, with the measurement aggregated within each combination.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "Mailing City",
17            "table_name": "ContactSemanticLayer__dll"
18          }
19        },
20        "row_grouping": true
21      },
22      {
23        "expression": {
24          "table_field": {
25            "name": "Annual Revenue",
26            "table_name": "AccountSemanticLayer__dll"
27          }
28        },
29        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
30      }
31      // ...
32    ],
33    "options": {
34      "row_counts": true
35    }
36  },
37  "semanticModelApiName": "test_model"
38}

Group by measurements

A measurement can itself be a grouping axis. Instead of aggregating the measurement within each group, mark it with row_grouping: true and its distinct raw values become the groups. This is useful when the measurement’s values are the categories you want to analyze by. When a measurement is a grouping field it is not aggregated; it is treated like any other grouped field.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Cost",
8            "table_name": "SemanticAccount__dlm"
9          }
10        },
11        "row_grouping": true
12      }
13    ]
14  },
15  "semantic_model": {
16    // ... SemanticAccount__dlm defines Cost as a semanticMeasurement
17  }
18}

Group by dates

Group by a date or datetime field the same way you group by any other dimension: mark it with row_grouping: true. Each distinct date (or timestamp) value becomes a group.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Approved_Date1",
8            "table_name": "Application8"
9          }
10        },
11        "alias": "Application8.Approved_Date1",
12        "row_grouping": true
13      }
14    ],
15    "options": {
16      "limit_options": {
17        "limit": 100
18      }
19    }
20  },
21  "semantic_model": {
22    // ... Application8 defines Approved_Date1 as a DateTime dimension
23  }
24}

Performance: A limit caps the number of groups returned and is applied after grouping — it does not reduce the rows scanned. Use filters to reduce the scan.

Group by fiscal dates

To group by a fiscal grain, group on a calculated field whose expression derives the fiscal part from a date field with the FISCAL_YEAR and FISCAL_QUARTER functions. Reference the date column in the expression, and mark the field with row_grouping: true so each fiscal period becomes a group.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "calculated_field": {
7            "name": "fiscal_quarter_on_the_fly_calculation",
8            "expression": "FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)"
9          }
10        },
11        "row_grouping": true
12      }
13    ]
14  },
15  "semanticModelApiName": "test_model"
16}

Reference

Field (wire name)	Type	Required	Description
`grouping`	String (enum)	N	Grouping axis for a `QueryField`: `ROW_GROUPING` or `COLUMN_GROUPING`.
`row_grouping`	Boolean	N	Shorthand that marks a `QueryField` as a row grouping field; equivalent to `grouping: "ROW_GROUPING"`.
`semantic_aggregation_method`	String (enum)	N	Aggregation applied to a non-grouped measurement within each group (for example `SEMANTIC_AGGREGATION_METHOD_SUM`).

For the full request schema, see Request Reference.

Limitations

Grouping changes the shape of the entire query. As soon as any field is a grouping field, the query returns one row per distinct combination of grouped values, and every non-grouped measurement must resolve to an aggregate.
Fiscal grouping uses the standard calendar only. Fiscal grains are derived through the fiscal date functions over the standard calendar; a custom fiscal calendar is not applied.

Aggregation & Totals — How measurements are aggregated within groups, and how subtotals and grand totals summarize grouped results.
Sorting & Limiting — Sort grouped results, including sorting a grouped dimension by an aggregated measure.
Filtering — How filters and aggregate filters (HAVING) interact with grouped queries.
Calculated Fields — Group by a calculated field, such as a fiscal-period expression.
Semantic Data Object Field — Authoring API for the fields you group by.