In this tutorial we learn about the DIMENSION BY component of the MODEL clause.
DIMENSION BY specifies which columns in a SELECT statement are dimension columns, which for our purposes can be thought of as any column that serves to identify each row in the result of a SELECT statement. By default, the dimension columns in a MODEL clause must produce a unique key for the result set. See the Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Glossary for a formal definition.
Before we begin please note that, on its own, DIMENSION BY has little visible effect on the output of the SELECT statement. Most of the examples below would produce the same result as one with no MODEL clause at all. This is because we are not trying to manipulate the results just yet. We are simply seeing how to specify our dimension columns, which is a precursor for learning to manipulate results in subsequent pages.
Consider the following table.
select key , key_2 , group_1 , group_2 , num_val from t order by key ;
KEY KEY_2 GROUP_1 GROUP_2 NUM_VAL
------ ----- ---------- ---------- -------
1 T-1 A a1 100
2 T-2 A a2 200
3 T-3 A a3 300
4 T-4 B a1
5 T-5 B a2 300
6 T-6 B a3 100
7 T-7 C a1 100
8 T-8 C a2
9 T-9 a1 200
10 T-10 a2 800
We see that KEY, KEY_2, and (GROUP_1, GROUP_2) all uniquely identify each row in the table. They are therefore dimension column candidates. To let Oracle know which column(s) we plan to use as dimensions we compose a MODEL clause like this. (Ignore the MEASURES and RULES clauses for now. We will explore those later.)
select key , num_val from t model DIMENSION BY ( KEY ) measures ( num_val ) rules () order by key ;
KEY NUM_VAL
------ -------
1 100
2 200
3 300
4
5 300
6 100
7 100
8
9 200
10 800
Multiple Dimensions
If needed, you can define more than one dimension column, as this example shows.
select group_1 , group_2 , num_val from t model DIMENSION BY ( GROUP_1, GROUP_2 ) measures ( num_val ) rules () order by group_1, group_2 ;
GROUP_1 GROUP_2 NUM_VAL
---------- ---------- -------
A a1 100
A a2 200
A a3 300
B a1
B a2 300
B a3 100
C a1 100
C a2
a1 200
a2 800
You can even include columns in the DIMENSION BY clause which are not required to uniquely identify each result row.
select key , date_val , num_val from t model DIMENSION BY ( KEY, DATE_VAL ) -- date_val not required to uniquely identify row measures ( num_val ) rules () order by key ;
KEY DATE_VAL NUM_VAL
------ ---------- -------
1 2005-01-01 100
2 2005-06-12 200
3 300
4 2006-02-01
5 2006-06-12 300
6 2005-01-01 100
7 2006-06-12 100
8
9 2005-02-01 200
10 2005-02-01 800
Aliasing
You cannot use SELECT clause aliases in DIMENSION BY. Here are some examples of aliases that will cause errors.
select
KEY AS KEY_3 ,
num_val
from
t
model
dimension by ( KEY_3 )
measures ( num_val )
rules ()
;
dimension by ( KEY_3 )
*
ERROR at line 7:
ORA-00904: "KEY_3": invalid identifier
select
KEY * 10 AS KEY_3 ,
num_val
from
t
model
dimension by ( KEY_3 )
measures ( num_val )
rules ()
;
dimension by ( KEY_3 )
*
ERROR at line 7:
ORA-00904: "KEY_3": invalid identifier
select
ROWNUM AS KEY_3 ,
num_val
from
t
model
dimension by ( KEY_3 )
measures ( num_val )
rules ()
;
dimension by ( KEY_3 )
*
ERROR at line 7:
ORA-00904: "KEY_3": invalid identifier
You can however alias such expressions directly in DIMENSION BY.
select KEY_3, num_val from t model DIMENSION BY ( KEY AS KEY_3 ) measures ( num_val ) rules () order by key_3 ;
KEY_3 NUM_VAL
---------- -------
1 100
2 200
3 300
4
5 300
6 100
7 100
8
9 200
10 800
select KEY_3, num_val from t model DIMENSION BY ( KEY * 10 AS KEY_3 ) measures ( num_val ) rules () order by key_3 ;
KEY_3 NUM_VAL
---------- -------
10 100
20 200
30 300
40
50 300
60 100
70 100
80
90 200
100 800
select KEY_3, num_val from t model DIMENSION BY ( ROWNUM AS KEY_3 ) measures ( num_val ) rules () order by key_3 ;
KEY_3 NUM_VAL
---------- -------
1 100
2 200
3 300
4
5 300
6 100
7 100
8
9 200
10 800
Uniqueness
By default, if your DIMENSION BY columns do not give you a unique key for your result set you will get an error.
select group_2 , num_val from t model DIMENSION BY ( GROUP_2 ) -- group_2 is not unique measures ( num_val ) rules () order by group_2 ; t * ERROR at line 5: ORA-32638: Non unique addressing in MODEL dimensions
This rule can be relaxed somewhat by specifying UNIQUE SINGLE REFERENCE.
select group_2 , num_val from t model UNIQUE SINGLE REFERENCE dimension by ( group_2 ) -- group_2 is not unique measures ( num_val ) rules () order by group_2 ;
GROUP_2 NUM_VAL ---------- ------- a1 100 a1 100 a1 200 a1 a2 800 a2 200 a2 300 a2 a3 300 a3 100
Note that UNIQUE SINGLE REFERENCE affects the types of RULES you can define. This is explained further in Expressions and Cell References.