Deal of the Day

Tuesday 5 July 2011

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

No comments:

Post a Comment