select pa12. AS QUARTER_ID,
a13. AS QUARTER_DESC0,
pa12. AS WJXBFS1,
pa12. AS WJXBFS2
from pa12,
a13
where pa12. = a13.
and (pa12. = 1
and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature onlyWhere clause driving table = Use fact table (Default)Where clause driving table = Use lookup table
| select pa12. AS QUARTER_ID,
a13. AS QUARTER_DESC0,
pa12. AS WJXBFS1,
pa12. AS WJXBFS2
from pa12,
a13
where pa12. = a13.
and (pa12. = 1
and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature onlyWhere clause driving table = Use fact table (Default)Where clause driving table = Use lookup table
|
|
|
select a11.CALL_CTR_ID CALL_CTR_ID,
max(a12.CENTER_NAME)
CENTER_NAME,
a11.MONTH_ID MONTH_ID,
max(a13.MONTH_DESC)
MONTH_DESC,
sum(a11.TOT_COST) WJXBFS1
from ITEM_MNTH_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)
where (a11.MONTH_ID in (200001)
and a12.COUNTRY_ID in (1)
and a11.ITEM_ID in (1))
group by a11.CALL_CTR_ID,
a11.MONTH_ID
| select a12.CALL_CTR_ID CALL_CTR_ID,
max(a12.CENTER_NAME)
CENTER_NAME,
a13.MONTH_ID MONTH_ID,
max(a13.MONTH_DESC)
MONTH_DESC,
sum(a11.TOT_COST) WJXBFS1
from ITEM_MNTH_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)
where (a13.MONTH_ID in (200001)
and a12.COUNTRY_ID in (1)
and a11.ITEM_ID in (1))
group by a12.CALL_CTR_ID,
a13.MONTH_ID
Both reports return the same results, but this report uses the fact table for [MONTH_ID] and [ITEM_ID] and the lookup table for [COUNTRY_ID].
Having in mind that the structure of the [ITEM_MNTH_CTR_SLS] table is:
|
The reasons for this behavior are the following:
•[ITEM_ID] exists in the fact table and the filter can ONLY be applied to it.
•[COUNTRY_ID] does NOT exist in the fact table, so ONLY the lookup table can drive this filter.
•[MONTH_ID] exists in both, the fact and the lookup table in this case the filter is applied to the fact table because the VLDB property says that. This report uses the lookup table for the filters on [MONTH_ID] and [COUNTRY_ID] but still goes to the fact table to apply the filter on [ITEM_ID].
The reasons for this behavior are the following:
•[ITEM_ID] exists in the fact table and it can ONLY be retrieved from it. The setting will NOT force the Strategy Engine to include the [LU_ITEM] table.
•[COUNTRY_ID] exists in the lookup table and this table is already present in the FROM clause, so it can indeed be extracted from the lookup table.
•The filter on [MONTH_ID] is applied to the lookup table because the VLDB property dictates so.
|
The reasons for this behavior are the following:
•[ITEM_ID] exists in the fact table and the filter can ONLY be applied to it.
•[COUNTRY_ID] does NOT exist in the fact table, so ONLY the lookup table can drive this filter.
•[MONTH_ID] exists in both, the fact and the lookup table in this case the filter is applied to the fact table because the VLDB property says that. This report uses the lookup table for the filters on [MONTH_ID] and [COUNTRY_ID] but still goes to the fact table to apply the filter on [ITEM_ID].
The reasons for this behavior are the following:
•[ITEM_ID] exists in the fact table and it can ONLY be retrieved from it. The setting will NOT force the Strategy Engine to include the [LU_ITEM] table.
•[COUNTRY_ID] exists in the lookup table and this table is already present in the FROM clause, so it can indeed be extracted from the lookup table.
•The filter on [MONTH_ID] is applied to the lookup table because the VLDB property dictates so.
|
In summary, the 'Where clause driving table' VLDB property, will NOT force the Strategy Engine to include tables in the where clause. If the filter cannot be switched to another table, the Strategy Engine will apply the filter to the tables that allow it, overriding in some cases the value of the setting.
If it is required to always include the lookup tables to apply the filters on them, then the 'Partial star join' VLDB property needs to be used in conjunction to the 'Where clause driving table'.
If this setting is changed on the latter example:
