MODEL Clause

DIMENSION BY

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11664.html]SQL Snippets: MODEL Clause - DIMENSION BY[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11664.html">SQL Snippets: MODEL Clause - DIMENSION BY</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: MODEL Clause - DIMENSION BY
  • URL (href): http://www.sqlsnippets.com/en/topic-11664.html

Revision Notes

Date Category Note
2007-03-27 Revision A new section was added to better describe Aliasing.