SYMPTOM:
Execute a report with a transformation metric and the VLDB property Joins > DSS Star Join is set to 'Partial star join,' redundant tables may appear in the FROM clause. Depending on the data in the tables, it may affect report results.
Consider a report in the Strategy Tutorial project with the following definition.
In this report, the DSS Star Join VLDB property is set to 'Partial star join,' as illustrated below:
This report generates SQL as follows.
select a13.YEAR_ID YEAR_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from YR_CATEGORY_SLS a11
join LU_YEAR a12
on (a11.YEAR_ID = a12.YEAR_ID)
join LU_YEAR a13
on (a11.YEAR_ID = a13.PREV_YEAR_ID)
group by a13.YEAR_ID
Note that LU_YEAR is joined twice in the query, under separate aliases (a12 and a13), and that only alias a13 is actually used in the query. A12 is introduced but ignored.
In this case, the report results are correct as expected, but there may be other scenarios in which the data in the warehouse tables produce incorrect results.
CAUSE:
By definition, the partial star join will introduce lookup tables for all attributes present in the template, filter or metric dimensionality into the corresponding SQL pass. With the partial star join disabled, the set of attributes is reduced to the minimum required to retrieve the data prior to generating the FROM clause.
A transformation metric uses a hidden attribute called a 'transformation attribute' to join to the transformation table. Without the star join, it may be necessary to consider only the transformation attribute when constructing the join tree (for example, if the attribute's lookup table and transformation table are the same). When the partial star join is enabled, it is necessary to bring in the lookup tables for the normal project attribute as well as the transformation attribute.
In the example above, the LU_YEAR table includes the transformation column; hence the table is joined once for the normal Year attribute and again for the transformation attribute. If the transformation used a different table, there would still be the unused table in the FROM clause but the same table would not appear twice.
This report returns the expected results, however, because of the separate table aliases. The two joins are considered individually and their results are not intersected. One instance of the table produces the raw attribute IDs matching the fact table, while the other produces the values after applying the transformation. This can be seen by modifying the SQL manually to include the YEAR_ID column from a12, as shown below:
select a12.YEAR_ID YEAR_ID12,
a13.YEAR_ID YEAR_ID13,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from YR_CATEGORY_SLS a11
join LU_YEAR a12
on (a11.YEAR_ID = a12.YEAR_ID)
join LU_YEAR a13
on (a11.YEAR_ID = a13.PREV_YEAR_ID)
group by a12.YEAR_ID, a13.YEAR_ID
If both conditions were applied to the same instance of the table, then the conditions would be mutually exclusive and cause the report to return no data. This SQL, however, is structurally and functionally different from the 'partial star join' SQL. The Strategy SQL Generation Engine can produce the following SQL in some circumstances, typically involving incorrect use of attribute roles in the schema. For more details on this latter scenario, consult the following Strategy Knowledge Base technical note.
KB10289 (KB5200-75x-0442): Reports with transformation metrics return no data, and incorrect SQL is generated in Strategy SQL Engine.
select a12.YEAR_ID YEAR_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from YR_CATEGORY_SLS a11
join LU_YEAR a12
on (a11.YEAR_ID = a12.YEAR_ID and
a11.YEAR_ID = a13.PREV_YEAR_ID)
group by a12.YEAR_ID
ACTION:
To eliminate the extraneous join, change the VLDB property to 'No star join.'
