Oracle Reporting 6 - Model

Example: 
SELECT SUBSTR(country, 1, 20) country,
SUBSTR(product, 1, 15) product, year, sales
FROM sales_view
WHERE country IN ('Italy','Japan' ) 
and product in 'Bounce'
and year in (2000, 2001)
MODEL
RETURN ALL ROWS --RETURN UPDATED ROWS
PARTITION BY (country) DIMENSION BY (product, year) 
MEASURES (sales sales) --IGNORE NAV
RULES UPSERT --UPDATE, UPSERT ALL
(sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000])
ORDER BY country, product, year;

Partition - defines the logical blocks of the result set in a way similar to the partitions of the analytical functions. Rules in the MODEL clause are applied to each partition independent of other partitions. Thus, partitions serve as a boundary point for parallelizing the MODEL computation. Oracle parallelizes  model computation based on partitions.

Dimension - defines the multi-dimensional array and are used to identify cells within a partition. By default, a full combination of dimensions should identify just one cell in a partition. In default mode, they can be considered analogous to the key of a relational table.

Measure -  equivalent to the measures of a fact table in a star schema. They typically contain numeric values such as sales units or cost. Each cell is accessed by specifying its full combination of dimensions. Note that each partition may have a cell that matches a given combination of dimensions.

Rules - manipulates the measure values of the cells in the multi-dimensional array defined by partition and dimension columns.

CV Function - use cv function on the right side of a rule to access the value of a dimension column of the cell referenced on the left side of a rule.

ANY/IS ANY - all values for the dimension, including nulls, satisfy the ANY specification. New cells will not be created if any of the references is ANY.


ITERATE - run the rule 4 times.
MODEL DIMENSION BY (x) MEASURES (s)
RULES ITERATE (4) (s[x=1] = s[x=1]/2)

RETURN UPDATED ROWS - return only those rows that are updated or inserted by the model computation. By default or if you use RETURN ALL ROWS, you would get all rows not just the ones updated or inserted by the MODEL clause.

SEQUENTIAL ORDER - rules to be evaluated in the order they are written. This is the default.

AUTOMATIC ORDER - all rules to be evaluated in an order based on their logical dependencies.

MEASURES (sales, 0 AS profits) - 0 as the default value for this measure.

Symbolic Dimension References - sales[product = 'Bounce', year >= 2001].

Positional Dimension References - sales['Bounce', 2001]

UPSERT - this updates the measure values of existing cells. If the cells do not exist, and the rule has appropriate notation, they are inserted. If any of the cell references are
symbolic, no cells are inserted. By default, rules in the MODEL clause have UPSERT semantics,

UPSERT ALL - this is similar to UPSERT, except it allows a broader set of rule notation to insert new cells.

UPDATE - this updates existing cell values. If the cell values do not exist, no updates are done.

IGNORE NAV - default nulls and missing cells to:
 0 for numeric data
 Empty string for character/string data
 01-JAN-2001 for data type data
 NULL for all other data types 
 This can be used at a global level for all measures in a model. 

KEEP NAV - treats missing cells as cells with NULL measure values.. It is useful for making exceptions when IGNORE NAV is specified at the global level. This is the default, and can be omitted.

If you recall the report of comparing 2001 sales to 2000 sales from Oracle Reporting 4 - Time Series Calculations, with model clause, it's a piece of cake to rewrite that query:
WITH v AS
( 
  SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year,
  sum(s.amount_sold) sales
  FROM Sales s, Times t, Products p
  WHERE s.Time_id = t.Time_id AND
  s.Prod_id = p.Prod_id AND p.Prod_name in ('Y Box') AND
  t.Calendar_Year in (2000,2001)       
  GROUP BY p.Prod_Name, t.Calendar_Year
)
select prod, calendar_year, sales, last_year_sales
from v
model 
return all rows
dimension by(prod, calendar_year)
measures (sales, 0 last_year_sales)
rules (
  last_year_sales [prod, calendar_year] = presentv( sales[cv(prod), cv(calendar_year)-1], sales[cv(prod), cv(calendar_year)-1],0)
);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1061170/，如需轉載，請註明出處，否則將追究法律責任。