Writing OLAP SQL query

3
votes

I have a project I am working on that requires the following :

  1. create a normalized sample rdbms (5 tables)
  2. using Java I entered 1 million rows of data to each table
  3. run two OLTP and two OLAP queries on the normalized tables.
  4. Denormalized tables.
  5. run the same OLTP and OLAP queries on them and compare time.

What does OLAP query mean? I've searched the internet and all that I can find is that I have to make a cube, and apply queries on it. How can I write an OLAP query on a RDBMS? I have a sample : tables normalized(orders,product,customer,branch,sales)

  1. sales : order_id,product_id,quantity
  2. product : product_id,name,description,price,sales_tax
  3. customer : customer_id,f_name,l_name,tel_no,addr,nic,city
  4. branch : branch_id,name,tel_no,addr,city
  5. orders : order_id,customer_id,order_date,branch_id

I want to write an OLAP query on the above tables. I am using Oracle Express with SQL Developer.

1
sqloraclerdbmsolap
Sample of OLAP query i've made up for the above tables : SELECT BRANCH.BRANCH_ID, PRODUCT.PRODUCT_ID, ORDERS.ORDER_DATE,SUM(SALES.QUANTITY) FROM BRANCH,PRODUCT,SALES,ORDERS WHERE ORDERS.BRANCH_ID = BRANCH.BRANCH_ID AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID AND ORDERS.ORDER_ID = SALES.ORDER_ID AND ORDER_DATE BETWEEN to_date ('02/01/2000', 'dd/mm/yyyy') AND to_date ('12/12/2006', 'dd/mm/yyyy') GROUP BY BRANCH.BRANCH_ID,SALES.PRODUCT_ID, ORDERS.ORDER_DATE - hasnain095

1 Answers

2
votes

There are some SQL functions that are sometimes called "analytic", "windowing", or "OLAP", depending on the RDBMS you are using. In some scenarios they can provide enormous performance improvements, typically where you are comparing rows within the same set of data, which would otherwise require a self-join.

As you are using Oracle I would heartily recommend visiting asktom.oracle.com and searching for "analytics rock" - you will find some great examples, which are very well explained and much easier than just reading the Oracle documentation, which is here.

Some notes: they are not strictly "relational", because relational theory does not address the order of tuples; they are carried out after the other parts of a query - i.e. after filtering and aggregation; and they are implemented to a lesser extent by some databases.

I see that Oracle also have a separate OLAP offering for working with cubes, but this is currently a separately licensed option for Enterprise Edition, so I don't think it is what you are referring to if you are using Express edition.

EDIT

Here's a simple example roughly based on your data model that shows how you can use analytic functions to get a running total, which is not simple to do without them:-

select 
o.customerid, 
o.orderid, 
o.orderdate, 
p.price, 
sum (p.price) over (partition by o.customerid order by o.orderdate) running_total

from orders o
inner join sales s on o.orderid = s.orderid
inner join product p on s.productid = p.productid

This gives (for some simple data I inputted):-

CUSTOMERID           ORDERID              ORDERDATE PRICE RUNNING_TOTAL
-------------------- -------------------- --------- ----- -------------
1                    1                    01-JAN-12    30            30 
1                    2                    01-MAR-12    30            60 
1                    3                    01-APR-12    30            90 
1                    4                    01-MAY-12    30           120