The CV() Function and ANY Wildcard

FOR Loops

Order of Evaluation of Rules

Reference MODELs

Iterative MODELs

Ordered Rules

sales('prod1', 2002) = sales('prod1', 2000) + sales('prod1', 2001)
sales('prod2', 2002) = sales('prod2', 2000) + sales('prod2', 2001)

Completed the Installing Oracle Database 10g on Windows Using Real Application Clusters (RAC) and Automated Storage Management (ASM) tutorial

OR

Completed the Installing Oracle Database 10g on Linux tutorial

First make sure that you have a clean environment. From a terminal window, execute the following command:

cd /home/oracle/wkdir
sqlplus sh/sh
@cleanup

The cleanup.sql script contains the following:

DROP VIEW sales_view;
DROP TABLE dollar_conv;
DROP TABLE growth_rate;
DROP TABLE ledger;

Now you can create the SALES_VIEW view. From your SQL*Plus session, execute the following script:

@sample_data

The sample_data.sql script contains the following:

CREATE VIEW sales_view AS
   SELECT country_name country, prod_name prod, calendar_year year,
          SUM(amount_sold) sale, COUNT(amount_sold) cnt
   FROM   sales, times, customers, countries, products
   WHERE  sales.time_id = times.time_id AND
          sales.prod_id = products.prod_id 
   AND    sales.cust_id = customers.cust_id
   AND    customers.country_id = countries.country_id
   GROUP BY country_name, prod_name, calendar_year
/

Verify that the view is created correctly and that 3219 rows exist. From your SQL*Plus session, execute the following command:

SELECT COUNT(*) FROM sales_view;

To maximize performance, your system should already have a materialized view built on the data that is used by the above view. The materialized view is created during the installation of the sh schema data. Oracle's summary management system will automatically rewrite any query using the above view so that it takes advantage of the materialized view.

Many of our examples do not require ORDER BY on the COUNTRY column. It is included in the specification in case you want to modify the examples and add multiple countries.

You want to view the SALES value for the product Bounce in the year 2000, in Italy, and set it to 10. To do so, use a "positional cell reference." The value for the cell reference is matched to the appropriate dimension based on its position in the expression. The DIMENSION BY clause of the model determines the position assigned to each dimension—in this case, the first position is product (PROD) and the second position is YEAR. From your SQL*Plus session, execute the following script:

@pos_cell1

The pos_cell1.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
  FROM   sales_view
  WHERE   country='Italy'
  MODEL  RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES  (
      sales['Bounce', 2000] = 10 )
ORDER BY country, prod, year
/

You want to create a forecast value of SALES for the product Bounce in the year 2005, in Italy, and set it to 20. Use a rule in the SELECT statement that sets the year value to 2005 and thus create a new cell in the array. From your SQL*Plus session, execute the following script:

@pos_cell2

The pos_cell2.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country='Italy'
  MODEL  RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES  (
      sales['Bounce', 2005] = 20 )
ORDER BY country, prod, year
/

Note: If you want to create new cells, such as values for future years, you must use positional references or FOR loops (discussed later in this tutorial). That is, positional reference permits both updates and inserts into the array. This is called the UPSERT process.

You want to update the SALES for the product Bounce in all years after 1999 where the values are recorded for Italy and set them to 10. To do so, use a "symbolic cell reference." The value for the cell reference is matched to the appropriate dimension using Boolean conditions. You can use all the normal operators such as <,>, IN, and BETWEEN. In this case the query looks for product value equal to Bounce and any year value greater than 1999. This shows how a single rule can access multiple cells. From your SQL*Plus session, execute the following script:

@sym_cell1

The sym_cell1.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country='Italy'
  MODEL   RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES (
      sales[prod='Bounce', year>1999] = 10 )
ORDER BY country, prod, year
/

Note: Symbolic references are very powerful, but they are used solely for updating existing cells: they cannot create new cells such as sales projections in future years.

You want a single query to update the sales for several products in several years for multiple countries, and you also want it to insert new cells. By placing several rules into one query, processing is more efficient because it reduces the number of times needed to access the data. It also allows for more concise SQL, which supports higher developer productivity. From your SQL*Plus session, execute the following script:

@pos_sym

The pos_sym.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year,   sales
FROM   sales_view  WHERE country IN  ('Italy','Japan') 
  MODEL  RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES ( 
      sales['Bounce', 2002] = sales['Bounce', year = 2001] ,  
      --positional notation:  can insert new cell
      sales['Y Box', year>2000] = sales['Y Box', 1999],   
      --symbolic notation: can update existing cell
      sales['2_Products', 2005] = 
           sales['Bounce', 2001] + sales['Y Box', 2000]  )
      --positional notation:  permits insert of new cells 
      --for new product
ORDER BY country, prod, year
/

