KB11055: What is the VLDB property "Compute Non-Agg before/after OLAP functions (e.g. Rank) calculated in Analytical Engine"?

• Strategy

What is the VLDB property "Compute Non-Agg before/after OLAP functions (e.g. Rank) calculated in Analytical Engine"

The following example demonstrates the use of the VLDB property "Compute Non-Agg before/after OLAP functions (e.g. Rank) calculated in Analytical Engine" found under the 'Metrics' folder in the list of VLDB properties. This property exists at the Database instance, Report and Metric levels:

In contrast to what the name may imply, the VLDB setting is not related to the non-aggregating functions (such as Log(), Max(), Min()) but instead to non-aggregating metrics. A non-aggregating metric is a metric for which it does make sense to aggregate the values for calculation; Inventory metrics that calculate the beginning or ending values of inventory over a period of time are the most prominent examples. Non-aggregatable metrics are defined in Strategy using the metric dimensionality Grouping = Beginning or Ending (fact or lookup) settings.

Consider the following reporting example where the report is defined by slightly altering the existing objects within the Strategy Tutorial project:

A new metric, Rank(BOH-MTD), is created by applying the Rank() function on the existing 'BOH-MTD' (Beginning On Hand-Month To Date) metric within the project. The following is the definition for this new metric. Note that the Sort By Value property of Rank is set to 'False.' The original BOH-MTD metric definition is depicted along with this metric as follows:

This report definition would result in the following SQL and the following intermediate and final result sets:

Certified: MD, WH, ST
SQLResult Sets

select distinct a11.PBTNAME PBTNAME
from PMT_INVENTORY a11
where a11.QUARTER_ID in (20022)

select a11.MONTH_ID MONTH_ID,
a13.CATEGORY_ID CATEGORY_ID,
sum(a11.EOH_QTY) WJXBFS1,
sum(a11.EOH_QTY) WJXBFS2
from INVENTORY_Q2_2002 a11,
LU_ITEM a12,
LU_SUBCATEG a13,
LU_MONTH a14
where a11.ITEM_ID = a12.ITEM_ID and
a12.SUBCAT_ID = a13.SUBCAT_ID and
a11.MONTH_ID = a14.MONTH_ID
and a14.QUARTER_ID in (20022)
group by a11.MONTH_ID,
a13.CATEGORY_ID

create table ZZTQ6003SWQNB000(
MONTH_ID NUMBER(10),
CATEGORY_ID NUMBER(3),
WJXBFS1 NUMBER,
WJXBFS2 NUMBER)

insert into ZZTQ6003SWQNB000 values (200204, 1, 1273, .166666666666667)

create table ZZTQ6003SWQMB001 nologging as
select pa2.CATEGORY_ID CATEGORY_ID,
min(pa2.MONTH_ID) WJXBFS1
from ZZTQ6003SWQNB000 pa2
group by pa2.CATEGORY_ID

select distinct pa2.CATEGORY_ID CATEGORY_ID,
a11.CATEGORY_DESC CATEGORY_DESC,
pa2.WJXBFS1 WJXBFS1,
pa2.WJXBFS2 WJXBFS2
from ZZTQ6003SWQNB000 pa2,
ZZTQ6003SWQMB001 pa3,
LU_CATEGORY a11
where pa2.CATEGORY_ID = pa3.CATEGORY_ID and
pa2.MONTH_ID = pa3.WJXBFS1 and
pa2.CATEGORY_ID = a11.CATEGORY_ID

Looking at the intermediate result set, one can see that the percent rank is calculated across all 12 entries. Thus, the value 1273 is the second largest value amongst the 12 entries making its percent rank 2/12*100=16.67%. Similarly, the value 11809 is the 8th largest value amongst the 12 entries making its percent rank 8/12*100=66.67%. Also note that the SQL qualifies on the first month of the quarter due to the dimensionality and the beginning-on-hand nature of the metric.

If the VLDB setting is changed to 'Calculate non-aggregation after OLAP functions/Rank,' the result set(s) change as follows:

SQLResult Set

select distinct a11.PBTNAME PBTNAME
from PMT_INVENTORY a11
where a11.QUARTER_ID in (20022)

create table ZZTQ6003SMVPO000 nologging as
select a11.MONTH_ID MONTH_ID,
a13.CATEGORY_ID CATEGORY_ID,
sum(a11.EOH_QTY) WJXBFS1,
sum(a11.EOH_QTY) WJXBFS2
from INVENTORY_Q2_2002 a11,
LU_ITEM a12,
LU_SUBCATEG a13,
LU_MONTH a14
where a11.ITEM_ID = a12.ITEM_ID and
a12.SUBCAT_ID = a13.SUBCAT_ID and
a11.MONTH_ID = a14.MONTH_ID
and a14.QUARTER_ID in (20022)
group by a11.MONTH_ID,
a13.CATEGORY_ID

create table ZZTQ6003SMVMB001 nologging as
select pa1.CATEGORY_ID CATEGORY_ID,
min(pa1.MONTH_ID) WJXBFS1
from ZZTQ6003SMVPO000 pa1
group by pa1.CATEGORY_ID

select distinct pa1.CATEGORY_ID CATEGORY_ID,
a11.CATEGORY_DESC CATEGORY_DESC,
pa1.WJXBFS1 WJXBFS1,
pa1.WJXBFS2 WJXBFS2
from ZZTQ6003SMVPO000 pa1,
ZZTQ6003SMVMB001 pa2,
LU_CATEGORY a11
where pa1.CATEGORY_ID = pa2.CATEGORY_ID and
pa1.MONTH_ID = pa2.WJXBFS1 and
pa1.CATEGORY_ID = a11.CATEGORY_ID

With the altered VLDB setting, the percent rank is calculated after the BOH-MTD metric is calculated. Thus, the values are ranked amongst the 4 entries rather than the 12 values. The value 1273 is the lowest amongst the 4 values making its percent rank 1/4*100=25%. Although it is not reflected in the SQL, the metric BOH-MTD is included in the report to display the corresponding values.

Comment

0 comments

Details

Knowledge Article

Published:

May 16, 2017

Last Updated:

May 16, 2017