Bert Swope
Denodo Model Best Practices For Creation of Associations
What Are Denodo Associations?
In denodo, associations follow the same concept as modeling tools, which can be described as an ‘on-demand join.’
Where Should Associations Be Created In the Denodo Model?
You don’t necessarily need to define an Association at every level; usually, the best practice is to apply associations at the following points:
- On final views published for data consumers, indicating relationships between related views; Especially, on published web services.
- On the component views below, any derived view brings together disparate (dissimilar) data sources. The associations should be defined as Referential Constraints whenever appropriate to aid the optimization engine.
- On the component views below, any derived view that joins a “Base View from Query” with standard views since Base Views from Query cannot be rewritten by the denodo optimization engine. Often Base Views from Query create performance bottlenecks.
These best practices should cover the majority of scenarios; beyond these guidelines, it is best to take an ad-hoc approach to create Associations when you see a specific performance/optimization.
Why Are Associations important in Denodo?
In a nutshell, associations performance and the efficiency of the denodo execution optimizer along with other model metadata, such as:
- The SQL of the view(s)
- Table metadata (Table Keys {PK, FK), Virtual Partitions…etc.)
- Data statistics, which are used by the Cost Based Optimizer (CBO)
- Associations enable cross-view joins in Denodo catalog self-service queries
Denodo Security Role Note
There is not currently an explicit privilege to grant CREATE ASSOCIATION to a user/role without granting rights to create other objects.
