Skip to main content

Common Relationships Problems

Unconnected Models​

Symptoms of Unconnected Models​

Unresponsive Filters​

When filters don't affect your metrics, it might indicate unconnected models.

Repeated Values​

When adding a dimension causes metric values to repeat across every row.

Understanding the Issue​

This occurs when your dimension or filter's model lacks a relationship path to the metric's model.

Solution​

Create appropriate relationships between the models to establish the necessary connections.

Why Are Unconnected Models Allowed?​

Unconnected models serve legitimate use cases. Consider this example:

A currency conversion scenario:

  • You have a sales model tracking transactions
  • A separate currencies model stores exchange rates
  • These models don't share natural relationships

But, you can use currencies in a metric of sales for currency conversion:

Sales in selected currency
sales * max(currencies.exchange_rate)

In this case:

  • Filters on sales won't affect max(currencies.exchange_rate)
  • Users can filter currencies to select their target currency
  • This separation is intentional and useful

Ambiguous Relationships​

Symptoms​

SQL Warning​

When ambiguous relationships are detected between selected dimensions, the generated SQL includes a warning comment highlighting this issue.

Unexpected Metric Results​

When multiple paths active to link a metric to a dimension, AQL selects one path using a ranking algorithm. If the system chosen path differs from what you expect, the metric may return unexpected values. The selected path is indicated in comments above the SQL query.

Warning relationships view​

When you add new relationships in the dataset, Holistics will automatically detect path ambiguity and alert you with a warning.

Understanding the Issue​

Ambiguous relationships occur when there are cycles (loops) of active relationships in your data model. For example:

In such cases, there are at least two possible paths between any two models in the cycle. When exploring data that involves multiple models, these multiple paths can lead to different interpretations of the relationships, potentially resulting in unexpected results.

Solutions​

1. Disable Redundant Relationships​

The simplest solution is to disable one of the relationships that creates a cycle, leaving only one relationship active by default.

2. Explicitly Enable Relationships​

When you need a specific relationship for a metric, enable it using with_relationships:

// Activate a specific relationship between order_items and products
sum(order_items.revenue) | with_relationships(order_items.product_id > products.id)

3. Override Path Priority​

If with_relationships doesn't use your desired relationship, it might be because the path that use it is in a lower tier than another active path. AQL ranks paths into tier based on performance and common use cases to avoid making crazy and expensive joins. To force the use of a specific path, you can disable the higher-priority path:

// Disable the default relationship between products and merchants
sum(order_items.revenue)
| with_relationships(relationship(products.merchant_id > merchants.id, 'two_way', false))

4. Modeling​

You can also remove the ambiguity by duplicate models to remove cycle. E.g. Duplicate Cities and Countries models into dedicated models for Users and Merchants:

Let us know what you think about this document :)