The screen shot below illustrates the setting "The key specified is the true key for the warehouse table" in the logical view of a warehouse table:
This setting is only applicable when the attributes on the template are comprised of all the attributes listed as table keys in the logical view of the fact table. Therefore, when checked, this setting forces the Structured Query Language (SQL) to not aggregate the data and to pull only from the fact tables directly.
The following SQL demonstrates the difference between two identical reports when this setting is checked and when it is not. The report SQL is provided below the grid view.
The first report was run against the fact table City_Subcateg_Sls without the "The key specified is the true key for the warehouse table" checked. It demonstrates aggregation by the metric:
select a11.CUST_CITY_ID CUST_CITY_ID,
max(a12.CUST_CITY_NAME) CUST_CITY_NAME,
a11.SUBCAT_ID SUBCAT_ID,
max(a13.SUBCAT_DESC) SUBCAT_DESC,
sum(a11.TOT_COST) COST
from CITY_SUBCATEG_SLS a11
join LU_CUST_CITY a12
on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)
join LU_SUBCATEG a13
on (a11.SUBCAT_ID = a13.SUBCAT_ID)
group by a11.CUST_CITY_ID,
a11.SUBCAT_ID
The following SQL demonstrates that by having all the keys from the fact table on the report template, the setting "The key specified is the true key for the warehouse table" allows the metric to return the values straight from the fact table without aggregation. This optimizes the SQL generated considerably:
select a11.CUST_CITY_ID CUST_CITY_ID,
a12.CUST_CITY_NAME CUST_CITY_NAME,
a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_COST COST
from CITY_SUBCATEG_SLS a11
join LU_CUST_CITY a12
on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)
join LU_SUBCATEG a13
on (a11.SUBCAT_ID = a13.SUBCAT_ID)
