Relations

​

Introduction to relations

Usually, there is more than one entity in data. For example, TPC-H example has few “natural” entities in the data: customers, orders, line items of an order and parts in a line item.

Relations have directions - a customer may have many orders. Each order may have many line items.

A Relation in Honeydew connects entities. It can be a 1:1 or a 1:many relationship.

The “1” side of a relationship always goes to a Granularity Key of an entity.

For example, orders and customers are connected: custkey of orders to custkey of customers. custkey is the granularity key of customers.

Once there is a relationship defined, you can look at data together. For example, you may want to see all the orders of a customer. Honeydew will know how to connect the two.

Relationships control:

• How JOINs are performed between entities
• How filters on entities affect other entities
• How aggregations are performed across entities

​

Join Properties

​

Join Type

Following types are supported:

• Inner
• Left
• Right
• Full Outer

By default, if not configured otherwise, Honeydew connects entities via LEFT join between many side (for example a fact table) and the 1 side (for example a dimension table)

​

Direction

Relationships typically convert to JOINs when are used to compute semantics.

The direction (1:many) affects how calculations are performed after the join, by telling the engine which rows are duplicated.

See mixing granularities for more information on how that is handled.

​

Connection

Connection defines how entities connect. There are two ways to define:

1. Selecting connecting fields: default mode, for the majority of joins
2. Build a SQL expression: for complex joins

​

Connecting entities using attributes

Relationship must include the choice for source and destination columns. Can define more than one for a composite join connection.

Joins based on that definition are always equality joins using the chosen columns.

​

Example: Fact and Dimension Tables

Given two tables:

• fact_sales: a fact table tracking order transactions that has a foreign key customer_id
• dim_customer: a dimension table holding current customer information that has a primary key customer_id

Would want to connect each order with the associated customer.

Define this connection:

1. Connect on the customer_id field from both sides.
2. Set a “many-to-one” relationship from fact_sales to dim_customer

Sample data:

fact_sales (Fact Table)

dim_customer (Dimension Table)

Result of a query on both:

​

Connecting entities using a custom SQL expression

Relationships may be set using custom SQL expressions, using any logic to implement the join.

It can be set to define an equality join that is equivalent to using connection with fields:

fact_orders.customer_id = dim_customer.id

Expressions can use any source table attribute or calculated attribute.

Expression connections can be used to define more complex relationships:

• Filtered joins:

  Connect ƒact_orders with dim_customer using a condition

  fact_orders.customer_id = dim_customer.id AND
  dim_customer.type != 'INTERNAL'
  

• Range joins:

  Connect ƒact_orders with dim_customer over a specific time range

  fact_orders.customer_id = dim_customer.id AND
  fact_orders.order_date >= dim_customer.upgrade_date
  

• Alternative keys:

  Connect ƒact_orders with dim_customer using a field which is not a key of dim_customer

  fact_orders.customer_id = dim_customer.alternative_id
  

• Multi-entity joins:

  Given that fact_orders is connected with dim_region dimension Connect ƒact_orders with dim_customer using a field from dim_region

  fact_orders.customer_id = dim_customer.id AND
  dim_regions.id = dim_customer.region_id
  

​

Example: Fact and a Slowly Changing Dimension (SCD2)

Given two tables:

• fact_sales: a fact table tracking order transactions that has a foreign key customer_id
• dim_customer: a dimension table tracking customer information over time (SCD Type 2). It has multiple entries per customer_id with validity ranges.

Would want to connect the sales data with the correct historical customer information as it was at the time of the order.

To associate each order with the correct customer version at that point in time, use a custom SQL expression on valid_from and valid_to:

fact_sales.customer_id = dim_customer.customer_id
AND fact_sales.order_date >= dim_customer.valid_from
AND fact_sales.order_date < dim_customer.valid_to

And set a “many-to-one” relationship from fact_sales to dim_customer

Sample data:

fact_sales (Fact Table)

dim_customer (SCD2 Dimension Table)

Result of a query on both:

​

Union

Honeydew does not support directly a UNION connection between entities.

If need to connect tables A and B, create a custom SQL query source data with the following sql:

SELECT * FROM A
UNION ALL
SELECT * FROM B

​

Cross-Filtering

What happens when you query attributes from one entity (customers.name), while filtering on another entity (parts.brand = 'Brand#55') ? Would you get the list of customers that had orders of parts of that brand, or would you get all customers?

The answer is based on whether a filter from parts “flows” the path of parts → order_lines → orders → customers or not. Each relationship can control in what directions can filters pass through (“cross-filter”).

Following settings are supported (given a 1:many relationship):

• Both directions (default)
• From one to many
• From many to one
• Not at all

​

Default Cross Filtering

Cross filtering options depend on join type:

​

Example Use Cases

Any supported combination can be used. Common use cases include:

• Users look at data from dimensions and facts using filters on both: use both cross-filtering and LEFT join type.

  In that case every filter used by the user will propagate to any entity regardless on where it comes from.

• Same, but with referential integrity assumption (i.e. it is never desired to count orders of a non existing customer): use both cross-filtering and INNER join type.

  In that case every filter used by the user will propagate to any entity regardless on where it comes from. Additionally, any values that do not exist in a dimension will be removed from the fact when joined.

• Users look at metrics based on a fact table, and use a dimension entity only as filter or a group by: use one-to-many cross- filtering and RIGHT join direction.

  In that all the values of that dimension always present in a group by, and can filter the fact.

• Dimension entity that is only as part of a filtered metric (typically with attribute lookup): use none and OUTER so no actions on either fact or dimension impact values directly, and only filter the metric.

​

Modeling many:many relations

Honeydew does not support a many:many relationship . However a many:many relationship is possible by building a connecting table (sometimes called a “junction table”):

For example authors and books is a many:many relation (authors may write many books, and a book may have multiple authors).

To model it, create a connection table that has as key book id and author id, and connects to both

Metrics and calculations that refer to both can be added on the connection table.

​

YAML Schema

Relations are defined in the entity YAML.

The schema for relation subsection is:

relations:
  - target_entity: [target-entity]
    rel_type: [many-to-one / one-to-many]
    rel_join_type: [inner/left/right/outer]
    cross_filtering: [both/many-to-one/one-to-many/none]
    connection:
      - src_field: [src field of join]
        target_field: [target field of join]
      - ...
    connection_expr:
      sql: |-
        expression

Fields:

• target_entity: entity to connect to
• connection: a list of fields for a join condition. Must fully cover the primary key of the entity on the “one” side of the relation.
• connection_expr: a custom join expression, if connection is not set. The sql expression may only use attributes.
• rel_type: logical direction of the relationship
• rel_join_type (optional): the type of default join to apply
• cross_filtering (optional): the cross-filtering configuration to apply