The example data has no values beyond the year 2001, so any rule involving the year 2002 or later requires insertion of a new cell. The same applies to any new product name defined here. In the third rule, 2_Products is defined as a product with sales in 2005 which equal the sum of Bounce in 2001 and Y Box in 2000.

The first rule, for Bounce in 2002, inserts new cells since it is positional notation. The second rule, for Y Box, uses symbolic notation, but since there are already values for Y Box in the year 2001, it updates those values. The third rule, for 2_Products in 2005, is positional, so it can insert new cells, and you will see them in the output.

You want to forecast the sales of Bounce in Italy for the year 2005 to be 100 more than the maximum sales in the period 1999 to 2001. To do so, you need to use the BETWEEN clause to specify multiple cells on the right side of the rule, and these are aggregated to a single value with the MAX() function. From your SQL*Plus session, execute the following script:

@multi_c

The multi_c.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country='Italy'
  MODEL   RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES (
      sales['Bounce', 2005] = 
       100 + max(sales)['Bounce', year BETWEEN 1998 AND 2002]  )
ORDER BY country, prod, year
/

Note that aggregate functions can appear only on the right side of rules. Arguments to the aggregate function can be constants, bind variables, measures of the MODEL clause, or expressions involving them.

You want to update the sales values for Bounce in Italy for multiple years, using a rule where each year's sales is the sum of Mouse Pad sales for that year, plus 20% of the Y Box sales for that year. From your SQL*Plus session, execute the following script:

@cvf1

The cvf1.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country='Italy'
  MODEL   RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES (
      sales['Bounce', year BETWEEN 1995 AND 2002] =
        sales['Mouse Pad', cv(year)] + 
        0.2 * sales['Y Box', cv(year)])
ORDER BY country, prod, year
/

Note that, in the above results, you see values for only years 1999–2001 although any year in the range 1995–2002 is accepted. This is because the table has data for only those years. The CV() function provides the current value of a DIMENSION BY key of the cell currently referenced on the left side. When the left side of the rule above references the cell Bounce and 1999, the right side expression would resolve to:

sales['Mouse Pad', 1999] + 0.2 * sales['Y Box', 1999]

Similarly, when the left side references the cell Bounce and 2000, the right side expression evaluates to:

sales['Mouse Pad', 2000] + 0.2 * sales['Y Box', 2000]

The CV() function takes a dimension key as its argument. It is also possible to use CV() without any argument as in cv() and in which case, positional referencing is implied. The above rule can also be written as:

s['Bounce', year BETWEEN 1995 AND 2002] =
  s['Mouse Pad', cv()] + 0.2 * s['Y Box', cv()]

CV() functions can be used only in right side cell references.

You want to calculate the year-over-year percent growth in sales for products Y Box, Bounce and Mouse Pad in Italy. From your SQL*Plus session, execute the following script:

@cvf2

The cvf2.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales, growth
FROM   sales_view
WHERE   country='Italy'
MODEL     RETURN UPDATED ROWS  
  PARTITION BY (country) 
  DIMENSION BY (prod, year)
  MEASURES (sale sales, 0 growth)
  RULES  (
    growth[prod in ('Bounce','Y Box','Mouse Pad'), year between 1998 and 2001] =
      100* (sales[cv(prod), cv(year)] - 
      sales[cv(prod), cv(year) -1] ) /  
      sales[cv(prod), cv(year) -1] )
ORDER BY country, prod, year
/

Note that the blank cells in the results are NULLs. The rule results in a NULL if there is no value for the product two years earlier. None of the products has a value for 1998, so in each case the 1999 growth calculation is NULL.

A wild card operator is very useful for cell specification, and you can use the ANY keyword for this purpose. You can use it with the previous example to replace the specification year between 1998 and 2001 as shown below.

ANY can be used in cell references to include all dimension values including NULLs. In symbolic reference notation, use the phrase IS ANY. Note that the ANY wildcard prevents cell insertion when used with either positional or symbolic notation.

From your SQL*Plus session, execute the following script:

@any

The any.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales, growth
FROM   sales_view
WHERE  country='Italy'
MODEL     RETURN UPDATED ROWS  
  PARTITION BY (country) 
  DIMENSION BY (prod, year)
  MEASURES (sale sales, 0 growth)
  RULES  (
    growth[prod in ('Bounce','Y Box','Mouse Pad'), ANY] =
       100* (sales[cv(prod), cv(year)] - 
       sales[cv(prod), cv(year) -1] ) /  
       sales[cv(prod), cv(year) -1] )
ORDER BY country, prod, year
/

This query gives the same results as the previous query because the full data set ranges from 1998 to 2001, and that is the range that was specified in the previous query.

You want to specify projection sales values for Mouse Pad for the years 2005 to 2012 so that they are equal to 120% of the value in 2001. From your SQL*Plus session, execute the following script:

@for

The for.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country='Italy'
MODEL     RETURN UPDATED ROWS  
  PARTITION BY (country) 
  DIMENSION BY (prod, year)
  MEASURES (sale sales) 
  RULES  (
    sales['Mouse Pad', FOR year FROM 2005 TO 2012 INCREMENT 1] =
      1.2 * sales[cv(prod), 2001]  )
ORDER BY country, prod, year
/

This kind of FOR construct can be used for dimensions of numeric, date, and datetime data types. The increment/decrement expression value3 should be numeric for numeric dimensions and can be numeric or interval for dimensions of date or datetime types. There are other methods to use the FOR construct, and they are described in detail in the Data Warehousing Guide. The most important of these other methods is to use a SQL subquery as the argument for an IN operator. When using FOR constructs with subqueries, it is essential to examine the total number of rules that the FOR construct may generate and make sure that they will not exceed the 10,000 rule limit.

You can have a model with many rules, which creates new product values based on other products. To ensure that the rules will be executed in correct sequence so that no dependencies are missed, use the AUTOMATIC ORDER keywords. The example below contains three rules to illustrate this concept. From your SQL*Plus session, execute the following script:

@s_o

The s_o.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country IN  ('Italy','Japan')
  MODEL   RETURN UPDATED ROWS
    PARTITION BY (country) 
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES SEQUENTIAL ORDER  (
      sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002], 
      sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000],
      sales['Y Box', 2002] = sales['Y Box', 2001] )
ORDER BY country, prod, year
/

This query returns the results for the newly created 2_Products product and calculates the values for Bounce and Y Box before 2_Products:

This query should not calculate the values for Bounce and Y Box before 2_Products, and 2_Products are assigned null values.

0 for numeric data

Empty string for character/string data

01-JAN-2001 for date type data

NULL for all other data types

Convert the query shown above to return a numeric value for sales even though the value for 2004 is missing. From your SQL*Plus session, execute the following script:

@i_n

The i_n.sql script contains the following:

SELECT SUBSTR(country,1,20) country, 
       SUBSTR(prod,1,15) prod, year, sales
FROM   sales_view
WHERE  country='Italy'
MODEL     IGNORE NAV  RETURN UPDATED ROWS  
  PARTITION BY (country) 
  DIMENSION BY (prod, year)
  MEASURES (sale sales)
  RULES  (
    sales['Mouse Pad', 2005] = 
    sales['Mouse Pad', 1999] + sales['Mouse Pad', 2004])
ORDER BY country, prod, year
/

Convert projected sales figures of different countries, each in their own currency, into US currency and show both figures. You need to create a table with conversion ratios of local currencies to the US dollar. From your SQL*Plus session, execute the following script:

@cre_dc

The cre_dc.sql script contains the following:

CREATE TABLE dollar_conv(country VARCHAR2(30), exchange_rate NUMBER)
/

Insert two rows into the DOLLAR_CONV table. From your SQL*Plus session, execute the following script:

@ins_dc

The ins_dc.sql script contains the following:

INSERT INTO dollar_conv VALUES('Canada', 0.75)
/ 
INSERT INTO dollar_conv VALUES('Brazil', 0.14)
/

Base the sales on the 2001 figures and project market growth by 2005 to be 22% in Canada and 34% in Brazil.

To convert the projected sales of Canada and Brazil for year 2005 to US dollars, you can use a Reference MODEL. From your SQL*Plus session, execute the following script:

@rm

The rm.sql script contains the following:

SELECT   SUBSTR(country,1,20) country, year, localsales, dollarsales
FROM     sales_view
WHERE    country IN ( 'Canada', 'Brazil')
GROUP BY country, year
MODEL  RETURN UPDATED ROWS 
  REFERENCE conv_refmodel ON (
    SELECT country, exchange_rate AS er FROM dollar_conv)
    DIMENSION BY (country) MEASURES (er) IGNORE NAV
  MAIN  main_model     
    DIMENSION BY (country, year)
    MEASURES (SUM(sale) sales, 0  localsales, 0 dollarsales) IGNORE NAV
    RULES  (
      /* assuming that sales in  Canada grow by 22% */
      localsales['Canada', 2005] = sales[cv(country), 2001] * 1.22,
        dollarsales['Canada', 2005] = sales[cv(country), 2001] * 1.22 *
        conv_refmodel.er['Canada'],
      /* assuming that economy in Brazil grows by 34% */
      localsales['Brazil', 2005] = sales[cv(country), 2001] * 1.34,
      dollarsales['Brazil', 2005] = sales['Brazil', 2001] * 1.34 * er['Brazil']
    )
/

Note the following:

• A one-dimensional reference model named CONV_REFMODEL is created on rows from the DOLLAR_CONV table and its measure EXCHANGE_RATE named ER has been referenced in the rules of the main model.

