Intelligent Enterprise

Moreover, when you use an OLAP function with a column function, such as AVG, SUM, or MAX, the target rows can be further refined, relative to the current row, as either a range or a number of rows preceding and following the current row. For example, within a window partitioned by month, a moving average can be calculated over the previous three-month period.

The value of having these terms available in leading product lines is that you can call upon the entire SQL vocabulary to combine in any of your OLAP-centric SQL statements.

OLAP Examples of SQL

The following example illustrates an advanced OLAP function used in combination with traditional SQL. The result is a valuable SQL statement that epitomizes the power and relevance of BI at the database engine level.

This example shows a rollup function of sales by region and city:

SELECT b.region_type_id,
a.city_id,
SUM(a.sales) AS
TOTAL_SALES
FROM fact_table a,
lookup_market b
WHERE YEAR(transdate)
=1999
AND a.city_id=b.city_id
AND b.region_type_id=6
GROUP BY ROLLUP
(b.region_type_id,
a.city_id)
ORDER BY
b.region_type_id,
a.city_id

The resultant set is reduced by explicitly querying region 6 and the year 1999. A sample result of the SQL is shown in Table 1, below. The result shows ROLLUP of two groupings (region, city) returning three totals, including region, city, and grand total.

Enterprisewide Dimensional Layer

The traditional data warehouse architecture includes an atomic layer of granular data, often normalized, that serves as the only source of data for subsequent, subject-specific data marts. Generally, the data marts are implemented as star schemas, proprietary MOLAP cubes, or both. Establishing a layer of data marts provides an excellent foundation from which to serve up consistent, multidimensional data on an enterprise scale. But when you couple the current notion of data marts with OLAP-centric SQL functions, it's important that BI architects confirm the value added from proprietary, OLAP-only technology, specifically proprietary multidimensional database servers.