KEMBAR78
Olap Operations | PDF | Portable Computers | Personal Computing
Scribd
Upload
6 views5 pages

Olap Operations

Uploaded by

damafa7880
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views5 pages

Olap Operations

Uploaded by

damafa7880
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

mysql> show tables ;

+ +
| Tables_in_vikas_dwm |
+ +
| fact |
| location |
| locationprofit |
| product |
| productprofit |
| supplier |
| time |
+ +
7 rows in set (0.02 sec)

mysql> select * from product ;


+ + + + + +
| id | type | category | cost | quantity |
+ + + + + +
| 1 | Earphone | Electronics | 1000.00 | 50 |
| 2 | Speakers | Electronics | 600.00 | 200 |
| 3 | Headphones | Electronics | 300.00 | 100 |
| 4 | wired | Accessories | 150.00 | 75 |
+ + + + + +

mysql> select * from supplier ;


+ + + + +
| id | name | contact | location |
+ + + + +
| 1 | ElectroWorld | 123-456-7890 | Mumbai |
| 2 | TechSupply Co. | 987-654-3210 | Delhi |
| 3 | Gadget Hub | 456-789-0123 | Bangalore |
| 4 | Device Central | 321-654-9870 | Chennai |
+ + + + +
4 rows in set (0.00 sec)

mysql> select * from time ;


+ + + + +
| id | year | month | day |
+ + + + +
| 1 | 2023 | January | 15 |
| 2 | 2023 | February | 10 |
| 3 | 2023 | March | 5 |
| 4 | 2023 | April | 20 |
+ + + + +
4 rows in set (0.00 sec)

mysql> select * from productprofit ;


+ + + +
| product_id | product_name | total_profit |
+ + + +
| 1 | Laptop | 15000.00 |
| 2 | Smartphone | 30000.00 |
| 3 | Tablet | 9000.00 |
| 4 | Monitor | 3750.00 |
+ + + +
4 rows in set (0.00 sec)

mysql> select * from fact ;


+ + + + + + +
+
| product_id | suppl_id | time_id | locat_ | revenue |sales | profit
|
| | | | pincode| | | |
+ + + + + + +
+
| 1 | 1 | 1 | 400001 | 50000.00 | 10 | 15000.00
|
| 2 | 2 | 2 | 110001 | 120000.00 | 50 | 30000.00
|
| 3 | 3 | 3 | 560001 | 30000.00 | 20 | 9000.00
|
| 4 | 4 | 4 | 600001 | 11250.00 | 5 | 3750.00
|
+ + + + + +
+
4 rows in set (0.00 sec)

mysql> select * from location ;


+ + + + +
| pincode | region | city | state |
+ + + + +
| 110001 | Northern Region | Delhi | Delhi |
| 400001 | Western Region | Mumbai | Maharashtra |
| 560001 | Southern Region | Bangalore | Karnataka |
| 600001 | Southern Region | Chennai | Tamil Nadu |
+ + + + +
4 rows in set (0.01 sec)

Roll-up Operation From City --> State

mysql> SELECT
-> loc.state,
-> SUM(fact.revenue) AS total_revenue,
-> SUM(fact.profit) AS total_profit
-> FROM
-> fact
-> JOIN
-> location loc ON fact.location_pincode = loc.pincode
-> GROUP BY
-> loc.state;
+ + + +
| state | total_revenue | total_profit |
+ + + +
| Maharashtra | 50000.00 | 25000.00 |
| Delhi | 120000.00 | 30000.00 |
| Karnataka | 30000.00 | 12000.00 |
| Tamil Nadu | 11250.00 | 5750.00 |
+ + + +
4 rows in set (0.00 sec)

Drill-down Operation : total profit of each product by city

mysql> SELECT
-> loc.city,
-> prod.type AS product_type,
-> SUM(fact.revenue) AS total_revenue,
-> SUM(fact.profit) AS total_profit
-> FROM
-> fact
-> JOIN
-> location loc ON fact.location_pincode = loc.pincode
-> JOIN
-> product prod ON fact.product_id = prod.id
-> GROUP BY
-> loc.city, prod.type;
+ + + + +
| city | product_type | total_revenue | total_profit |
+ + + + +
| Mumbai | Laptop | 50000.00 | 15000.00 |
| Delhi | Smartphone | 120000.00 | 30000.00 |
| Bangalore | Tablet | 30000.00 | 9000.00 |
| Chennai | Monitor | 11250.00 | 3750.00 |
+ + + + +
4 rows in set (0.00 sec)

SLICE OPERATION :

mysql> SELECT
-> fact.product_id,
-> fact.supplier_id,
-> fact.time_id,
-> fact.location_pincode,
-> fact.revenue,
-> fact.inventory_of_sales,
-> fact.profit
-> FROM
-> fact
-> JOIN
-> location loc ON fact.location_pincode = loc.pincode
-> WHERE
-> loc.city = 'Mumbai';
+ + + + + + + +
| product_id | suppler | time_id |pincode| revenue |sales | profit |
| | _id | | | | | |
+ + + + + + + +
| 1 | 1 | 1 |400001 | 50000.00 | 10 | 15000.00 |
+ + + + + + + +
1 row in set (0.00 sec)

