OLAP Functions in i-Series

 Hi Guys, 

One of my Linkedin friend asked me about OLAP Functions in i-Series, So just thought of writing a brief on OLAP.

OLAP functions in i-Series

 IBM gave developers an impressive set of Online Analytical Processing (OLAP) tools to aid with common ranking and numbering issues. The significance of these OLAP tools lies in the amount of work they do and the time it will save database programmers.

ROW_NUMBER

To start, the ROW_NUMBER is a huge blessing as it finally furnishes SQL with a built-in counter function. Here's a trivial example that adds a row number column to a result set:

Insert Into OrderItm

Select OrderNo,

       Row_Number() Over (Partition By OrderNo

                          Order By OrderNo,ItemNo),

       ItemNo,

       QtyOrdered,

       SalePrice

In this scenario, ROW_NUMBER will continually increment the ItemSeq field over all orders that are being imported and counter would get reset for every change in order number.

In this example, the Row_Number functionality is provided but the Partition By expression tells Row_Number to start counting all over again whenever there is a change in the OrderNo column.. As shown in the Row_Number's Order By expression, the sequence numbers will be assigned to rows in OrderNo,ItemNo order

Rank and Dense_Rank

Other functions available are RANK and Dense_Rank

Suppose that you want a list of student marks along with their ranking. The following query generates the ranking number for you:

 SELECT Stud_ID, Marks, 

        RANK() OVER(ORDER BY Mark DESC),

        DENSE_RANK() OVER(ORDER BY Mark DESC),

        ROW_NUMBER() OVER(ORDER BY Mark DESC)  

 FROM Stud_Mark

Stud_ID	Marks	RANK	DENSE_RANK	ROW_NUMBER
000010	52,750.00	1	1	1
000110	46,500.00	2	2	2
200010	46,500.00	2	2	3
000020	41,250.00	4	3	4
000050	40,175.00	5	4	5
000030	38,250.00	6	5	6
000070	36,170.00	7	6	7
000060	32,250.00	8	7	8
000220	29,840.00	9	8	9
200220	29,840.00	9	8	10

Example showing the more aggregate function

The OLAP functions introduced in DB2 V7 introduce a new class of functions. They're like aggregate functions, because the calculation is performed on values from multiple rows within a set to compute the result.

select city, Carmake, Numb 

sum(Numb) over (partition by City) as citysum,

avg(Numb) over (partition by City) as avgnum,

count(*) over (partition by City) as citycount,

max(Numb) over (partition by City) as maxnum 

from CarSale

City	CARMAKE	NUMB	CITYSUM	AVGNUM	CITYCOUNT	MAXNUM
1	Suzuki	50000	383000	63833	6	78000
1	Honda	75000	383000	63833	6	78000
1	Tata	52000	383000	63833	6	78000
1	Nissan	78000	383000	63833	6	78000
1	GM	75000	383000	63833	6	78000
1	Fiat	53000	383000	63833	6	78000
2	Suzuki	51000	102000	51000	2	51000
2	Honda	51000	102000	51000	2	51000
3	Tata	79000	209000	69666	3	79000
3	Nissan	55000	209000	69666	3	79000
3	GM	75000	209000	69666	3	79000
4	Suzuki	80000	164000	82000	2	84000
4	Tata	84000	164000	82000	2	84000

Notice that the query contains no GROUP BY clause. Instead, the OVER clause is used to partition the data so that the sum function is computed over rows in the same City, and the sum of all the Number of Cars in each City is returned for each row within the City. Without such functions we use to have JOINS but now the OLAP functions provide this much simpler formulation. Most existent aggregate functions (i.e. AVG, MIN, MAX, Sum, etc...) work with the OVER clause.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning