Denodo associations, referential constraints are part art and part science. The importance of both primary keys and associations and their effect on the denotative optimizer is hard to overstate. Combining appropriately applying primary keys and associations based on actual view use is an essential element in tuning denodo and getting the denodo optimizer to provide the best results. To simplify matters, here are some basic concepts to help get you started.
Enterprise Relationship Diagrams (ERD)
Associations do more than just reflect the source system Enterprise Relationship Diagrams (ERD). To be denodo associations need to:
- Be added based on actual use — not only based on source system Enterprise Relationship Diagrams (ERD). This is especially true if you are skipping tables for simplicity or efficiency purposes, which otherwise would have been used based on the Enterprise Relationship Diagram (ERD).
- Associations need to be applied for views that are being reused in other views. These associations need to mirror the joins to support the join and help the optimizer understand the actual relationship.
Placement of Denodo Associations
The knowledge base article (‘Best Practices to Maximize Performance II: Configuring the Query Optimizer’) is a bit misleading as it does imply that you need associations in both layers. Ideally, associations between entities in the same data source will be defined as Foreign Key constraints and can be imported from the data source (at the base view layer). Associations defined within the Denodo Platform are best defined in the semantic layer (i.e., between user-facing derived views). There is no need to define duplicate associations at other levels. Denodo is planning to update the (‘Best Practices to Maximize Performance II: Configuring the Query Optimizer’) document to clarify this understanding of the proper placement of associations within the logical layer structure of denodo.
Importance Primary And Foreign Keys
It is essential when working with associations that the primary keys (PK) and foreign keys (FK) between views are correctly understood and identified. These primary key (PK) and foreign key (FK) indexes need to be applied (if not already imported) to the affected views, in addition to applying the referential constraints of the Association to provide the maximum opportunity for the denotative optimizer to make the correct choices.
Determining the “Principal” and “Dependent” Association Constraint
The referential constraint is defined as part of an association between two entity types. The definition for a referential constraint specifies the following information:
- The “Principal” end of the constraint is an entity type whose entity Primary key (PK) is referenced by the foreign key (FK) dependent end.
- The “Dependent” end of the constraint is the foreign key (FK), which references the Primary Key (PK) of the opposite side of the constraint.
Not all associations will have a Primary Key (PK) and Foreign Key (FK) relationship. Still, where these relationships exist, the referential constraint must be applied and applied correctly to ensure the denodo optimizer uses the correct optimization logic.
General Guidance When working with Data Warehouse Schemas
The basic guidelines for association referential constraints are:
- Between dimension and Fact: the dimension is the principle
- Between two Facts: Parent fact (the one, in a one-to-many relationships) is Primary
- Between dimension and Bridge: The Dimension is primary
Denodo > Community > denodo Platform 8.0 > Associations