DICE OPERATION :

mysql> SELECT
-> loc.city,
-> prod.type AS product_type,
-> fact.revenue,
-> fact.profit
-> FROM
-> fact
-> JOIN
-> location loc ON fact.location_pincode = loc.pincode
-> JOIN
-> product prod ON fact.product_id = prod.id
-> WHERE
-> (loc.city = 'Delhi' OR loc.city = 'Bangalore')
-> AND (prod.type = 'Smartphone' OR prod.type = 'Tablet');
+ + + + +
| city | product_type | revenue | profit |
+ + + + +
| Delhi | Smartphone | 120000.00 | 30000.00 |
| Bangalore | Tablet | 30000.00 | 9000.00 |
+ + + + +

PIVOT OPERATION:

mysql> CREATE TABLE location_product_pivot (


-> Product_Name VARCHAR(50),
-> Delhi DECIMAL(10, 2),
-> Mumbai DECIMAL(10, 2),
-> Bangalore DECIMAL(10, 2),
-> Chennai DECIMAL(10, 2)
-> );
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO location_product_pivot (Product_Name, Delhi, Mumbai,


Bangalore, Chennai)
-> VALUES
-> ('Laptop', 12000.00, 15000.00, 5000.00, 8000.00),
-> ('Smartphone', 30000.00, 20000.00, 12000.00, 10000.00),
-> ('Tablet', 8000.00, 9000.00, 15000.00, 6000.00),
-> ('Monitor', 5000.00, 7500.00, 3500.00, 4000.00);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from location_product_pivot ;


+ + + + + +
| Product_Name | Delhi | Mumbai | Bangalore | Chennai |
+ + + + + +
| Laptop | 12000.00 | 15000.00 | 5000.00 | 8000.00 |
| Smartphone | 30000.00 | 20000.00 | 12000.00 | 10000.00 |
| Tablet | 8000.00 | 9000.00 | 15000.00 | 6000.00 |
| Monitor | 5000.00 | 7500.00 | 3500.00 | 4000.00 |
+ + + + + +
4 rows in set (0.00 sec)

mysql> SELECT
-> 'Delhi' AS City,
-> SUM(CASE WHEN Product_Name = 'Laptop' THEN Delhi ELSE 0 END)
AS Laptop,
-> SUM(CASE WHEN Product_Name = 'Smartphone' THEN Delhi ELSE 0
END) AS Smartphone,
-> SUM(CASE WHEN Product_Name = 'Tablet' THEN Delhi ELSE 0 END)
AS Tablet,
-> SUM(CASE WHEN Product_Name = 'Monitor' THEN Delhi ELSE 0 END)
AS Monitor
-> FROM location_product_pivot
-> UNION ALL
-> SELECT
-> 'Mumbai' AS City,
-> SUM(CASE WHEN Product_Name = 'Laptop' THEN Mumbai ELSE 0 END)
AS Laptop,
-> SUM(CASE WHEN Product_Name = 'Smartphone' THEN Mumbai ELSE 0
END) AS Smartphone,
-> SUM(CASE WHEN Product_Name = 'Tablet' THEN Mumbai ELSE 0 END)
AS Tablet,
-> SUM(CASE WHEN Product_Name = 'Monitor' THEN Mumbai ELSE 0 END)
AS Monitor
-> FROM location_product_pivot
-> UNION ALL
-> SELECT
-> 'Bangalore' AS City,
-> SUM(CASE WHEN Product_Name = 'Laptop' THEN Bangalore ELSE 0
END) AS Laptop,
-> SUM(CASE WHEN Product_Name = 'Smartphone' THEN Bangalore ELSE
0 END) AS Smartphone,
-> SUM(CASE WHEN Product_Name = 'Tablet' THEN Bangalore ELSE 0
END) AS Tablet,
-> SUM(CASE WHEN Product_Name = 'Monitor' THEN Bangalore ELSE 0
END) AS Monitor
-> FROM location_product_pivot
-> UNION ALL
-> SELECT
-> 'Chennai' AS City,
-> SUM(CASE WHEN Product_Name = 'Laptop' THEN Chennai ELSE 0 END)
AS Laptop,
-> SUM(CASE WHEN Product_Name = 'Smartphone' THEN Chennai ELSE 0
END) AS Smartphone,
-> SUM(CASE WHEN Product_Name = 'Tablet' THEN Chennai ELSE 0 END)
AS Tablet,
-> SUM(CASE WHEN Product_Name = 'Monitor' THEN Chennai ELSE 0
END) AS Monitor
-> FROM location_product_pivot;
+ + + + + +
| City | Laptop | Smartphone | Tablet | Monitor |
+ + + + + +
| Delhi | 12000.00 | 30000.00 | 8000.00 | 5000.00 |
| Mumbai | 15000.00 | 20000.00 | 9000.00 | 7500.00 |
| Bangalore | 5000.00 | 12000.00 | 15000.00 | 3500.00 |
| Chennai | 8000.00 | 10000.00 | 6000.00 | 4000.00 |
+ + + + + +
4 rows in set (0.01 sec)

You might also like