The percentile function is an eggregate function used in Strategy. Prior to Strategy 2020, it was calculated in the Analytical Engine, which means it fetches raw data from memory.
In the following example, the percentile function fetches all raw data from the MNTH_CATEGORY_SLS fact table in the data source. Then, it calculates the metrics defined with the percentile function in the Analytical Engine.
M01: Percentile(Cost, 0.3) {~}
Report: Quarter, Month, M01
select "a11"."MONTH_ID" "MONTH_ID",
"a11"."CATEGORY_ID" "CATEGORY_ID",
"a11"."TOT_COST" "WJXBFS1"
from "MNTH_CATEGORY_SLS" "a11"
create table ZZMD01(
"MONTH_ID" NUMBER(10),
"WJXBFS1" FLOAT(24))
[Analytical SQL calculated by the Analytical Engine:
select MONTH_ID,
Percentile("WJXBFS1", 0.3)
from [previous pass]
]
insert into ZZMD01 values ([Analytical Engine Results: MONTH_ID, Percentile("WJXBFS1", 0.3)])
In Strategy 2021, the calculation of the percentile function has been pushed to the data source side to improve performance.
The top ten gateways, including Oracle and SQL Server, support percentile functions in SQL statements. For the supported gateways listed at the end of this article, calculating the percentile function at the data source side can improve performance. This is because the amount of data that needs to be transferred can be substantially reduced, depending on the "cardinality" or number of rows in the fact table versus the aggregate table.
Different gateways support different SQL syntax to calculate the percentile function.
PARTITION by
select distinct [a11].[MONTH_ID] [MONTH_ID], PERCENTILE_CONT (0.3) WITHIN GROUP ( ORDER BY [a11].[TOT_COST] asc) OVER (PARTITION BY [a11].[MONTH_ID]) [WJXBFS1] into ##ZZT8N58IE65MD001 from [MNTH_CATEGORY_SLS] [a11]
select "a11"."MONTH_ID" "MONTH_ID", PERCENTILE_CONT (0.3) WITHIN GROUP ( ORDER BY "a11"."TOT_COST" asc) "WJXBFS1" from "MNTH_CATEGORY_SLS" "a11" group by "a11"."MONTH_ID"
For the following situation, we still keep the old logic to calculate percentile functions in the Analytic Engine due to production limitations, even if percentile functions are supported on the data source side:
Compound metrics defined with more than two metrics. At least one metric is using the percentile function and the other metric is using other functions.
Example:
(Sum(Cost){~+} - Percentile(Cost, 0.3) {~} )Your metadata must be upgraded to Strategy 2021 or above to populate the new SQL pattern. This requires switching the Data Engine Version to 2021 or above to push down the percentile function calculation to the data source side.