Teradata SQL Reference: Advantages of OLAP Functions over Subqueries in Teradata

Significance of OLAP Features:

Execution Time of the Query is minimized to a significant level after using OLAP features.
Less complex query can be formulated by OLAP functions.

Description of the table [PRDT_RECVD_DT_TBL] used:

Table contains the 5 columns namely PRODUCT, BRAND, CATEGORY, SALES_CENTER (i.e Store), PRDT_RCVD_DATE. Consider 'Product, Brand, Category' as a MERCHANDISE.

High level Requirement Specification:

Ultimate Goal is to compute the Earliest Product Received Date. Earliest Product Received Date is the most recent date when majority of the sales center received the goods at the Merchandise level.

Code level Specification:

We need to build a table containing Merchandise with its Earliest Product Received Date from the source table which has only product received date [PRDT_RCVD_DATE].

Earliest Product Received Date is the date when particular Merchandise is received by maximum number of SALES CENTERS (read as STORES). For particular Merchandise, if the maximum number of SALES CENTERS is same for two different PRDT_RCVD_DATEs then we should take the earliest date.

For Example - If the product is received at 1000 different stores on 2 different dates say on 11/01/2010 & 15/01/2010, then the query should return 11/01/2010[earliest date amongst the two dates] as Earliest Product Received Date.

OLAP Features Used:

We have used the following OLAP features in our module to enhance the performance of the query.

The QUALIFY clause allows restriction of rows to be output in the final result. In the below query, the QUALIFY clause restricts the output to be based on product received by majority of the STORES and latest PRODUCT RECEIVED DATE and return the firstSTORE satisfying this criteria.

The PARTITION BY clause may be used in conjunction with a RANK function to change the scope of the ranking. PARTITION BY clause controls scope, i.e., rank sales within store. In the below query, the scope limits to MERCHANDISE.

The RANK () OVER ORDER BY clause is used to assign the ranking sequence.

Implementation of the above requirement with OLAP Functions:

SELECT DISTINCT A.PRODUCT, A.CATEGORY, A.BRAND, A.PRDT_RCVD_DATE AS ERLST_RCVD_DATE

FROM

(

SELECT A.PRODUCT, A.CATEGORY, A.BRAND, PRDT_RCVD_DATE,

COUNT(*) OVER(PARTITION BY A.PRODUCT, A.CATEGORY,

A.BRAND, A.PRDT_RCVD_DATE) AS

SLSCNTR_COUNT

FROM PRDT_RECVD_DT_TBL A

) A

QUALIFY RANK() OVER (PARTITION BY A.PRODUCT, A.CATEGORY, A.BRAND ORDER BY SLSCNTR_COUNT DESC, PRDT_RCVD_DATE ASC)=1;

Execution time: 9 min 14 sec

Implementation of the above requirement without OLAP Functions:

SELECT T1.PRODUCT AS PRODUCT, T1.CATEGORY AS CATEGORY, T1.BRAND AS BRAND, MIN (T1.PRDT_RCVD_DATE) AS ERLST_RCVD_DATE

FROM

(SELECT PRODUCT, CATEGORY, BRAND, PRDT_RCVD_DATE,

COUNT (SALES_CENTER) AS SLSCNTR_COUNT

FROM PRDT_RECVD_DT_TBL

GROUP BY PRODUCT, CATEGORY, BRAND, PRDT_RCVD_DATE) T1

WHERE (T1.PRODUCT, T1.CATEGORY, T1.BRAND,

T1.SLSCNTR_COUNT) IN

(SELECT T2.PRODUCT, T2.CATEGORY, T2.BRAND,

MAX (T2.SLSCNTR_COUNT)

FROM

(SELECT PRODUCT, CATEGORY, BRAND,

PRDT_RCVD_DATE, COUNT (SALES_CENTER) AS

SLSCNTR_COUNT

FROM PRDT_RECVD_DT_TBL

GROUP BY PRODUCT, CATEGORY, BRAND,

PRDT_RCVD_DATE) T2

GROUP BY T2.PRODUCT, T2.CATEGORY, T2.BRAND)

GROUP BY T1.PRODUCT, T1.CATEGORY, T1.BRAND

Execution Time: Executed till 11 minutes and then aborted with spool space error as the query is space intensive.

Test the above query:

Create the sample table with the below CREATE statement. Load the table with the DATALOAD statements that follow & then one can test.

Table Creation:

CREATE SET TABLE PRDT_RECVD_DT_TBL, NO FALLBACK,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

PRODUCT BYTEINT NOT NULL,

SALES_CENTER SMALLINT NOT NULL,

CATEGORY INTEGER NOT NULL,

BRAND SMALLINT NOT NULL,

PRDT_RCVD_DATE DATE FORMAT 'MM/DD/YYYY'

)

PRIMARY INDEX (PRODUCT, SALES_CENTER, CATEGORY, BRAND);

Insert Query to load the tables:

Table: PRDT_RECVD_DT_TBL