SYMPTOM:
When a user runs a report that includes a template with attributes at the lowest level of their respective dimensions and/or the IDs for those attributes are the only IDs included in the fact table, the SQL is missing the GROUP BY clause:
select a24.STORE_NBR STORE_NBR,
a24.STORE_DESC STORE_DESC,
a23.CLASS_NBR CLASS_NBR,
a23.CLASS_DESC CLASS_DESC,
a23.ITEM_NBR ITEM_NBR,
a23.ITEM_DESC ITEM_DESC,
(a21.REG_SLS_QTY) REGULARSALE,
from STORE_ITEM_00 a21,
LOOKUP_ITEM a23,
LOOKUP_STORE a24
where a21.CLASS_NBR = a23.CLASS_NBR
and a21.ITEM_NBR = a23.ITEM_NBR
and a21.STORE_NBR = a24.STORE_NBR
This SQL returns the whole fact table instead of grouping the attributes according to the template. The correct SQL is as follows:
select a24.STORE_NBR STORE_NBR,
max(a24.STORE_DESC) STORE_DESC,
a23.CLASS_NBR CLASS_NBR,
max(a23.CLASS_DESC) CLASS_DESC,
a23.ITEM_NBR ITEM_NBR,
max(a23.ITEM_DESC) ITEM_DESC,
sum(a21.REG_SLS_QTY) REGULARSALE
from STORE_ITEM_00 a21,
LOOKUP_ITEM a23,
LOOKUP_STORE a24
where a21.CLASS_NBR = a23.CLASS_NBR and
a21.ITEM_NBR = a23.ITEM_NBR and
a21.STORE_NBR = a24.STORE_NBR
group by a24.STORE_NBR,
a23.CLASS_NBR,
a23.ITEM_NBR
CAUSE:
For the above SQL, the template includes the attributes Store and Serial Number, along with a COUNT(Cost_Amt) metric, as follows:
The fact table STORE_ITEM_00 has the following structure:
|
|
|
|
|
Store, Class, and Item are the only attributes related to this table, so the SQL Generation Engine assumes that they constitute the primary key for this table. Therefore, it generates SQL without a Group By expression.
There might be a situation in which the attributes included in the template do not constitute the primary key, so the SQL may be returning the whole table, which in most cases is not desired.
ACTION:
If the attributes included in the template do not constitute the primary key, the following actions must be taken to correct the issue:
