With the SQL model clause you build one or more matrixes with a variable number of dimensions. This is called the model. The model uses a subset of the available columns from your FROM clause. It contains at least one dimension, at least one measure and optionally one or more partitions. You can think of a model as a spreadsheet file containing separate worksheets for each calculated value (measures). A worksheet has a X- and an Y-axis (two dimensions) and you can imagine having your worksheets split up in several identical areas, each for a different country or department (partition).

The next figure shows a model of the well known EMP table, where deptno is a partition, empno a dimension and sal and comm are two measures:

Once your model is setup, you define rules that modify your measure values. These rules are the core of the model clause. With few rules you are able to make complex calculations on your data and you are able to create new rows as well. The measure columns are now arrays that are indexed by the dimension columns, where the rules are applied to all partitions of this array. After all rules are applied, the model is converted back to traditional rows.

My experience with the model clause wire diagram in the Oracle documentation is that it is quite complex and it tends to scare off people. That’s not what I’d like to achieve here, so I’ll use a different approach using lots of small examples using the EMP and DEPT table, starting with very simple ones, and gradually expanding on that. At the end of this piece, you’ll find a script you can download and run on your own database.

Here we have two measures, ename and sal, and one dimension being empno. The combination of all partitions and dimension columns have to be unique. This is checked at runtime, and if violated, you’ll get an ORA-32638. Because of the two measures, two one dimensional arrays are internally created, which are index by empno. At line 9 you see the keyword “RULES”, but without contents yet. This is the place where the rules will be defined doing the calculations and the creations of new rows. The keyword “RULES” is optional, but for sake of clarity I will always write it down. When done modeling, all partitions, dimensions and measures are converted back to columns in traditional rows, which means that you can only have columns in your select list that appear in your model. If, for instance, I did not include the column ename as a measure, I would get this error message

The rule at line number 10 shows how measure ename is expanded with dimension 7777 and gets the value ‘VAN WIJK’. If table EMP would have contained an empno 7777 already, then the existing ename value for empno would have been overwritten by this rule. But 7777 does not exist in EMP, so now a new cell has been created, which shows itself as a new row in the result set. Please note that the row is not being inserted into the table, but only to the result set of the query. By adding a second rule you can fill the sal column as well:

All calculations are being performed on each partition. We can see this happening when we drop the filter predicate “deptno = 10” and show the deptno column instead. Now the question becomes whether to include the deptno column as a partition or as a measure. First, let’s explore what happens if we define deptno as a measure:

A clear difference. In table EMP only the deptno values 10, 20 and 30 occur and therefore this model effectively has three partitions. The rule is applied to all three partitions, thus resulting in the addition of three new rows.

So far the examples will probably not have persuaded you to use the model clause. A lot of extra lines of SQL code were introduced in order to get something done which is quite simple: with the creative use the UNION ALL set operator and table DUAL we could have achieved the same results. The intention of this first part is only to show the basics. In the next part I will show multi cell references, references models and iterations. And this is where things become more interesting.

6 comments:

Hi, Rob. AFAIU you finally got some free time and began to translate your dutch article on Model clause into english.At least the first part is so easy that it can be used as a school-book for those who have difficulties with understanding the description in the manuals or who are lazy to read it (manual I mean) :))Hope to see next parts soon.