The 'Set as key' option is used for defining the lookup table primary key for the attributes when generating SQL with Strategy Engine. Any of the lookup table columns can be set as the attribute key by using 'Set as key' feature, but only the forms that are actual primary keys in the lookup tables should be used. The lookup table primary keys are used for aggregating the data against the fact tables by matching the lookup table id values with the fact table id values.
For information on how to set a compound primary key for the lookup tables see the following tech note KB4641.
When the desc form is selected as the key form for the category attribute using 'Set as key' feature, the user will be prompted to disable the original id form. This should be done for having only one unique id column used to identify the category attribute elements in the lookup table. In this case the invalid objects include the id form, but also the possible parent or child attributes might be removed as a part of the process. Strategy is automatically recognizing the parent child relationships that become invalid due to the change of the lookup table primary key.
EXAMPLE:
Consider the following report as an example when using the above mentioned attribute.
The following sql is generated when the 'id' form is used as the id form of category attribute.
select a11.CATEGORY_ID CATEGORY_ID,
max(a12.CATEGORY_DESC) CATEGORY_DESC,
sum(a11.TOT_UNIT_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11
join LU_CATEGORY a12
on (a11.CATEGORY_ID = a12.CATEGORY_ID)
group by a11.CATEGORY_ID
The following sql is generated after the 'desc' form is changed as the key form of the attribute.
select a12.CATEGORY_DESC CATEGORY_DESC,
sum(a11.TOT_UNIT_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11
cross join LU_CATEGORY a12
group by a12.CATEGORY_DESC
Joins
The main difference in the generated SQL are the joins. Since category_desc form is selected as the key for the attribute and this column is not in the fact table qtr_category_sls, a cross join is generated in order to retrieve the matching values between lu_category lookup table and the category_sls fact table.
Key form
When the id form is used as key, it can be seen in the select clause and in the group by clause. When desc form is used as unique key column in the report the desc form is used in the select and group by clauses.
NOTE:
The use of this feature depends greatly on the underlying warehouse and the data model. By mapping lookup table columns that are not real id columns as Strategy id forms might cause wrong results hence this feature dictates the join path generation for the fact table aggregation.
KB20727
