This example is referenced in my other post about the usage of the join cardinality settings in SAP HANA Calculation Views. Please have a look at the other post to get a better understanding of the context for this example
This example will demonstrate how to get a proposal for the join cardinality by the modeling tool. The proposal is based on the data cardinality that holds for the join fields between the tables at the time of the proposal. Prerequisite is that the join is directly based on tables.
Let’s start with a Calculation View that only includes a join of the two tables “salesOrders” and “employees” (the model and tables are discussed in more details here). Join the two tables with a left outer join on field “employee”, table “salesOrders” being the left table:
Requesting join cardinality proposals. Button for requesting proposals is shown in yellow
As described above, table “salesOrders” has only distinct values in column “employee”. Similarly, column “employee” in table “employees” also has no repeated values. Therefore, if you wanted to set the cardinality for a join on the fields “employee” you would set it to “1..1”. As long as only tables are involved in the join you can ask the modeling tool for proposals of cardinality. Here is, how you could do it:
Click on the button marked in yellow in the figure above to get an proposal of the join cardinality. Based on the cardinality of the field “employee” in the tables “1..1” will be proposed. For the sake of demonstration remove the join and create a new left-outer join. This time between “employee” of the left table and “manager” of the right table. As there are multiple duplicates in column “manager” (e.g., “Alice”) the proposal by the modeling tool is “1..n”.
This means: when you include only tables in your join the modeler will propose a cardinality for the join field based on the current data cardinality. The following is a demonstration that no proposals are available if the join includes other nodes.
Create another view. This time do not add the table “employees” directly into the join node but place it first in a projection:
Request for join cardinality proposals is failing because another node is involved in the join.
Now you will receive no proposal for the join cardinality. The reason is that a proposal is only available when directly joining tables. In this example one table enters via a projection though.
To sum up, join cardinality proposals are only available if tables are directly involved in the join and not entering via other nodes.
This example is referenced in my other post about the usage of the join cardinality settings in SAP HANA Calculation Views. Please have a look at the other post to get a better understanding of the context for this example.