SYMPTOM:
In Strategy Developer 9.x-10.x, a report contains a consolidation that has multiple derived elements, while the derived elements have different attributes. Even the attributes are mapped to the same column, there are still same passes generated for the each derived element.
Reproducing steps in Tutorial:
1. Duplicate the attribute ‘Year’ to “Year1”, notice the ID form is mapped to YEAR_ID column of LU_YEAR table as shown blow:
2. Create a consolidation called CD1 with two elements: {Year=2010} and {Year1=2011} as shown below:
3. Create a report called R1 with consolidation CD1 in row and metric ‘Revenue’ in column as shown below:
4. Execute the report, notice in the SQL view same passes Pass0 and Pass1 are calculating the consolidation derived elements separately as shown below:
Pass0 - Execution Duration: 0:00:00.04
Data Fetch Duration from Datasource(s): 0:00:00.00
Total Data Fetch Duration: 0:00:00.00
Other Processing Duration: 0:00:00.03
Rows selected: 1
select a12. AS YEAR_ID,
sum(a11.) AS Revenue
from a11,
a12
where a11. = a12.
and a12. = 2010
group by a12.
Pass1 - Execution Duration: 0:00:00.00
Data Fetch Duration from Datasource(s): 0:00:00.00
Total Data Fetch Duration: 0:00:00.00
Other Processing Duration: 0:00:00.00
Rows selected: 0
select a12. AS YEAR_ID,
sum(a11.) AS Revenue
from a11,
a12
where a11. = a12.
and a12. = 2011
group by a12.
CAUSE:
This is working by design. Following current SQL Optimization logic, only SQL passes with same Group-by attributes may be combined. Although the SQL statements have same group-by columns, in the SQL Engine backend they belong to different attributes, thus SQL Engine perceives them as not combinable.
WORKAROUND:
Use one attribute instead of multiple attributes to define the consolidation elements.
KB41450
