Relationships

Most useful dashboards are built on a star schema. A fact table contains the numbers and foreign keys. Dimension tables contain the labels and business context users expect to see.

Semaphor Data Apps let you author source-bearing fields and let Semaphor resolve modeled relationships server-side. This is how you build filters and visuals with business labels without writing client-side joins.

Reference Schema

The examples on this page use a commerce model.

fact_orders
  order_id
  order_date
  customer_id
  campaign_id
  store_id
  revenue
  gross_margin

dim_campaign
  campaign_id
  campaign_name
  channel

dim_store
  store_id
  store_name
  state_id

dim_geo
  region_id
  region_name
  state_id
  state_name

fact_inventory_snapshot
  snapshot_date
  store_id
  product_id
  quantity_on_hand

fact_purchase_line
  purchase_date
  material_id
  net_purchase_value

fact_sales_line
  sale_date
  material_id
  sales_value

dim_material
  material_id
  material_family
  material_name

dim_product
  product_id
  product_name
  category_id

dim_category
  category_id
  category_name

Modeled relationships:

fact_orders.campaign_id -> dim_campaign.campaign_id
fact_orders.store_id -> dim_store.store_id
dim_store.state_id -> dim_geo.state_id
fact_inventory_snapshot.store_id -> dim_store.store_id
fact_inventory_snapshot.product_id -> dim_product.product_id
fact_purchase_line.material_id -> dim_material.material_id
fact_sales_line.material_id -> dim_material.material_id
dim_product.category_id -> dim_category.category_id

Mental Model

Your app should not infer joins from matching names such as campaign_id. Semaphor uses the semantic relationship model.

Pattern 1: Joined Projection

Use a joined projection when the query measure comes from one source, but a display field comes from a related dimension.

Business question:

Show revenue by campaign name.

Sources and fields:

const orders = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'fact_orders',
});

const campaign = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'dim_campaign',
});

const revenue = semaphor.field.measure('revenue', {
  source: orders,
  aggregate: 'SUM',
});

const campaignName = semaphor.field.dimension('campaign_name', {
  source: campaign,
});

Query:

const revenueByCampaign = semaphor.records({
  id: 'revenue_by_campaign',
  label: 'Revenue by Campaign',
  source: orders,
  fields: [campaignName, revenue],
  orderBy: {
    field: revenue,
    direction: 'desc',
  },
  limit: 10,
});

Semaphor resolves:

fact_orders.campaign_id -> dim_campaign.campaign_id

The governed query shape is equivalent to:

select
  dim_campaign.campaign_name,
  sum(fact_orders.revenue) as revenue
from fact_orders
left join dim_campaign
  on fact_orders.campaign_id = dim_campaign.campaign_id
group by dim_campaign.campaign_name
order by revenue desc
limit 10

The frontend renders returned rows. It does not join fact_orders to dim_campaign in client code.

Pattern 2: Joined Input Filtering A Fact View

Use a joined input when a filter is authored from a dimension, but it filters a fact-backed query.

Business question:

Let users select a campaign name and filter all order KPIs.

Option query:

const campaignId = semaphor.field.id('campaign_id', {
  source: campaign,
});

const campaignOptions = semaphor.inputOptions({
  id: 'campaign_options',
  inputId: 'campaign',
  source: campaign,
  labelField: campaignName,
  valueField: campaignId,
  searchField: campaignName,
  limit: 100,
});

Input handle:

const campaignInput = useSemaphorInput({
  id: 'campaign',
  label: 'Campaign',
  kind: 'filter',
  field: campaignId,
  operator: 'in',
  multi: true,
});

Apply the input to an orders query:

const result = useSemaphorQuery(revenueByCampaign, {
  inputs: [campaignInput],
});

The selected value is a campaign id from dim_campaign. The query source is fact_orders. Semaphor proves the relationship and applies the filter server-side.

Pattern 3: Same-Dimension Cascading Filters

Use same-dimension cascading when parent and child fields live in the same dataset.

Business question:

If Region is South, State should only show southern states.
If State is Texas, Region should narrow to South.

Fields:

const geo = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'dim_geo',
});

const regionId = semaphor.field.id('region_id', { source: geo });
const regionName = semaphor.field.dimension('region_name', { source: geo });
const stateId = semaphor.field.id('state_id', { source: geo });
const stateName = semaphor.field.dimension('state_name', { source: geo });

Option queries:

const regionOptions = semaphor.inputOptions({
  id: 'region_options',
  inputId: 'region',
  source: geo,
  labelField: regionName,
  valueField: regionId,
});

