Analytic SQL Function: A Solution for Well Managed ETL in ROLAP

Analytic SQL is not at all a new feature of Oracle. It is available from Oracle 8i. Still it is very much useful in data analysis specially if your data mart is designed on Relational OLAP. But if you are proficient with analytic tool like Analytic Workspace Manager, then you may not need to use analytic SQL so much.

I found analytic SQL very much useful in several data mart ETL. Here I will show an example how it will simplify you SQL. Suppose, you want to calculate the best performance location of every customer from the sales report. Its very simple if you use analytic SQL. Here is it -

select distinct cx, first_value(location) over (partition by cx order by rev desc,location) location
from (select cx,location,sum(cost) rev
from sales group by cx,location)

At first step, you get the aggregate data. Then run your analytic SQL on this aggregate data. This is because, analytic function works well on aggregate data. In the above example, the steps are same. At the beginning get the customer and location wise revenue.

Then partition your data based on customer and in each partition sort the data primarily based on revenue, then based on location in descending manner. So, the first row of each partition must be the highest revenue considering location wise segregation. If two location contains location wise maximum revenue, then get the location whose name comes first in lexical sorting.

Here is a link for the beginner of analytic sql function -

http://www.oracle-base.com/.

I have found AVG, FIRST_VALUE, LAST_VALUE, RANK, ROW_NUMBER, CORR, NTH_VALUE to be most useful. Specially, ROW_NUMBER removes the limitation of rownum and it can be used for data paging.