One of the more powerful data analysis features introduced in Oracle Database 12c 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’s built-in features of scalability, manageability, security, and so on. In addition, when 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 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:
In the results, you can see the historical aggregate quantity_sold for each year by product category for the Direct Sales channel. You can use the model clause to project the quantity_sold. In the following listing, you’ll project values for 2002 for the product category Hardware in the channel. The quantity_sold will be based on the previous year’s value (2001), plus 10 percent. Table 1 explains the syntax of the listing.
Following are the results of the previous query. Notice that a new row has been added for Hardware in 2002. Its quantity_sold is 2638.9, which is the previous year’s value (2399) plus 10 percent.
The model clause has many variations and allows for very powerful calculations. We want to point out some of the characteristics and/or features you should be aware of. Supported functionalities include the following:
- Looping (for example, FOR loops)
- Recursive calculations
- Regression calculations
- Nested cell references
- Dimension wildcards and ranges
- The model clause does not update any base table, although in theory, you could create a table or materialized view from the results of the query using the model clause.
- Restrictions include the following:
- The rules clause cannot include any analytic SQL or windowing functions.
- A maximum of 20,000 rules may be specified. This may seem like plenty, but a FOR loop is expanded into many single-cell rules at execution time.
Leave a Reply