const stateOptions = semaphor.inputOptions({
  id: 'state_options',
  inputId: 'state',
  source: geo,
  labelField: stateName,
  valueField: stateId,
});

Pass active peer inputs to option queries:

const regionInput = useSemaphorInput({
  id: 'region',
  label: 'Region',
  kind: 'filter',
  field: regionId,
  operator: 'in',
  multi: true,
});

const stateInput = useSemaphorInput({
  id: 'state',
  label: 'State',
  kind: 'filter',
  field: stateId,
  operator: 'in',
  multi: true,
});

const stateOptionResult = useSemaphorQuery(stateOptions, {
  inputs: [regionInput],
});

const regionOptionResult = useSemaphorQuery(regionOptions, {
  inputs: [stateInput],
});

This supports bidirectional option narrowing. It does not auto-set values. Selecting Texas can narrow Region options to South, but it should not silently change the Region input value.

Pattern 4: Cascading Filters That Also Filter A Fact View

Region and State come from dim_geo, but orders only know store_id. The relationship path is:

fact_orders.store_id -> dim_store.store_id
dim_store.state_id -> dim_geo.state_id

Use the same Region and State input handles in a fact query:

const ordersRevenue = semaphor.metric({
  id: 'orders_revenue',
  label: 'Orders Revenue',
  source: orders,
  measures: [revenue],
  primaryMeasure: revenue,
});

const result = useSemaphorQuery(ordersRevenue, {
  inputs: [regionInput, stateInput],
});

Semaphor applies the selected geography fields through the modeled path to fact_orders.

Pattern 5: Conformed Dimension Filter Across Multiple Facts

Use a conformed dimension filter when one visible input should update multiple views backed by different fact tables.

Business question:

Use one Material Family filter for purchase spend and sales revenue.

The visible input comes from dim_material.

const purchaseLine = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'fact_purchase_line',
});

const salesLine = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'fact_sales_line',
});

const material = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'dim_material',
});

const materialFamily = semaphor.field.dimension('material_family', {
  source: material,
});

const netPurchaseValue = semaphor.field.measure('net_purchase_value', {
  source: purchaseLine,
  aggregate: 'SUM',
});

const salesValue = semaphor.field.measure('sales_value', {
  source: salesLine,
  aggregate: 'SUM',
});

const materialFamilyInput = useSemaphorInput({
  id: 'material_family',
  label: 'Material Family',
  kind: 'filter',
  field: materialFamily,
  operator: 'in',
  multi: true,
});

Each query binds that same input to its own relationship path.

const purchaseQuery = semaphor.records({
  source: purchaseLine,
  fields: [materialFamily, netPurchaseValue],
  inputs: [
    semaphor.bindInput(materialFamilyInput, {
      field: materialFamily,
      operator: 'in',
      relationshipHint: {
        relationshipIds: ['purchase_line_to_material'],
      },
    }),
  ],
});

const salesQuery = semaphor.records({
  source: salesLine,
  fields: [materialFamily, salesValue],
  inputs: [
    semaphor.bindInput(materialFamilyInput, {
      field: materialFamily,
      operator: 'in',
      relationshipHint: {
        relationshipIds: ['sales_line_to_material'],
      },
    }),
  ],
});

Semaphor resolves:

fact_purchase_line.material_id -> dim_material.material_id
fact_sales_line.material_id -> dim_material.material_id

The same inputId and selected value are reused. The field binding and relationship proof are query-specific.

Pattern 6: Shared Date Range Across Multiple Facts

Use a shared date range when one visible date control should filter multiple event or fact views, and each view has its own date field.

Business question:

Use one Date Range control for purchase and sales trends.

const purchaseDate = semaphor.field.date('purchase_date', {
  source: purchaseLine,
});

const saleDate = semaphor.field.date('sale_date', {
  source: salesLine,
});

const dateRangeInput = useSemaphorInput({
  id: 'date_range',
  label: 'Date Range',
  kind: 'filter',
  field: purchaseDate,
  operator: 'between',
});

const purchaseTrend = semaphor.records({
  source: purchaseLine,
  fields: [purchaseDate, netPurchaseValue],
  inputs: [
    semaphor.bindInput(dateRangeInput, {
      field: purchaseDate,
      operator: 'between',
    }),
  ],
});

const salesTrend = semaphor.records({
  source: salesLine,
  fields: [saleDate, salesValue],
  inputs: [
    semaphor.bindInput(dateRangeInput, {
      field: saleDate,
      operator: 'between',
    }),
  ],
});

The shared input value is the same for both views. The purchase view filters purchase_date; the sales view filters sale_date.

Snapshot dates need explicit semantics

