We just wrapped up AG Studio Launch Week See what we shipped
AG Grid
Best JavaScript Grid in the World
AG Charts
Best JavaScript Charts in the World
AG Studio
Best JavaScript Dashboard in the World
Bryntum Gantt
Speed-tuned Gantt chart for project planning
Bryntum Scheduler
Resource scheduling timeline component
PROPRO
Bryntum Scheduler Pro
Scheduler with auto-scheduling and constraints
Bryntum Calendar
Flexible JavaScript event calendar
Bryntum Task Board
Kanban-style task board component
Search⌘ KCtrl K
What's New
Getting Started
OverviewQuick StartBuilding a DashboardMigrationEnterprise Licence
AI Skills
Connecting Data
Data OverviewData ModellingLoading DataSharing & Caching DataLive Data
Agent Framework
Agent Framework OverviewAgent Quick Start
WebMCP
Fields & Formatting
Data TypesFormattingExpressionsCalendarsEditable Fields
Appearance & Behaviour
Modes & LayoutStandalone LayoutResponsive LayoutThemingTheme BuilderLocalisationAccessibilityStateFigma Design System
Interaction
Context MenuPopupsDrill ThroughUndo & RedoExporting
Widgets
Widgets OverviewConfiguring WidgetsCustom WidgetsForm Configuration
Server-Side Data
Custom EngineImplementing Queries

React Embedded AnalyticsData Modelling

Version 3.1.0

When multiple tables are provided, Relationships describe how the tables are linked, and how those relationships should be shaped for the queries a dashboard runs.

Relationships Copy Link

When multiple tables are provided, if there are relationships between the data, then these should be provided alongside the data sources. This will allow fields from different tables to be displayed together in the same widget.

The example above demonstrates two tables, Medals and Capitals, linked together by country. Both tables are Sync Data.

const data = useMemo(() => { 
	return {
        sources: [{
            id: 'medals',
            data: [
                {
                    year: 2000,
                    sport: 'Swimming',
                    country: 'United States',
                    // ... other fields
                },
                // ... other rows
            ],
        }, {
            id: 'capitals',
            data: [
                {
                    country: 'United States',
                    capital: 'Washington, D.C.',
                    // ... other fields
                },
                // ... other rows
            ],
        }],
        relationships: [
            {
                id: 'medals-capitals',
                source: 'medals.country',
                target: 'capitals.country',
                type: 'many-to-one'
            },
        ],
    };
}, []);

<AgStudio data={data} />

Name each field as a 'tableId.fieldId' string. An object with tableId and fieldId properties, such as { tableId: 'medals', fieldId: 'country' }, also works.

Normal relationships are defined using the AgSingleDataRelationDefinition interface:

idCopy Link
string
ID of the relationship.
typeCopy Link
AgRelationType
The cardinality of the relationship from the source table to the target table. For a composite relationship it describes the combination of all its fields, not any one field.
acceptFanoutCopy Link
boolean
Accept row duplication a query genuinely introduces by joining through this relationship - e.g. a many-to-many relationship onto a pre-aggregated table with no finer-grained key available. When true, a query whose only path to a shared dimension crosses this relationship executes via ordinary join-through instead of being rejected; downstream aggregates must account for the duplication. It also lets a query join through this relationship when its "one" side holds a repeated value, which data.options.fanout.execute: 'prevent' otherwise refuses. A query that groups by nothing totals each table's measures over that table's own rows whatever this setting says, and a calculated measure reading tables on both sides over the rows they match on.
sourceCopy Link
AgRelationFieldRef
Source field.
targetCopy Link
AgRelationFieldRef
Target field.

Joining on More Than One Field Copy Link

Sometimes one field is not enough to identify the row a relationship points to. In a multi-tenant dataset, each tenant may number its orders from 1, so order 1 exists once for tenant 10 and again for tenant 20. Only the combination of tenantId and orderId identifies one order.

