Implementation of OLAP Queries

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. 

 

Post a Comment

Previous Post Next Post