OLAP functions in Teradata

As of 2020, the organizations are significantly looking for business development. Business development and improvements are vital during this time, and this purely depends on the data and its reports, followed by analytics. OLAP, Online Analytical Processing will provide the results based on the database queries. The results will be delivered based on the queries and the functions you use based on your requirements. OLAP is a broader part of business intelligence, and it is essential to understand the concept of OLAP and its functions. In this blog, you will have a clear understanding of OLAP functions in Teradata

What are Teradata OLAP functions?

OLAP, Online Analytical Processing help gain insights into the data coming from different means or sources. OLAP helps in performing the execution of the ad-hoc and analytical queries that could be easy and complex. OLAP provides us with the analysis of the data and provides the recommendations and ideas that would help develop the business.

OLAP's functionality is usually performed by using SQL programming language by making use of the extensions available to SQL statements and the functions. Many OLAP functions perform a specific functionality on the available data, retrieving the results accordingly as per the user query. We need to utilize the functions within the SQL statements, which will then return the results as needed based on the query. 

OLAP functions are the functions that are similar to the aggregate functions with a slight difference. With respect to the general aggregate function, the output will be only one value, which means the only value will be returned. When the OLAP functions are used to evaluate the data, the OLAP function will help us with the individual rows and the aggregates. The OLAP functions will provide or display the results of the data values in the most precise format and will give accurate results. The OLAP functions will be performed on all the tables or the views. We have a few sets of OLAP functions that are most commonly used in business-oriented organizations. Let us have a quick review of them.

Want to become a certified Teradata Professional? visit here to get the Teradata Online Training from HKR Trainings

Syntax of an OLAP function:

As OLAP functions are operated using SQL language and statements, this would make us understand that the OLAP functions would usually run based on the rows in the table. The INSERT/SELECT is used to populate the tables. Below is the general syntax of an OLAP function.

OVER