Declare a relationship like this with conditions, one entry for each pair of fields, in place of source and target:

{
    id: 'order-lines-orders',
    conditions: [
        { source: 'orderLines.tenantId', target: 'orders.tenantId' },
        { source: 'orderLines.orderId', target: 'orders.orderId' },
    ],
    type: 'many-to-one',
}

The example above sums the order lines of each customer. Acme and Cedar both have an order 1, under tenants 10 and 20, and each total counts only the lines of its own tenant.

A row joins only when every pair matches. A row with a blank value in any of the fields matches nothing.

The relationship's type and acceptFanout apply to the combination of fields, not to any one of them. Above, each order line has one order for its tenantId and orderId together, even though many orders share the same orderId.

Every pair must link the same two tables, and a field can appear only once on each side. A relationship uses either source and target or conditions, never both.

Declaring one relationship for each field is not the same thing. Each of those relationships joins on its own field alone, so an order line would match every order with the same orderId, whichever tenant it belongs to.

Relationships on more than one field are defined using the AgCompositeDataRelationDefinition interface:

idCopy Link
string
ID of the relationship.
typeCopy Link
AgRelationType
The cardinality of the relationship from the source table to the target table. For a composite relationship it describes the combination of all its fields, not any one field.
acceptFanoutCopy Link
boolean
Accept row duplication a query genuinely introduces by joining through this relationship - e.g. a many-to-many relationship onto a pre-aggregated table with no finer-grained key available. When true, a query whose only path to a shared dimension crosses this relationship executes via ordinary join-through instead of being rejected; downstream aggregates must account for the duplication. It also lets a query join through this relationship when its "one" side holds a repeated value, which data.options.fanout.execute: 'prevent' otherwise refuses. A query that groups by nothing totals each table's measures over that table's own rows whatever this setting says, and a calculated measure reading tables on both sides over the rows they match on.
conditionsCopy Link
readonly [AgRelationCondition, ...AgRelationCondition[]]
The pairs of fields to match, in order. Every pair joins the same source table to the same target table.

Each entry in conditions is an AgRelationCondition:

sourceCopy Link
AgRelationFieldRef
Field on the relationship's source table.
operatorCopy Link
"equals"
default: 'equals'
How the two fields compare. Only equality is supported.
targetCopy Link
AgRelationFieldRef
Field on the relationship's target table.

The "one" side of a relationship must hold unique values: the target field of a many-to-one, the source field of a one-to-many, and both fields of a one-to-one. If that field holds a repeated value, Studio reports a warning naming the relationship, and rows joined through it may be repeated.

When data.options.fanout.execute is 'prevent', Studio reports an error instead and does not run a query that joins through that relationship, unless the relationship sets acceptFanout: true. See Fan-out Detection.

For a relationship on more than one field, each combination of values on the "one" side must be unique. A single field may repeat a value, as long as no two rows share every field's value.

The order-lines-orders relationship above meets this rule, because orders has one row for each order. The following relationship differs only in its target table, and breaks it:

// Bad: 'orderRevisions' has one row for each revision of an order,
// so an order revised twice has two rows with the same tenantId and orderId
{
    id: 'order-lines-order-revisions',
    conditions: [
        { source: 'orderLines.tenantId', target: 'orderRevisions.tenantId' },
        { source: 'orderLines.orderId', target: 'orderRevisions.orderId' },
    ],
    type: 'many-to-one',
}

Studio reports a warning for this relationship, and joins each line of a twice-revised order to both revisions. To fix it, relate the order lines to a table with one row for each order. If the order lines record which revision they belong to, you can instead add a third condition on that field.

Common Mistakes Copy Link

The following definitions are also wrong. Each shows only the condition at fault; the other condition is the same as in the order-lines-orders relationship above.

