| Team Fly | |
As we stated earlier, we as database administrators do not have to know the theory behind the functions provided by Oracle Database 10g, or even how to use their results. However, we should be able to let our users know what capabilities are available. Knowing this, our users will be able to take advantage of these functions and construct efficient queries. In the next section, we will be discussing a new feature in Oracle Database 10g—SQL models.
One of the more powerful data analysis features introduced in Oracle Database 10g is SQL models. SQL models allow a user to create multidimensional arrays from query results. Formulas, both simple and complex, can then be applied to the arrays to generate results in which the user is interested. SQL models allow inter-row calculations to be applied without doing expensive self-joins.
SQL models are similar to other multidimensional structures used in business intelligence applications. However, because they are part of the database, they can take advantage of Oracle Database 10g's built-in features of scalability, manageability security, and so on. In addition, using SQL models, there is no need to transfer large amounts of data to external business intelligence applications.
A SQL model is defined by the model extension of the select statement. Columns of a query result are classified into one of three groups:
Partitioning This is the same as the analytic partitioning we defined in the Windowing Functions section.
Dimensions These are the attributes used to describe or fully qualify a measure within a partition. Examples could include product, sales rep id, and phone call type.
Measures These are the numeric (usually) values to which calculations are applied. Examples could include quantity sold, commission amount, and call duration.
One of the main applications of SQL models is projecting or forecasting measures based on existing measures. Let's look at an example of the model clause to illustrate. The listing and its results show an aggregate query using the SALES table:
select c.channel_desc, p.prod_category, t.calendar_year year,
sum(s.quantity_sold) quantity_sold
from sales s, products p, channels c, times t
where s.prod_id = p.prod_id
| Team Fly | |