([PARTITION BY] [ORDER BY columnname] [ROWS BETWEEN 

UNBOUNDED PRECEDING AND UNBOUND FOLLOWING)

In the above syntax, the aggregation function could be SUM, COUNT, MIN, MAX, and AVG.

Let us get a clear understanding with an example using some data available.

Employee_ID Gross_Salary Deductions Net_pay
1001
40000
4000 36000
1002 80000 6000 74000
1003 90000 7000 83000
1004 75000 5000 70000

With the above data, we can retrieve the data by writing the OLAP function in the below format:

SELECT
Employee_ID, Net_pay,
SUM(Net_pay) OVER(ORDER BY Emp_Id ROWS
UNBOUNDED PRECEDING) as TotalSalary
FROM Salary;

On the execution of the above query, the output will be returned as follows.

Employee_ID Net_pay TotalSalary
1001 36000 36000
1002 74000 110000
1003 83000 193000
1004 70000 263000

Teradata OLAP Functions:

As we have the idea of the syntax and its working, let us understand about the OLAP Functions in Teradata.

QUANTILE Function: 

A QUANTILE Function is one among the OLAP functions that is specifically used to divide the rows into different partitions of the same number of rows in each partition. Most of the business make use of the QUANTILE Function. By default, the QUANTILE column and QUANTILE value itself will be the output represented in the ascending order. In some scenarios, the ORDER BY clause is used to reorder the output for display purposes. 

Syntax of QUANTILE function:
SELECT QUANTILE (, , [DESC/ASC]
FROM
[QUALIFY QUANTILE () {< I > I = I <= I >=} ];

[OR]

QUANTILE ( quantile_constant, sortlist)
Quantile_constant : it is the constant used to define the number of quantile partitions
Sortlist: it is the column reference used for the partition and to sort the output.

Example to represent QUANTILE Function:

Employee_id Salary
1 10000
2 20000
3 30000
4 40000
5 50000

The syntax used to represent the QUANTILE function for the above data is:

SELECT salary, QUANTILE(100, salary DESC) FROM EDW_RESTORE_TABLES.TESTX

Salary Quantile(100,salary)

10000 0
20000 20
30000 40
40000 60
50000 80                                                                                                                                                                                                                                                               
                                                                                                                         

RANK Function:

Rank Function in Teradata will help in the ordering of the records based on the columns provided. The RANK function also helps in filtering the number of records returned based on the rank. The RANK function will provide the flexibility to evaluate each column and compare them based on the highest or lowest order. By defaut, the order will be in the descending order. 

Teradata Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

Syntax of Rank function:

RANK() OVER

([PARTITION BY columnlist ] [ORDER BY columnlist ] [DESC I ASC]

Example of RANK function: 

Let us consider the below data

EmployeeID FirstName LastName JoinedDate DepartmentID DateofBirth
1001 Mike Johnson 3/7/2005 1 1/5/1980
1002 Peter williams 4/25/2007 2 3/5/1983
1003 Alex Paul 3/21/2007 2 4/1/1983
1004 Robert James 2/1/2008 3 11/6/1984

If you want to Explore more about Teradata? then read our updated article - Teradata Tutorial

The syntax for the RANK function would be:

SELECT EmployeeID, JoinedDate, RANK()

OVER (ORDER by JoinedDate) as Senority

FROM Employee;

The output will be represented as:

EmployeeID JoinedDate Seniority
1001 3/7/2005 1
1003 3/21/2007 2
1002 4/25/2007 3
1004 2/1/2008 4

Subscribe to our youtube channel to get new updates..!

CSUM

CSUM is the OLAP function in Teradata is used to compute a running or cumulative total of the column’s value. It helps in returning the cumulative value.

Syntax of CSUM:

CSUM (columnname, sortlist)

Example for CSUM:

The syntax would be : SELECT salesdate, sales, CSUM(sales, salesdate)FROM daily_sales;

 The output for the above example would be represented by considering the sales date, sales and csum.

salesdate sales Csum
98/01/01 150.00 150.00
98/01/02 200.00 350.00
98/01/03 250.00 600.00
98/01/05 350.00 950.00
98/01/10 550.00 1500.00
98/01/21 150.00 1650.00
98/01/25 200.00 1850.00
98/01/31 100.00 1950.00
98/02/01 150.00 2100.00
98/02/03 250.00 2350.00
98/02/06 350.00 2700.00
98/02/17 550.00 3250.00
98/02/20 450.00 3700.00
98/02/27 350.00 4050.00

                                                                                                                                                                                                            

MAVG

MAVG refers to Moving Average. MAVG is one of the OLAP function which helps in moving average on a column. The number of rows used for aggregation operation is called as query width.

Syntax for MAVG:

MAVG(colnam, n, sortlist)

Colname: It specifies the column on which the moving average is computed.

N: Refers to the number of rows

Sortlist: It refers to the column which determine the sequencing of the rows.

Teradata Training

Weekday / Weekend Batches

Example of MAVG:

 SELECT EMPLOYEEID, SALARY, MAVG (SALARY, 2, EMPLOYEEID) FROM

EDW_RESTORE_TABLES.TESTY

employeeid      Salary          MAvg(Salary,2,employeeid)

         1              10000         10000.00

         2              10000         10000.00 avg of current row and prev row

         2              20000          15000.00

         4              20000          20000.00

         5              30000          25000.00

         6              10000          20000.00

         7              10000          10000.00

         8              10000          10000.00

MSUM

MSUM is one of the OLAP function in Teradata which helps in calculation of the Moving sum on a column.

Syntax of MSUM: MSUM (colname, n, sortlist)

Colname: It specifies the column on which the moving sum is computed.

N: Refers to the number of rows

Sortlist: It refers to the column which determines the sequencing of the rows.

Example of MSUM:

SELECT salesdate, itemid, sales, MSUM(sales, 3, salesdate)

WHERE itemid = 10

FROM daily_sales;

The output will be represented as follows:

salesdate   itemid      sales               MSum

98/01/01    10  150.00    150.00

98/01/02    10 200.00       350.00   Sum of 2 rows

98/01/03    10 250.00             600.00 Sum of 3 rows starting row 1

98/01/05    10    350.00              800.00 Sum of 3 rows starting row 2

98/01/10     10 550.00           1150.00 Sum of 3 rows starting row 3

98/01/21    10 150.00             1050.00

98/01/25    10    200.00              900.00   Sum of 3 rows

98/01/31    10    100.00              450.00

98/02/01    10    150.00              450.00

98/02/03    10   250.00              500.00

98/02/06    10    350.00            750.00

98/02/17     10 550.00         1150.00

98/02/20     10 450.00         1350.00

98/02/27    10 350.00         1350.00

Frequently Asked Teradata Interview Questions & Answers For Beginners

MDIFF:

MDIFF is the OLAP function in Teradata that helps in calculating the moving difference of a specified column based on the defined query width. The width (n) will help in determining how many rows back should the count be done.

Syntax of MDIFF: 

MDIFF (colname, n , sortlist)

Colname: It specifies the column on which the moving difference is computed.

N: Refers to the number of rows

Sortlist: It refers to the column which determines the sequencing of the rows.

Example of MDIFF:

SELECT salesdate, itemid, sales, MDIFF(sales, 3, salesdate)

FROM daily_sales;

The Output will be represented as:

salesdate   itemid    sales MDiff

98/01/01    10 150.00   ? Null because there is no value 3 rows above

98/01/02    10     200.00   ? Null because there is no value 3 rows above

98/01/03    10 250.00   ? Null because there is no value 3 rows above

98/01/05    10 350.00   200.00 Difference this row and row 1

98/01/10    10 550.00   350.00 Difference this row and row 2

98/01/21    10 150.00   -100.00

98/01/25    10 200.00   -150.00

98/01/31    10     100.00   -450.00

98/02/01    10 150.00       .00

98/02/03    10 250.00     50.00  Difference of 2 rows

98/02/06    10 350.00   250.00

98/02/17    10 550.00   400.00

98/02/20    10 450.00   200.00

98/02/27    10 350.00       .00

Conclusion:

The OLAP functions in Teradata will help the organizations perform the analytics and understand the trends going through the daily basis. Hence, learning and understanding the OLAP functions in the current business standpoint. I hope this tutorial is helpful and makes you understand the concept of utilizing the functions in the daily basis scenarios that come up. I would recommend you undergo the training and certification of Teradata, which would help you gain in-depth knowledge in the business intelligence world. This would also help you in finding the best opportunities as a part of career improvements and developments. Learn more and gain more knowledge, which will help you attain the right position in this fast technological world.

Categories

SAP

Request for more information

Webinar

Register free live webinar on Linux Shell Scripting

30th October | 07:00 pm

0 Registered

Sachin
Sachin
Networking, OS, Salesforce & Sap
Am sachin I have worked with hkr trainings for more than 4 years .and our methodologies had been a practical suit for student culture and professionals. And I have trained more than 30 batches over the last 8 months. And I have great experience working with hkr trainings. Contact me with my LinkedIn and Twitter.

WhatsApp
To Top