// Bad: 'type' reads from source to target. Here 'orders' is the source, so this says each
// tenantId and orderId pair appears on only one order line, and Studio warns that it does not.
// Use 'one-to-many', or swap the source and target.
{
    id: 'orders-order-lines',
    conditions: [
        { source: 'orders.tenantId', target: 'orderLines.tenantId' },
        // ...the orderId pair, also from 'orders' to 'orderLines'
    ],
    type: 'many-to-one',
}

// Error: 'orders' stores tenantId as text and 'orderLines' stores it as a number,
// so no value on one side can match a value on the other.
// Studio reports an error and leaves the relationship out.
{
    id: 'order-lines-orders',
    conditions: [
        {
            source: 'orderLines.tenantId', // number
            target: 'orders.tenantId', // text
        },
        // ...the orderId pair
    ],
    type: 'many-to-one',
}

// Error: the second condition joins a different table.
// Studio reports an error and leaves the relationship out.
{
    id: 'order-lines-orders',
    conditions: [
        // ...the tenantId pair, from 'orderLines' to 'orders'
        { source: 'orderLines.orderId', target: 'orderHeaders.orderId' },
    ],
    type: 'many-to-one',
}

// Error: 'tenantId' appears in both conditions, copied without changing the source field.
// Studio reports an error and leaves the relationship out.
{
    id: 'order-lines-orders',
    conditions: [
        // ...the tenantId pair
        { source: 'orderLines.tenantId', target: 'orders.orderId' },
    ],
    type: 'many-to-one',
}

When Studio leaves a relationship out, it does not run a query that reads both of its tables, unless another relationship joins them. It reports an error that names the relationship.

Joining Dates Copy Link

By default date fields are joined the same way as any other field, where matching rows are linked by exact date value. A date field can also be joined to a datetime field, and Studio then compares them by day.

It is also possible to join date fields of different granularities, or to override the granularity at which they are joined.

This is done by using a ::unit field ID in the relationship, Studio derives a bucketed column from the base date field and uses it as the join key.

The following units are supported. The value column shows the integer each unit produces, which is what the join key is compared against. Each example is the value for Wednesday 15 May 2024, 13:45 UTC.

A chronological unit identifies one period, so its value includes the year: month is 202405 for May 2024, and May 2025 has a different value.

UnitValueExample
yearCalendar year2024
halfYearYear times 10, plus the half (1 or 2)20241
quarterYear times 10, plus the quarter (1-4)20242
monthYear times 100, plus the month (1-12)202405
weekISO 8601 week-numbering year times 100, plus the week202420
dayYear, month and day, as yyyymmdd20240515
hourYear, month, day and hour in UTC, as yyyymmddhh2024051513
minuteYear, month, day, hour and minute in UTC, as yyyymmddhhmm202405151345

A cyclic unit repeats every year, quarter, month, week or day, so one value covers many periods: monthOfYear is 5 for May of every year.

UnitValueExample
halfOfYear1-2, where 1 is January to June1
monthOfQuarter1-3, the month's position within its quarter2
monthOfYear1-12, where 1 is January5
weekOfMonth1-5, where days 1-7 of the month are week 13
dayOfWeek1-7, where 1 is Monday and 7 is Sunday (ISO 8601)3
hourOfDay0-23, the hour in UTC13

A date field has no time of day, so hour, minute and hourOfDay read it as midnight: hourOfDay is always 0 for a date field.

To join a field holding a position, such as a month number from 1 to 12, use the cyclic unit: monthOfYear, not month. A month value such as 202405 never equals 5, so a relationship from month to a field holding 1-12 matches no rows.

Both sides of the relationship can use ::unit. When they do, both must declare the same unit. Using different units on each side is a configuration error.

A ::unit field holds an integer, so the other side must be one of these:

  • A ::unit field with the same unit.
  • A number field holding the same values.
  • A plain date or datetime field. Studio takes the same unit from it, on either side of the relationship. A relationship from targets.target_date to orders.order_date::quarter joins as if it named targets.target_date::quarter, so an order joins the target whose date falls in the same quarter.
