18.5 Reference Models

Commercial spreadsheet applications, such as Microsoft Excel, allow you to link cells from one spreadsheet to those in another spreadsheet. The same thing is possible with model queries in the Oracle database. A given model can reference one or more read-only spreadsheets, called reference models.

The REFERENCE clause can be used for referencing spreadsheets/models. The syntax of the REFERENCE clause is:

REFERENCE name ON (query)

DIMENSION BY (d)

MEASURES (m)

[ref_options]

The syntax elements are:

name

A name for the reference model. You use this together with dot notation to reference values from the model.

query

A SELECT statement that defines the reference model.

d

Dimension column(s) for the reference model.

m

Reference column(s) for the reference model.

ref_options

Cell referencing options such as IGNORE NAV, KEEP NAV.

For example, the following query uses a REFERENCE clause to create a one-dimensional reference model containing monthly adjustment factors. Those adjustment factors are then referenced by the rule in the main part of the MODEL clause:

SELECT r, y, m, s

FROM sales_history

MODEL

  UNIQUE SINGLE REFERENCE  

  RETURN UPDATED ROWS

  REFERENCE ref_adj ON

    (SELECT month, factor FROM monthly_sales_adjustment)

    DIMENSION BY (month)

    MEASURES (factor)

  PARTITION BY (region_id r)

  DIMENSION BY (year y, month m)

  MEASURES (sales s)

  RULES 

  (

    s[2004, FOR m in (1,2,3)] = AVG(s)[y BETWEEN 1995 AND 2003,CV(m)] 

                                * ref_adj.factor[CV(m)]

  )

ORDER BY y, r, m;



    R          Y          M          S

----- ---------- ---------- ----------

    5       2004          1  687440.25

    5       2004          2  858965.67

    5       2004          3  747757.56

    6       2004          1  824440.95

    6       2004          2  598003.02

    6       2004          3  840880.92

    7       2004          1  511678.35

    7       2004          2 862700.085

    7       2004          3  865910.76

Look at the following component in the preceding query:

  REFERENCE ref_adj ON

    (SELECT month, factor FROM monthly_sales_adjustment)

    DIMENSION BY (month)

    MEASURES (factor)

This code component defines the reference model, which has its own dimensions and measures. In this case, the reference model is a one-dimensional array filled in with adjustment factors from the monthly_sales_adjustment table. Those factors are dimensioned by month, making it easy to retrieve the adjustment factor for any given month.

The cells of the reference model are referenced from the main model in the following lines:

s[2004, FOR m in (1,2,3)] = AVG(s)[y BETWEEN 1995 AND 2003,CV(m)] 

                            * ref_adj.factor[CV(m)]

The cells of the reference model are qualified using the name of the reference model. In this example, we used ref_adj for our reference model name. Thus, the single measure is ref_adj.factor. Use dot notation to qualify a measure name with the name of the reference model containing the measure. The cell reference ref_adj.factor[CV(m)] that you see here retrieves the adjustment factor for each month.

When a query contains more than one model, it makes sense to name each model such that it can be distinguished easily. In the preceding example, you saw how to name a reference model. You can actually name the main model, too, by specifying a name along with the MAIN option, as shown in the following example:

SELECT r, y, m, s

FROM sales_history

MODEL

  UNIQUE SINGLE REFERENCE  

  RETURN UPDATED ROWS

  REFERENCE ref_adj ON

    (SELECT month, factor FROM monthly_sales_adjustment)

    DIMENSION BY (month)

    MEASURES (factor)

  MAIN sales_forecast

  PARTITION BY (region_id r)

  DIMENSION BY (year y, month m)

  MEASURES (sales s)

  RULES 

  (

    s[2004, FOR m in (1,2,3)] = AVG(s)[y BETWEEN 1995 AND 2003,CV(m)] 

                                * ref_adj.factor[CV(m)]

  )

ORDER BY y, r, m;

In this example, the main spreadsheet is named sales_forecast. You can name the main spreadsheet irrespective of whether it references other spreadsheets.

The following rules and restrictions apply to reference models:

• The query defining the reference model cannot correlate to the outer (main) query.

• A reference model cannot have a PARTITION BY clause.

• Reference models are read-only. You can't update/upsert a cell in the reference model.

A model query can have only one main spreadsheet, but many reference spreadsheets.