Experiment No-11
Title :- Implementation of OLAP Queries.
Aim : Demonstrate all OLAP operations and cube
operator in OLAP.
Objective : To understand the concept of aggregation
& summarization concept.
Theory:
OLAP (online analytic processing):
1.OLAP applications are includes complex queries,
usually involves analysis of data.
2.OLAP support queries that needed to support DSS
(Decision Support System).
3.OLAP performed on data warehouses or data marts.
OLAP types:
1. Multidimensional OLAP (MOLAP): It uses
multidimensional arrays to store a dataset. In multidimensional data model the
focus is on collection of numeric measures. Each measure depends on a set of
dimension. For each dimension, the set of associated values can be structured
as a hierarchy.
2. Relational OLAP (ROLAP): In this OLAP, data is
stored in relational database.
3. Hybrid OLAP (HOLAP): It combines best features
of MOLAP and ROLAP.
OLAP Operations:
1. Roll-up
2. Drill- down
3. Slice
4. Dice
5. Pivoting
OLAP includes multidimensional aggregation queries
for example:
1. Find the total sales.
2. Find total sales for each city.
3. Find total sales for each sate.
CUBE operator:
The GROUP BY clause with the CUBE keyword is
equivalent to a collection of GROUP
BY statements, with one GROUP BY statement for each
subset of k dimensions.
Syntax: select column list, aggregate
function(column name)
From relation
[Where condition]
Group by CUBE(column list);
ROLLUP(dimension reduction aggregation):
1. It allows user to ask query that move up an
aggregation hierarchy.
2. Each ROLLUP corresponds to a single SQL query
with grouping.
3. In general, given a measure with k associated
dimensions, we can roll up on any
subset of these k dimensions, so there are total of
2k such SQL queries.
Syntax: select column list, aggregate
function(column name)
From relation
[Where condition]
Group by ROLLUP(column list);
Procedure:-
1) Create relations with appropriate schema
2) Insert at least 10 tuples in it.
3) Execute the CUBE & ROLLUP operator.
4) Note down the output.
5) Analyze the output.
1. create table locations(locid varchar(5),city
varchar(10),state varchar(5),country varchar(10));
2. create table sales(pid varchar(5),timeid
varchar(5),locid varchar(5),sales varchar(5));
3. create table products(pid varchar(5), pname
varchar(10), category varchar(15),price varchar(5));
4. create table time(timeid varchar(5),year
varchar(5));
mysql> select *from locations;
+-------+---------+-------+---------+
| locid | city | state | country |
+-------+---------+-------+---------+
| 1 | madison | W1 | USA |
| 2 | fresno | CA | USA |
| 5 | Chennai | TN | India |
+-------+---------+-------+---------+
3 rows in set (0.00 sec)
insert into sales values(12,3,1,50);
insert into sales values(11,1,2,35);
insert into sales values(11,2,2,22);
insert into sales values(11,3,2,10);
insert into sales values(12,1,2,26);
insert into sales values(12,2,2,45);
insert into sales values(12,3,2,20);
insert into sales values(13,1,2,20);
insert into sales values(13,2,2,40);
insert into sales values(13,3,2,5);
mysql> select *from sales;
+------+--------+-------+-------+
| pid | timeid | locid | sales |
+------+--------+-------+-------+
| 11 | 1 | 1 | 25 |
| 11 | 2 | 1 | 8 |
| 11 | 3 | 1 | 15 |
| 12 | 1 | 1 | 30 |
| 12 | 2 | 1 | 20 |
| 13 | 1 | 1 | 8 |
| 13 | 2 | 1 | 10 |
| 13 | 3 | 1 | 10 |
mysql> select *from products;
+------+-----------+------------+-------+
| pid | pname | category | price |
+------+-----------+------------+-------+
| 11 | Lee Jeans | Apparel1 | 25 |
| 12 | Zord | Toys | 18 |
| 13 | Biro Pen | Stationary | 2 |
+------+-----------+------------+-------+
3 rows in set (0.00 sec)
mysql> select *from time;
+--------+------+
| timeid | year |
+--------+------+
| 1 | 2016 |
| 2 | 2017 |
| 3 | 2018 |
+--------+------+
1. SELECT T.year,L.state,SUM(S.sales)
FROM sales S, time T, locations L
WHERE S.timeid=T.timeid and S.locid=L.locid
GROUP BY T.year, L.state;
+------+-------+--------------+
| year | state | SUM(S.sales) |
+------+-------+--------------+
| 2016 | CA | 81 |
| 2016 | W1 | 63 |
| 2017 | CA | 107 |
| 2017 | W1 | 38 |
| 2018 | CA | 35 |
| 2018 | W1 | 75 |
2. SELECT T.year,SUM(S.sales)
FROM sales S, time T
WHERE S.timeid=T.timeid
GROUP BY T.year;
+------+--------------+
| year | SUM(S.sales) |
+------+--------------+
| 2016 | 144 |
| 2017 | 145 |
| 2018 | 110 |
+------+--------------+
3. SELECT L.state,SUM(S.sales)
FROM sales S, locations L
WHERE S.locid=L.locid
GROUP BY L.state;
+-------+--------------+
| state | SUM(S.sales) |
+-------+--------------+
| CA | 223 |
| W1 | 176 |
+-------+--------------+
4. SELECT T.year,L.state,SUM(S.sales)
FROM sales S, time T, locations L
WHERE S.timeid=T.timeid and S.locid=L.locid
GROUP BY CUBE (T.year, L.state);
5. SELECT T.year,L.state,SUM(S.sales)
FROM sales S, time T, locations L
WHERE S.timeid=T.timeid and S.locid=L.locid
GROUP BY ROLLUP (T.year, L.state);
+------+-------+--------------+
| year | state | SUM(S.sales) |
+------+-------+--------------+
| 2016 | CA | 81 |
| 2016 | W1 | 63 |
| 2016 | NULL | 144 |
| 2017 | CA | 107 |
| 2017 | W1 | 38 |
| 2017 | NULL | 145 |
| 2018 | CA | 35 |
| 2018 | W1 | 75 |
| 2018 | NULL | 110 |
| NULL | NULL | 399 |
+------+-------+--------------+
10 rows in set (0.00 sec)
Conclusion :- OLAP tool can be used to take
decision in an organization.