// Valid: both sides extract the same unit
{
    id: 'orders-targets',
    source: 'orders.order_date::quarter',
    target: 'targets.target_date::quarter',
    type: 'many-to-one',
}

// Valid: a number field holding the month of the year, 1 to 12
{
    id: 'orders-months',
    source: 'orders.order_date::monthOfYear',
    target: 'months.month_number',
    type: 'many-to-one',
}

// Valid: the plain date takes the unit of the other side,
// so this joins on orders.order_date::quarter
{
    id: 'orders-targets',
    source: 'orders.order_date',
    target: 'targets.target_date::quarter',
    type: 'many-to-one',
}

// Error: mismatched units
{
    id: 'orders-targets',
    source: 'orders.order_date::month',
    target: 'targets.target_date::year',
    type: 'many-to-one',
}

The example above demonstrates joining at different granularities.

Star Schema Copy Link

A star schema puts this into practice: one fact table - the table holding your measures - related to several dimension tables, each by its own many-to-one relationship. It's named for the shape: the fact table sits at the centre with its dimensions radiating out around it.

productsorderItemscustomersregions many-to-onemany-to-onemany-to-one

The example above groups the orderItems fact table by two of its dimensions, products and regions, in a single widget - each dimension contributes its own many-to-one relationship, with no join between dimensions themselves.

This application of denormalised dimension tables produces fewer joins per query, leading to faster query times. This is why the star schema is the standard recommendation across the BI and analytics industry for read-heavy workloads such as dashboards and reporting.

Galaxy Schema Copy Link

Sometimes the data behind a dashboard arrives at more than one grain - daily order items and individual shipments, for example.

Avoid joining tables like these to each other directly. Instead, give each fact its own star, reusing the same dimension tables where they apply - a conformed dimension is a dimension table shared by more than one fact's star.

Multiple stars linked by conformed dimensions like this form a galaxy schema (also called a fact constellation). Joining the two facts directly can multiply rows on both sides, inflating measures in a way that's easy to miss.

The example below extends the star above with a second fact table: shipments (one row per shipment, a coarser grain than orderItems). Both facts relate to products and regions - now conformed dimensions, shared across both stars - while customers still relates to orderItems only:

productsorderItemscustomersshipmentsregions many-to-onemany-to-onemany-to-onemany-to-onemany-to-one 
const data = useMemo(() => { 
	return {
        sources: [
            { id: 'products', data: [/* ... */] },
            { id: 'customers', data: [/* ... */] },
            { id: 'regions', data: [/* ... */] },
            { id: 'orderItems', data: [/* ... */] },
            { id: 'shipments', data: [/* ... */] },
        ],
        relationships: [
            {
                id: 'order-items-products',
                source: 'orderItems.productId',
                target: 'products.id',
                type: 'many-to-one',
            },
            {
                id: 'order-items-customers',
                source: 'orderItems.customerId',
                target: 'customers.id',
                type: 'many-to-one',
            },
            {
                id: 'order-items-regions',
                source: 'orderItems.regionId',
                target: 'regions.id',
                type: 'many-to-one',
            },
            {
                id: 'shipments-products',
                source: 'shipments.productId',
                target: 'products.id',
                type: 'many-to-one',
            },
            {
                id: 'shipments-regions',
                source: 'shipments.regionId',
                target: 'regions.id',
                type: 'many-to-one',
            },
        ],
    };
}, []);

<AgStudio data={data} />

Keep each fact's measure in its own widget, both grouped by products.name: one widget for sum(orderItems.netSales), another for count(shipments.id). Both widgets agree on which products they're showing, since they share the same products dimension.

The example above shows each fact as its own widget, both grouped by products.name.

Putting sum(orderItems.netSales) and count(shipments.id) in the same widget is safe: Studio detects that both measures come from facts sharing the products dimension and aggregates each fact independently before combining the results - the same handling described for Calendars, not a special case limited to them.

