SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we learn about the MEASURES component of the MODEL clause.

MEASURES specifies which columns in a SELECT are measure columns, which for our purposes can be thought of as any column containing a measurable quantity like a price or a length. 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, MEASURES 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 measure columns, which is a precursor to manipulating the results. We will see how to actually manipulate our output when we explore the RULES clause in subsequent tutorials.

Before we see MEASURES in action first consider the following table.

select
  key ,
  group_1  ,
  group_2  ,
  date_val ,
  num_val
from
  t
order by
  key
;
 

   KEY GROUP_1    GROUP_2    DATE_VAL   NUM_VAL
------ ---------- ---------- ---------- -------
     1 A          a1         2005-01-01     100
     2 A          a2         2005-06-12     200
     3 A          a3                        300
     4 B          a1         2006-02-01
     5 B          a2         2006-06-12     300
     6 B          a3         2005-01-01     100
     7 C          a1         2006-06-12     100
     8 C          a2
     9            a1         2005-02-01     200
    10            a2         2005-02-01     800
 

If we decide to use KEY as our sole dimension column, then all other columns are available for use as measure columns. To let Oracle know we want to use the NUM_VAL column as our measure we can compose a MODEL clause like this.

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
 

If we want to include more measure columns we do it like this.

select
  key ,
  date_val ,
  num_val
from
  t
model
  dimension by ( key )
  MEASURES     ( DATE_VAL, 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
 

You can define measures using constants and expressions instead of simple column names, like this.

select
  key ,
  num_val    ,
  num_val_2  ,
  date_val_2 ,
  note
from
  t
model
  dimension by
  ( key )
  MEASURES
  ( num_val ,
    NUM_VAL * 10    AS NUM_VAL_2  ,
    SYSDATE         AS DATE_VAL_2 ,
    'A BRIEF NOTE'  AS NOTE
  )
  rules( )
order by
  key
;
 

   KEY NUM_VAL  NUM_VAL_2 DATE_VAL_2 NOTE
------ ------- ---------- ---------- ------------
     1     100       1000 2007-02-28 A BRIEF NOTE
     2     200       2000 2007-02-28 A BRIEF NOTE
     3     300       3000 2007-02-28 A BRIEF NOTE
     4                    2007-02-28 A BRIEF NOTE
     5     300       3000 2007-02-28 A BRIEF NOTE
     6     100       1000 2007-02-28 A BRIEF NOTE
     7     100       1000 2007-02-28 A BRIEF NOTE
     8                    2007-02-28 A BRIEF NOTE
     9     200       2000 2007-02-28 A BRIEF NOTE
    10     800       8000 2007-02-28 A BRIEF NOTE