The VLDB property 'Rank Method if DB Ranking Not Used' can be reached under the 'Query Optimizations' folder, as shown below.
Database platforms vary in their support for analytical functions such as ranking. When a database platform does not natively support a necessary function, the Strategy SQL Generation Engine and/or Analytical Engine employ alternate techniques to obtain the desired result.
Ranking in particular can be performed in three different ways, depending on the level of database support:
Strategy SQL Generation Engine performs two tests to determine which method to use:
Note from this that the Rank Method VLDB property is effectively a fallback position. It takes effect only if the ranking calculation is not required to be performed in the Analytical Engine and the database does not support the calculation.
It also applies only to ranking qualifications in filters. If the ranking needs to be displayed on the template as a metric value and the database does not support ranking, the Analytical Engine will supply the ranking values.
What are the differences between the three methods?
The following report definition will be used to illustrate the three ways a metric ranking qualification can be rendered into SQL:
For this report, a user would expect to see the following calculations:
Method 1: Native database ranking
The ranking calculation will be generated as an expression in the SELECT clause of the SQL statement. The syntax is specific to the database platform. In Oracle, a ranking calculation will appear as follows:
-- calculate metric ranks
create table ZZAM00 nologging as
select a11.MONTH_ID MONTH_ID,
rank () over( order by sum(a11.TOT_DOLLAR_SALES) desc nulls last) WJXBFS1
from MNTH_CATEGORY_SLS a11
group by a11.MONTH_ID
-- collect attribute IDs meeting the qualification
create table ZZMQ01 nologging as
select pa1.MONTH_ID MONTH_ID
from ZZAM00 pa1
where (pa1.WJXBFS1 <= 5.0)
-- calculate template metric values for those IDs
select a11.MONTH_ID MONTH_ID,
a12.MONTH_DESC MONTH_DESC,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from MNTH_CATEGORY_SLS a11,
ZZMQ01 pa2,
LU_MONTH a12
where a11.MONTH_ID = pa2.MONTH_ID and
a11.MONTH_ID = a12.MONTH_ID
group by a11.MONTH_ID,
a12.MONTH_DESC
Method 2: Analytical Engine ranking
In this technique, the metric values to be ranked are selected into Strategy Intelligence Server, where the Analytical Engine performs the rank. Once the Analytical Engine has determined which attribute IDs meet the qualification, an intermediate table is created and one row per attribute ID is inserted into that table.
-- calculate metric values to be ranked
select a11.MONTH_ID MONTH_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from MNTH_CATEGORY_SLS a11
group by a11.MONTH_ID
create table #ZZTPF0100IVMQ000(
MONTH_ID INTEGER)
-- calculate ranks in the Intelligence Server
-- insert attribute IDs; only one insert is shown in the report SQL, although many will be executed
insert into #ZZTPF0100IVMQ000 values (200204)
-- calculate template metric values for those IDs
select a11.MONTH_ID MONTH_ID,
max(a12.MONTH_DESC) MONTH_DESC,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from MNTH_CATEGORY_SLS a11
join #ZZTPF0100IVMQ000 pa1
on (a11.MONTH_ID = pa1.MONTH_ID)
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
group by a11.MONTH_ID
Method 3: ODBC ranking
ODBC ranking is unique in that it actually does not need to calculate the ranks explicitly. Instead, it orders the ranked metric values as needed to determine a cutoff value. It then uses ODBC function calls to advance the query's cursor exactly as many values as are needed to satisfy the condition. Since the ranking qualification in this report is to retrieve the top five values, it orders the metric values in descending order (top values first), then obtains the fifth value. There is no need to fetch any values after the fifth.
This value is then embedded in a SQL statement that captures the attribute IDs for metric values greater or less than the target value. The comparison operator depends on the operator chosen in the ranking qualification.
-- calculate metric values to be ranked
select a11.MONTH_ID MONTH_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZTPF0100J1MR000
from MNTH_CATEGORY_SLS a11
group by a11.MONTH_ID
-- select the metric values in descending order; only as many rows as are needed to define the qualification will be retrieved
select pa1.WJXBFS1 WJXBFS1
from #ZZTPF0100J1MR000 pa1
order by 1 desc
-- select attribute IDs for the metric values greater than the cutoff value
select pa1.MONTH_ID MONTH_ID
into #ZZTPF0100J1MQ001
from #ZZTPF0100J1MR000 pa1
where (pa1.WJXBFS1 >= 901617.99999999)
-- calculate template metric values for those IDs
select a11.MONTH_ID MONTH_ID,
max(a12.MONTH_DESC) MONTH_DESC,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from MNTH_CATEGORY_SLS a11
join #ZZTPF0100J1MQ001 pa2
on (a11.MONTH_ID = pa2.MONTH_ID)
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
group by a11.MONTH_ID
ODBC ranking is the default ranking technique (again, when the database does not support native ranking) because it is more efficient for large datasets. Analytical Engine ranking must fetch all the metric values along with the associated attribute IDs into Strategy Intelligence Server memory before the ranks can be calculated. If the intermediate result set comprises tens of thousands of rows, memory consumption could be significant, especially if many such calculations are being performed concurrently. ODBC ranking does not require significant memory allocation.