Fan-out is still worth designing around, though. Joining two facts directly instead of through a shared dimension, or relating a dimension to a fact through a many-to-many relationship, are both shapes Studio can't safely combine automatically. See Fan-out Detection below for how Studio reports and controls these cases.

The ::unit Override is intended for date fields, and only changes the granularity of a single join.

Snowflake Schema Copy Link

A dimension table can itself be normalised into further tables - splitting products into products and categories, for example, so each category's name is stored once rather than repeated on every product row. Following a dimension's relationships out another hop like this is called a snowflake schema, named for the way its dimensions branch further outward than a star's.

productscategoriesorderItemscustomersregions many-to-onemany-to-onemany-to-onemany-to-one

The example above groups orderItems.netSales by categories.name and products.name together. categories is not a dimension of orderItems directly - the join reaches it by following products' own relationship out one more hop.

Snowflaking trades query speed for storage: each extra hop is another join a query must perform. Prefer denormalising a dimension - keeping category as a plain column on products - unless the normalised attribute is large, changes independently of products, or is shared by dimensions outside this star.

Fan-out Detection Copy Link

Fan-out is row duplication introduced by a join: a shape where a single fact row can match more than one row on the other side, so a measure summed across the join counts some rows more than once. Two relationship shapes trigger it:

  • Two fact tables joined directly, rather than through a shared conformed dimension (see Galaxy Schema above).
  • A dimension related to a fact through a many-to-many relationship, so a fact row can attribute to more than one dimension member and vice versa.
orderItemstags many-to-many

In the shape above, an order item tagged with three tags contributes to sum(orderItems.netSales) three times over once grouped by tags.name - once per tag - rather than once.

A widget that groups by nothing, such as a KPI showing one total, is not affected: Studio totals each table's measures over that table's own rows, and a calculated measure that combines fields from tables on both sides over the rows they match on.

Fan-out is almost never what you want. Inflated measures are easy to miss and hard to spot in review, since the numbers still look plausible - just wrong. Prefer remodelling the relationship (adding a conformed dimension, or normalising a many-to-many join through a bridge table) over accepting the duplication.

By default, Studio detects both shapes and reports a warning through the same collector used for other query validation issues, then runs the query anyway.

Accept the duplication for a specific relationship with acceptFanout: true, silencing its warning regardless of the data.options.fanout policy below:

{
    id: 'order-items-tags',
    source: 'orderItems.tagId',
    target: 'tags.id',
    type: 'many-to-many',
    acceptFanout: true,
}

Or control fan-out detection for the whole data model with data.options.fanout:

const data = useMemo(() => { 
	return {
        sources: [/* ... */],
        options: {
            fanout: {
                warning: true, // default - report a warning for any unaccepted fan-out finding
                execute: 'allow', // default - run the query anyway; set to 'prevent' to block it instead
            },
        },
    };
}, []);

<AgStudio data={data} />

With execute: 'prevent', a query whose measures come from two or more tables that share a dimension, and which cannot be answered for each table separately, is not run. The error names the tables and measures involved. The same applies when the tables share no relationship at all.

A query that joins through a relationship whose "one" side holds a repeated value is not run either, unless that relationship sets acceptFanout: true. Studio finds a repeated value only after reading the table, so it may refuse the first such query after running it. None of its rows are returned.

const data = useMemo(() => { 
	return {
        sources: [/* ... */],
        options: {
            fanout: false, // shorthand for { warning: false, execute: 'allow' } - never warn, never block
        },
    };
}, []);

<AgStudio data={data} />

Set acceptFanout on a relationship only once you've confirmed the duplication is what you want - for example, a many-to-many relationship onto a table that's already pre-aggregated to a grain with no finer key available. Downstream aggregates still need to account for the duplication; Studio just stops warning about it.