This pattern covers simple event and fact date fields. Latest-snapshot, as-of, and slowly changing dimension semantics should be modeled explicitly instead of treated as ordinary date filters.

Pattern 7: Fact-Bridged Cascading Options

Sometimes two dimensions are not directly related. They become related only through a fact population.

Business question:

When Store is Chicago Loop, Product should only show products currently stocked in that store.

There is no direct relationship between dim_store and dim_product. They are related through fact_inventory_snapshot.

const inventory = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'fact_inventory_snapshot',
});

const product = semaphor.source.semantic({
  domainId: 'commerce',
  datasetName: 'dim_product',
});

const productId = semaphor.field.id('product_id', { source: product });
const productName = semaphor.field.dimension('product_name', {
  source: product,
});

const productOptions = semaphor.inputOptions({
  id: 'product_options',
  inputId: 'product',
  source: product,
  labelField: productName,
  valueField: productId,
  population: {
    kind: 'related_population',
    baseSource: inventory,
  },
  limit: 100,
});

Then execute product options with the active Store input. This assumes you already created a storeInput filter from dim_store.store_id.

const productOptionResult = useSemaphorQuery(productOptions, {
  inputs: [storeInput],
});

Semaphor uses fact_inventory_snapshot as the population that proves which products are relevant to the selected store.

If you also want Category to narrow Product, model the direct relationship dim_product.category_id -> dim_category.category_id and use the same direct cascading pattern from the previous section.

Pattern 8: Duplicate Labels

Labels are not always unique. Two stores can both be named Springfield.

Use disambiguation fields:

const storeOptions = semaphor.inputOptions({
  id: 'store_options',
  inputId: 'store',
  source: store,
  labelField: storeName,
  valueField: storeId,
  disambiguationFields: [city, state],
  limit: 100,
});

The UI can render:

Springfield - Springfield, IL
Springfield - Springfield, MO

The selected value remains store_id.

Pattern 9: Ambiguous Relationships

Sometimes multiple valid relationships exist.

fact_orders.customer_id -> dim_customer.customer_id
fact_orders.bill_to_customer_id -> dim_customer.customer_id
fact_orders.ship_to_customer_id -> dim_customer.customer_id

If the user says "revenue by customer segment", Semaphor should not guess whether that means buyer, billing customer, or shipping customer.

In that case the result should include relationshipDiagnostics.status: 'ambiguous'. The app or agent should ask the user to choose the relationship, then pass an explicit hint if needed.

const buyerSegmentInput = useSemaphorInput({
  id: 'buyer_segment',
  label: 'Buyer Segment',
  kind: 'filter',
  field: customerSegment,
  operator: 'in',
  relationshipHint: {
    relationshipIds: ['rel_orders_buyer_customer'],
  },
});

Relationship hints should use modeled relationship ids. Do not use free-form text aliases.

Pattern 10: Fanout Risk

A relationship can be unsafe for projection even if it is useful for filtering.

Example:

fact_orders -> fact_order_items
cardinality: one_to_many

Joining order items can duplicate order-level revenue. Semaphor may allow a semi-join filter through a bridge, but block or warn for a projection that would multiply measures.

Expected diagnostic:

relationshipDiagnostics: {
  status: 'fanout_risk',
  fanoutRisk: {
    status: 'error',
    message: 'Joining order items can duplicate order-level revenue.',
  },
}

Relationship Diagnostics

Check executionResult when relationship-aware queries fail or return partial results.

const result = useSemaphorQuery(revenueByCampaign);

const diagnostics = result.executionResult?.relationshipDiagnostics;

Common diagnostics:

Status	Meaning	What to do
`not_required`	The query does not need a relationship path.	No action needed.
`resolved`	Semaphor found a safe modeled path.	No action needed.
`missing`	No usable modeled path connects the sources, or the path is incomplete for the requested shape.	Model the relationship, complete the key/date semantics, add a valid option population, or remove the cross-source field.
`ambiguous`	More than one valid path exists.	Ask the user to choose a relationship and pass a relationship id hint.
`fanout_risk`	A join can duplicate measures.	Use filtering, change projection, or model safe aggregate semantics.
`unknown`	Semaphor could not classify the relationship state.	Inspect `message`, `warnings`, and `recommendedNextStep`.

Relationship Checklist

Use related fields directly in query specs. Do not join in client code.
Use ids as option values and labels as option labels.
Use population.kind = 'related_population' when dimensions are only related through a fact or bridge population.
Omit dependencies for normal cascading filters; auto is the default.
Use relationshipHint.relationshipIds only when Semaphor reports ambiguity.
Treat fanout diagnostics as correctness warnings, not UI errors to hide.

Relationships

On this page