By default, ID and DESC forms of an attribute are retrieved in a single SQL pass with Strategy SQL Engine. For performance reasons, it might be desirable to separate ID and DESC forms to be retrieved in different SQL passes. In Strategy SQL Engine, it can be done by using the 'Additional Final Pass Option' in the VLDB properties.
The ID and the DESC forms can be separated in different SQL passes by using the 'Additional Final Pass Option' VLDB property. There are two options available of which the first option is the default one. The following screenshot is describing the 'Additional Final Pass Option' VLDB property and it's location. The behaviour of the options is described below the screenshot.
Option 1 (default):
By default, 'Additional Final Pass Option' VLDB property is set to 'Final pass CAN do aggregation and join lookup tables in one pass'. This option is retrieving both, ID and DESC forms in the SELECT clause and join(s) are generated in the FROM clause. This is because the DESC form has to be joined to a higher level of Attribute through the lookup tables.
Option 2:
When 'Additional Final Pass Option' VLDB property is set to 'One additional final pass only to join lookup tables' the ID form is retrieved in the first SQL pass. This solution is calculating the metric(s) in the first pass and the ID form is retrieved by joining the lookup table ID with the Fact table ID. In some cases the ID form is already present in the Fact table and no joins are needed. The IDs retrieved in the first pass are saved to a temp table. In the second pass the temp table IDs are joined with the lookup table IDs in order to retrieve the DESC forms from the lookup tables.
NOTES:
VLDB property 'Additional Final Pass Option' can be enabled under Query Optimizations category in Database Instance level and Report level.
For more information about 'Additional Final Pass Option' VLDB property, refer to the following Strategy Knowledge Base technical note and product manual:
