SQL MODEL - RULES UPSERT SEQUENTIAL ORDER ITERATE(n)

The query below shows the Oracle SQL MODEL - RULES UPSERT SEQUENTIAL ORDER ITERATE(4) to model data for four years from 2010 through 2013. The rule as shown below applies to product - "Laptop". The string specification (upper case, initcap or lower) in the rule has to match the database data (Laptop) for the rule to work.

SQL MODEL - RULES UPSERT SEQUENTIAL ORDER ITERATE(4)

SELECT county, prod, year, a actual_val, s model_val
FROM
 (SELECT 1 county, 'Laptop' prod, 2010 year, 7550 sale FROM DUAL 
 UNION
  SELECT 1 county, 'Server' prod, 2011 year, 4500 sale FROM DUAL
 UNION
  SELECT 1 county, 'Laptop' prod, 2012 year, 8250 sale FROM DUAL
 UNION
  SELECT 2 county, 'Laptop' prod, 2010 year, 3510 sale FROM DUAL
 UNION
  SELECT 2 county, 'Laptop' prod, 2011 year, 4750 sale FROM DUAL
 UNION
  SELECT 2 county, 'Server' prod, 2012 year, 1500 sale FROM DUAL
 UNION
  SELECT 2 county, 'Laptop' prod, 2013 year, 9475 sale FROM DUAL
 UNION
  SELECT 3 county, 'Server' prod, 2010 year, 1960 sale FROM DUAL)
  MODEL
    PARTITION BY (county)
    DIMENSION BY (prod, year)
    MEASURES (sale s, sale a)
    IGNORE NAV
    UNIQUE DIMENSION
    RULES UPSERT SEQUENTIAL ORDER ITERATE(4)
    (s['Laptop', 2010 + ITERATION_NUMBER] = 
                         s['Laptop', 2011 + ITERATION_NUMBER],
     a['Laptop', 2010 + ITERATION_NUMBER] =
                         a['Laptop', 2010 + ITERATION_NUMBER])
  ORDER BY county, prod, year;

SQL Model Query Output
SQL MODEL - RULES UPSERT SEQUENTIAL ORDER ITERATE(4)

SQL Model Query Output Analysis

For county 1,
Iteration(0)  2010 (Laptop) = 2011 (laptop) = 0
Iteration(1)  2011 (Laptop) = 2012 (laptop) = 8250
Iteration(2)  2012 (Laptop) = 2013 (laptop) = 0
Iteration(3)  2013 (Laptop) = 2014 (laptop) = 0

The same rules are applied for county 2 and 3. since there is no laptop data for county 3, all four years have 0 value as shown in query output above. The IGNORE NAV converts the null values to 0. The value of ITERATION_NUMBER starts with 0 and in this query the last value is 3.

Available sales data in above query table (fact) with all dimension using cartesian join

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 03, 2014

74081