Additional Final Pass Option

Additional Final Pass Option is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Additional Final Pass Option determines whether the Engine calculates an aggregation function and a join in a single pass or in separate passes in the SQL.

Levels at Which You Can Set This

Report, template, and database instance

It is recommended that you use this property on reports. You must update the metadata to see the property populated in the metadata.

Example

The following SQL example was created using SQL Server metadata and warehouse.

Consider the following structure of lookup and fact tables:

LU_Emp_Mgr has 4 columns, namely: Emp_ID, Emp_Desc, Mgr_ID, and Mgr_Desc

In this structure, Emp_ID is the primary key of LU_Emp_Mgr table

LU_Dept has 2 columns, namely: Dept_ID and Dept_Desc

In this structure, Dept_ID is the primary key of LU_Dept table

Fact table Emp_Dept_Salary has 3 columns, namely: Emp_ID, Dept_ID, and fact Salary

From the above warehouse structure, define the following schema objects:

Attribute Employee with 2 forms: Employee@ID (defined on column Emp_ID) and Employee@Desc (defined on column Emp_Desc)
Attribute Manager with 2 forms: Manager@ID (defined on column Mgr_ID) and Manager@Desc (defined on column Mgr_Desc)
Attribute Department with 2 forms: Department@ID (defined on column Dept_ID) and Department@Desc (defined on column Dept_Desc)
Fact Fact_Salary, which is defined on Salary column

The Manager attribute is defined as the parent of the Employee attribute via LU_Emp_Mgr table. This is a common practice in a star schema.

Create two metrics that are defined as

Salary_Dept = Sum(Fact_Salary)){~+, Department+}
Salary = Avg(Salary_Dept){~+}

In a report called Employee_Salary, put the Salary metric on a template with the Manager attribute. In this example, the Employee_Salary report generates the following SQL:

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Pass0
select a12.Mgr_Id Mgr_Id,
 a11.Dept_Id Dept_Id,
 sum(a11.Salary) WJXBFS1
into #ZZTUW0200LXMD000
from dbo.Emp_Dept_Salary a11
 join dbo.Emp_Mgr a12
 on (a11.Emp_Id = a12.Emp_Id)
group by a12.Mgr_Id,
 a11.Dept_Id 
Pass1
select pa1.Mgr_Id Mgr_Id,
 max(a11.Mgr_Desc) Mgr_Desc,
 avg(pa1.WJXBFS1) WJXBFS1
from #ZZTUW0200LXMD000 pa1
 join dbo.Emp_Mgr a11
 on (pa1.Mgr_Id = a11.Mgr_Id)
group by pa1.Mgr_Id
Pass2
drop table #ZZTUW0200LXMD000

The problem in the SQL pass above, in lines 14-17, is that the join condition and the aggregation function are in a single pass. The SQL joins the ZZTUW0200LXMD000 table to the Emp_Mgr table on column Mgr_ID, but Mgr_ID is not the primary key to the LU_Emp_Mgr table. Therefore, there are many rows on the LU_Emp_Mgr table with the same Mgr_ID. This results in a repeated data problem.

Clearly, if both the conditions, aggregation and join, do not exist on the same table, this problem does not occur.

To resolve this problem, select the option One additional final pass only to join lookup tables in the VLDB Properties Editor. With this option selected, the report, when executed, generates the following SQL:

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Pass0
select a12.Mgr_Id Mgr_Id,
 a11.Dept_Id Dept_Id,
 sum(a11.Salary) WJXBFS1
into #ZZTUW01006IMD000
from dbo.Emp_Dept_Salary a11
 join dbo.Emp_Mgr a12
 on (a11.Emp_Id = a12.Emp_Id)
group by a12.Mgr_Id,
 a11.Dept_Id 
Pass1
select pa1.Mgr_Id Mgr_Id,
 avg(pa1.WJXBFS1) WJXBFS1
into #ZZTUW01006IEA001
from #ZZTUW01006IMD000 pa1
group by pa1.Mgr_Id 
Pass2
select distinct pa2.Mgr_Id Mgr_Id,
 a11.Mgr_Desc Mgr_Desc,
 pa2.WJXBFS1 WJXBFS1
from #ZZTUW01006IEA001 pa2
 join dbo.Emp_Mgr a11
 on (pa2.Mgr_Id = a11.Mgr_Id)
Pass3
drop table #ZZTUW01006IMD000
Pass4
drop table #ZZTUW01006IEA001

In this SQL, lines 12-13 and 21-23 show that the Engine calculates the aggregation function, which is the Average function, in a separate pass and performs the join operation in another pass.