Relationships

When a model is created, some relationships are generated automatically, while others are added manually. This design approach achieves a balance between efficiently establishing relationships while maintaining acceptable performance.

Automatically created relationships:

Manually detected relationships:

All other relationships can be added manually or by using the Detect feature. When using Detect relationships, Data Hub applies intelligent profiling algorithms to identify and list all logical relationships based on field names and data comparisons. You can then select which relationships to create.

Semantic relationships exist in the data warehouse and also link the appropriate facts and dimensions within the Semantic model, enabling their use in report creation. In the Properties of each relationship, the Type can be set via a drop-down menu in the General section.

The RELATIONSHIPS tab for each pipeline shows both Semantic and Lookup relationships. Semantic relationships is indicated by the icon to the left.

Lookup relationships' purpose is to support transformations in the model but are not exposed in the semantic layer, there for they have no relationship icon indicator.

The Relationships section of the model screen only shows semantic layer relationships.

Mapping relationships

A mapping must be added for each field in the related pipeline that is marked as a key field. In many cases, the relationship will be between a single foreign key field in the current pipeline and a single primary key field in another pipeline.

If the For reporting check box in the General section of the Design panel is cleared, the relationship is used only to support lookup columns in the warehouse, and is not used in the cube. In this scenario, the relationship may be more flexible than a relationship used in the cube. Two additional controls, Not and Operator, appear in the Mappings section.

It is often necessary to create two or more relationships that map between the same two pipelines. Dates are a good example. Delivery date, Order date, Receipt date, invoice date are just a few that could exist on the same pipeline. In order to correctly report on all these, you want different dimensions for each of them in the cube, which requires a relationship for each.

Example

In this example, two relationships from Purchase order line exist to the Date pipeline: one for the Order date, and one for Actual Delivery Date. These are duplicate relationships to the same Pipeline and will act as role-playing dimensions in the cube. A third relationship will be added for Update Date.

A role-playing dimension is like a copy of another dimension, that plays the role of allowing another, different relationship to be made to the dimension.

NOTE the error message indicating a duplicate relationship when you first add it.

Corrective action:

Rename the relationship so it has a different name from the existing relationship. In this situation, the Relationship name box appears as a drop-down list as seen above.

The drop-down list displays all the role-playing dimension-type relationships already available in the data model. If the duplicate relationship you are creating is for the same purpose as a duplicate relationship already created for another pipeline, you can select that relationship name. Doing this means that the two duplicate relationships will share the same name. This results in a single role-playing dimension in the cube, rather than two very similar dimensions, reducing complexity

If another role playing dimension matching yours does not exist, simply type a relevant name to match your new relationship purpose.

Many-to-Many Relationships (M2M) enable entities to relate with each other (where one entity contains a parent instance with many children in the relating entity and vice versa).