This illustrates the process flow in a SQL model. The SQL statement in question is:
The the flow is:
The query results input to MODEL clause are vcr 2018 sales of 9 and dvd sales for 2018 of 0
An array is defined of prod/year with 1999, 2017, and 2018 values of vcr 1, 5, 9; dvd 2, 6, 0; tv 3, 7, 1; pc 4, 8, 2
rule 1 is applied so the values for 2017 change to vcr=2, dvd=4, tv=6, pc=8
rule 2 is applied so the value for 2019vcr=11
rule 3 is applied so the value for 2019 dvd=3
MODEL clause results converted back to rows (vcr, 2018, 9), (dvd, 2018, 0), (vcr, 2019, 11), and (dvd, 2019, 3)
MODEL
DIMENSION BY (prod, year)
MEASURES (sales s)
RULES UPSERT
s[ANY, 2017] = s[CV(prod), CV(year -1)*2], -- rule 1
s[vcr, 2017] = s[vcr, 2018 + s[vcr, 2017], -- rule 2
s[dvd, 2019] = AVG(s)[CV(prod), year < 2018]) -- rule 3