• The main model has the optional keyword MAIN at the start of its specification, giving it the alias MAIN_MODEL. The MAIN keyword makes it easier to note the start of the main model specification. MAIN_MODEL has two dimensions, COUNTRY and YEAR, whereas the reference model DOLLAR_CONV has one dimension COUNTRY.

• You can use different styles of accessing the EXCHANGE_RATE measure of the reference model: for Canada it is explicit with model_name.measure_name notation CONV_REFMODEL.ER whereas for Brazil, it is a simple measure_name reference ER. The former notation must be used to resolve any ambiguities in column names across main and reference models.

• Use the placeholder value of 0 when specifying the new measures LOCALSALES and DOLLARSALES. Other numbers would also work as placeholder value.

Growth rates in this example are hard-coded in the rules: growth rate for Canada is 22% and that of Brazil is 34%. Your rules would be much more flexible if they could work with growth values looked up from a separate table of growth rates. Such a table could cover many years and countries.

Use both exchange rate and growth rate reference models to find the projected sales in local currency and US dollars for 2002. Create a table that stores the percentage growth by country and year. From your SQL*Plus session, execute the following script:

@cre_gr

The cre_gr.sql script contains the following:

CREATE TABLE growth_rate(country     VARCHAR2(30), 
                         year        NUMBER, 
                         growth_rate NUMBER)
/

Insert rows into the GROWTH_RATE table. From your SQL*Plus session, execute the following script:

@ins_gr

The ins_gr.sql script contains the following:

INSERT INTO growth_rate VALUES('Brazil', 2002, 2.5)
/
INSERT INTO growth_rate VALUES('Brazil', 2003, 5)
/
INSERT INTO growth_rate VALUES('Canada', 2002, 3)
/
INSERT INTO growth_rate VALUES('Canada', 2003, 2.5)
/

Write a query that calculates sales for Brazil and Canada, applying the 2002 growth figures and converting the values to dollars. Use the reference model shown below in your query. From your SQL*Plus session, execute the following script:

@rm2

The rm2.sql script contains the following:

SELECT   SUBSTR(country,1,20) country, year, localsales, dollarsales
FROM     sales_view
WHERE    country IN  ('Canada','Brazil')
GROUP BY country, year
MODEL  RETURN UPDATED ROWS 
  REFERENCE conv_refmodel  ON  (
    SELECT country, exchange_rate FROM dollar_conv)
    DIMENSION BY (country c) 
    MEASURES (exchange_rate er) IGNORE NAV
  REFERENCE growth_refmodel ON  ( 
    SELECT country, year, growth_rate FROM growth_rate)
    DIMENSION BY (country c, year y) 
    MEASURES (growth_rate gr) IGNORE NAV
  MAIN  main_model 
    DIMENSION BY (country, year) 
    MEASURES (SUM(sale) sales, 0 localsales, 0  dollarsales) IGNORE NAV
    RULES  (
      localsales[FOR country IN ('Brazil', 'Canada'), 2002] = 
        sales[cv(country), 2001] *
        (100 + gr[cv(country), cv(year)])/100  ,
      dollarsales[FOR country IN ('Brazil', 'Canada'),2002] = 
        sales[cv(country), 2001] *
        (100 + gr[cv(country), cv(year)])/100  *
        er[cv(country)]
    )
/

Note the following:

• This query shows the capability of the MODEL clause in dealing with objects of different dimensionality. The Reference model CONV_REFMODEL has one dimension, whereas the Reference MODEL GROWTH_REFMODEL and the Main SQL MODEL have two dimensions.

• Dimensions in the single cell references on Reference MODELs are specified using the CV() function, thus relating the cells in Main SQL MODEL with the Reference MODEL. This specification, in effect, is performing a relational join between Main and Reference MODELs.

• By using the FOR construct, each rule can work with multiple countries, reducing the amount of coding.

• If you added the FOR construct to the YEAR dimension on the left side of the rules and CV(year) expressions to the right side, you could generalize the rule to multiple years.

You want to do financial planning for a person who earns a salary of $100,000 and has a capital gain of $15,000. His net income will be calculated as salary minus interest payments minus taxes. He pays tax-deductible interest on a loan. He also pays taxes at two rates: 28% for the salary income after interest expense is deducted, and 38% on capital gains. This person would like his interest expense to represent exactly 30% of his income. How can you calculate the taxes, interest expense, and net income that will result?

All values of this scenario are stored in a table called LEDGER. The table holds the labels for a financial item in one column and the value of the item in another. From your SQL*Plus session, execute the following script:

@cre_led

The cre_led.sql script contains the following:

CREATE TABLE  ledger  (account  VARCHAR2(20), balance  NUMBER(10,2) )
/