You can translate the question and the replies:

Associations Vs Joins

I would like to understand what option is better to use in the scenario of star schemas: * Only Joins in models for the derived views * Only Associations in Derived views * A combination of Joins and Association in Derived Views Please explain in what scenarios we can use each of these options from the best practices perspective. Regards, Sumit Gupta
user
20-10-2020 17:01:35 -0400

1 Answer

Hi, Joins combine records from different views based on join condition just like in databases. Joins would be required for creating derived views between different views There are different types of joins and join methods. You can take a look at following documents of the Virtual DataPort guide for more details. 1) [Join Operation](https://community.denodo.com/docs/html/browse/latest/vdp/vql/queries_select_statement/from_clause/join_operation) 2) [Optimizing Join Operations](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations) Associations represent relationship between elements (rows) of two views. Associations can also be marked as referential constraints and they would behave like Primary key-Foreign key relationship in database. For more details on Associations and why they are useful take a look at [Associations in Denodo](https://community.denodo.com/kb/view/document/Associations%20in%20Denodo?category=Combining+Data) knowledge base article. Regarding your final question, Associations along with Joins can be very useful from the performance perspective. They help the Virtual DataPort engine determine the best execution plan and optimize the queries. Two optimization techniques where associations are useful are Aggregation Push Down and Branch Pruning. Take a look at the [Optimization for Logical DataWarehouse](https://community.denodo.com/kb/view/document/Denodo%20Query%20Optimizations%20for%20the%20Logical%20Data%20Warehouse?category=Performance+%26+Optimization) for performance optimization using Associations. The example mentioned in the document is of a typical star schema. Hope this helps!
Denodo Team
21-10-2020 07:50:44 -0400
You must sign in to add an answer. If you do not have an account, you can register here