Reports: matrix report with percentage totals

...previous

On my previous note on reports I was asked for a query, that would return percentage ratios for vertical and horizontal subtotals (at least as I have understood ).

So in this case our report draft could be represented in the following way:

| Departments names block | Total
------------+----------+---------+----------+---------------
| | | |
Cities -+- Detailed data -+- Ratio (%) of totals
Names | for each | for every city
block -+- city & department -+- to the very total
| | | |
------------+----------+---------+----------+----------------
Total | Ratio (%) of totals for every | Overall total
| department to the very total | (100%)

As I have not very much spare time I decided to put here only both solutions (non-model and model) without any explanation, and if you have any doubts/questions - you can put it as a comment, then I will elaborate on this.

Input data you can still find here.

Non-Model solution:

SQL> select nvl(city, 'TOTAL') as city_,
2 nvl2(city,to_char(sum(decode(dep, 'DEP1', sal, 0))),round(sum(decode(dep, 'DEP1', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep1,
3 nvl2(city,to_char(sum(decode(dep, 'DEP2', sal, 0))),round(sum(decode(dep, 'DEP2', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep2,
4 nvl2(city,to_char(sum(decode(dep, 'DEP3', sal, 0))),round(sum(decode(dep, 'DEP3', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep3,
5 nvl2(city,to_char(sum(decode(dep, 'DEP4', sal, 0))),round(sum(decode(dep, 'DEP4', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep4,
6 round(2*100*ratio_to_report(sum(decode(flag, 1, sal, 3, sal, 0))) over (),1)||'%' total
7 from (
8 select city, dep, sum(sales) sal, grouping_id(city, dep) flag
9 from t
10 where trunc(year, 'y') = date '2007-01-01'
11 group by cube(city, dep))
12 group by city
13 order by nvl2(city, 0, 1), total desc
14 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ----------- ----------- ----------- ---------- ----------
Moscow 762 657 1020 487 87.9%
Omsk 34 213 156 0 12.1%
TOTAL 23.9% 26.1% 35.3% 14.6% 100%

SQL>

Model solution:

SQL> select city,dep1,dep2,dep3,dep4,total from t
2 where trunc(year,'y') = date '2007-01-01'
3 model
4 return updated rows
5 dimension by (city, dep)
6 measures(to_char(sales) dep1, to_char(sales) dep2, to_char(sales) dep3, to_char(sales) dep4, to_char(sales) total)
7 rules
8 upsert all
9 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
10 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
11 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
12 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
13 total['TOTAL',null]=sum(dep1)[any,null]+sum(dep2)[any,null]+sum(dep3)[any,null]+sum(dep4)[any,null],
14 dep1['TOTAL',null]=round(sum(dep1)[any,null]*100/total['TOTAL',null],1)||'%',
15 dep2['TOTAL',null]=round(sum(dep2)[any,null]*100/total['TOTAL',null],1)||'%',
16 dep3['TOTAL',null]=round(sum(dep3)[any,null]*100/total['TOTAL',null],1)||'%',
17 dep4['TOTAL',null]=round(sum(dep4)[any,null]*100/total['TOTAL',null],1)||'%',
18 total[city<>'TOTAL',null]=round((dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()])*100/total['TOTAL',null],1)||'%',
19 total['TOTAL',null]='100%'
20 )
21 /

CITY DEP1 DEP2 DEP3 DEP4 TOTAL
------ ----------- ----------- ---------- ---------- ----------
Moscow 762 657 1020 487 87.9%
Omsk 34 213 156 0 12.1%
TOTAL 23.9% 26.1% 35.3% 14.6% 100%

SQL>

PS
Added after the comment.

"A good problem description worth half a solution" :)
So again I'm not sure whether this is needed or not, but if you want to calculate only percentages in matrix report even for detailed data you can use the following:

So the scheme of our report would be:

| Departments names block | Total
------------+----------+---------+----------+---------------
| | | |
Cities -+- Ratio (%) of detailed -+- Ratio (%) of totals
Names | data for each city & | for every city
block -+- department to total -+- to the very total
| | | |
------------+----------+---------+----------+----------------
Total | Ratio (%) of totals for every | Overall total
| department to the very total | (100%)

Non-model solution:

SQL> select nvl(city, 'TOTAL') as city_,
2 round(sum(decode(dep, 'DEP1', sal, 0)), 2) || '%' dep1,
3 round(sum(decode(dep, 'DEP2', sal, 0)), 2) || '%' dep2,
4 round(sum(decode(dep, 'DEP3', sal, 0)), 2) || '%' dep3,
5 round(sum(decode(dep, 'DEP4', sal, 0)), 2) || '%' dep4,
6 round(sum(decode(flag, 1, sal, 3, sal, 0)), 2) || '%' total
7 from (select city, dep, sum(rtr) sal, grouping_id(city, dep) flag
8 from (select t.*, 100 * ratio_to_report(sales) over() rtr
9 from t
10 where trunc(year, 'y') = date '2007-01-01')
11 group by cube(city, dep))
12 group by city
13 order by nvl2(city, 0, 1), total desc
14 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ------------- ------------ ------------- ------------- -----------
Moscow 22.89% 19.74% 30.64% 14.63% 87.89%
Omsk 1.02% 6.4% 4.69% 0% 12.11%
TOTAL 23.91% 26.13% 35.33% 14.63% 100%

SQL>

Model solution:

SQL> select city,
2 round(dep1,2)||'%' dep1,
3 round(dep2,2)||'%' de2,
4 round(dep3,2)||'%' de3,
5 round(dep4,2)||'%' de3,
6 round(total,2)||'%' total from (
7 select t.*, 100*ratio_to_report(sales) over () rtr from t
8 where trunc(year,'y') = date '2007-01-01')
9 model
10 return updated rows
11 dimension by (city, dep)
12 measures(rtr dep1, rtr dep2, rtr dep3, rtr dep4, rtr total)
13 rules
14 upsert all
15 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
16 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
17 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
18 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
19 dep1['TOTAL',null]=sum(dep1)[any,null],
20 dep2['TOTAL',null]=sum(dep2)[any,null],
21 dep3['TOTAL',null]=sum(dep3)[any,null],
22 dep4['TOTAL',null]=sum(dep4)[any,null],
23 total[any,null]=dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()]
24 )
25 /

CITY DEP1 DE2 DE3 DE3 TOTAL
------ ----------- ------------ ----------- ------------ -----------
Moscow 22.89% 19.74% 30.64% 14.63% 87.89%
Omsk 1.02% 6.4% 4.69% 0% 12.11%
TOTAL 23.91% 26.13% 35.33% 14.63% 100%

SQL>

Reports: matrix report

...previous

After a while I decided to write one more note on reports. Now it would be about matrix reports.

The input data you can take here.
As you can find out it is information on sales for two years (2006, 2007) of four departments (DEP1, DEP2, DEP3, DEP4) in two cities (Omsk, Moscow).
Imagine we need to get sales only for the year 2007, but present it as a matrix report.
Horizontally information on departments should be placed, vertically it should be information on cities. In the last row and column of a table we need to show totals on departments and cities consequently. At the bottom in the most right column we need to place overall total sales.
So the general draft of our report would look like:

| Departments names block | Total
------------+----------+---------+---------+-------------
| | | |
Cities -+- Detailed data -+- Totals
Names | for each | for
block -+- city & department -+- every city
| | | |
------------+----------+---------+---------+-------------
Total | Totals for every department | Overall total

Non-Model solution:
To build such a report we need to get subtotals both on departments and cities. We also need to get the overall total of all sales for the year 2007. For such requirments we need to use CUBE Extension to GROUP BY.
So the whole query would be:

SQL> select nvl(city, 'TOTAL') as city_,
2 sum(decode(dep, 'DEP1', sal, 0)) dep1,
3 sum(decode(dep, 'DEP2', sal, 0)) dep2,
4 sum(decode(dep, 'DEP3', sal, 0)) dep3,
5 sum(decode(dep, 'DEP4', sal, 0)) dep4,
6 sum(decode(flag, 1, sal, 3, sal, 0)) total
7 from (select city, dep, sum(sales) sal, grouping_id(city, dep) flag
8 from t
9 where trunc(year, 'y') = date '2007-01-01'
10 group by cube(city, dep))
11 group by city
12 order by nvl2(city, 0, 1), total desc
13 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ---------- ---------- ---------- ---------- ----------
Moscow 762 657 1020 487 2926
Omsk 34 213 156 0 403
TOTAL 796 870 1176 487 3329

SQL>

This is the report we were looking for. So we can easily see that Dep3 sales in Omsk in the year 2007 were 156, while total sales of Dep3 were 1176, and total sales in Omsk were 403. Overall total sales were 3329.

Model solution:
Using model clause we need to have a tricky RETURN UPDATED ROWS clause. So that we would not group data in the outer query.
Also we will use UPSERT ALL command for all rules. What is this and how it can be used you can read in one of my previous notes. So the query would be:

SQL> select city,dep1,dep2,dep3,dep4,total from t
2 where trunc(year,'y') = date '2007-01-01'
3 model
4 return updated rows
5 dimension by (city, dep)
6 measures(sales dep1, sales dep2, sales dep3, sales dep4, sales total)
7 rules
8 upsert all
9 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
10 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
11 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
12 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
13 dep1['TOTAL',null]=sum(dep1)[any,null],
14 dep2['TOTAL',null]=sum(dep2)[any,null],
15 dep3['TOTAL',null]=sum(dep3)[any,null],
16 dep4['TOTAL',null]=sum(dep4)[any,null],
17 total[any,null]=dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()]
18 )
19 /

CITY DEP1 DEP2 DEP3 DEP4 TOTAL
------ ---------- ---------- ---------- ---------- ----------
Moscow 762 657 1020 487 2926
Omsk 34 213 156 0 403
TOTAL 796 870 1176 487 3329

SQL>

Notice that we have put as many measures for departments as we have in our data. So we are doing a column to row transformation, but as it was in the non-model solution it is static transformation. So we need to know how many columns we need to get beforehand.

As you know the model clause is working by executing a bunch of rules (one by one in a sequential order in our case).

We are placing the following groups of rules:
First we create new rows with department equal NULL, where we place sales of each department in each city.
Then we place rules for creating a TOTAL row, where we find the total sales for each department.
Finaly we write a rule (column TOTAL) - to calculate totals for every city and to find the overall total also.
So in the end we get the same results as with non-model solution.

to be continued...

Reports: getting total of all children values in a tree

... previous

Well, actually, this note is a continuation of a model series on reports.
So the structure of an article is the same: the problem description -> non-model solution -> model clause solution.

But also this note is very similar to summation of tree values, I've written a little bit earlier.

Input data:

So let's get started.
Our input for this post would be a table t from here and an additional table t_hrchy with a hierarchical structure of departments:

SQL> drop table t_hrchy;

Table dropped

SQL>
SQL> create table t_hrchy as (select 'DEP2' dep, 'DEP1' dep_par from dual union all
2 select 'DEP3' dep, 'DEP2' dep_par from dual union all
3 select 'DEP4' dep, 'DEP3' dep_par from dual)
4 /

Table created

SQL> select * from t_hrchy;

DEP DEP_PAR
---- -------
DEP2 DEP1
DEP3 DEP2
DEP4 DEP3

SQL>

So this table tells us that DEP1 is a parent department for DEP2, DEP2 is a parent department for DEP3 and DEP3 is a parent department for DEP4.

Problem:

Now our task would be to create a report where in one column would be sales of a particular department output and in another column we need to get the sales of a current department and for all the departments that are under the current one in the organization structure.
The data should be consolidated only inside the same year and inside the same city.

Non-Model solution:

When the matter concerns the hierarchical structure the first thing that pops up in our head is hierarchical queries with CONNECT BY clause.

Elaborating this a little bit we can use the following query proposed by Rob van Wijk here or here:

SQL> select to_char(year,'yyyy') year,
2 city,
3 t.dep,
4 th.dep_par,
5 sales,
6 sum(connect_by_root sales) tot_sales
7 from t, t_hrchy th
8 where t.dep = th.dep(+)
9 connect by t.dep = prior dep_par
10 and prior year = year
11 and prior city = city
12 group by city, year, t.dep, dep_par, sales
13 order by 1,2,3;

YEAR CITY DEP DEP_PAR SALES TOT_SALES
---- ------ ---- ------- ---------- ----------
2006 Moscow DEP1 562 2254
2006 Moscow DEP2 DEP1 457 1692
2006 Moscow DEP3 DEP2 890 1235
2006 Moscow DEP4 DEP3 345 345
2006 Omsk DEP1 23 264
2006 Omsk DEP2 DEP1 154 241
2006 Omsk DEP3 DEP2 87 87
2007 Moscow DEP1 762 2926
2007 Moscow DEP2 DEP1 657 2164
2007 Moscow DEP3 DEP2 1020 1507
2007 Moscow DEP4 DEP3 487 487
2007 Omsk DEP1 34 403
2007 Omsk DEP2 DEP1 213 369
2007 Omsk DEP3 DEP2 156 156

14 rows selected

SQL>

Lets see what this query actually does.
First we use an outer join of two tables - so for each row we get a parent department name.

Then we build a hierarchy by using

connect by t.dep = prior dep_par

Mention that hierarchy is built in the backward direction starting from the children and going up to their parents.

Also we add in the connect by clause the following:

and prior year = year
and prior city = city

So we build hierarchy separartely for different years and cities.

By using the operator

connect_by_root sales

in every row we find a value for sales of the root elements for this or that thread in a hierarchy. As we didn't specify any START WITH condition - the hierarchy is built starting from all the departments present in the data.
For example, let's look what would happen inside the partition for 2007 year Moscow city:

SQL> select to_char(year,'yyyy') year,
2 city,
3 t.dep,
4 th.dep_par,
5 sales,
6 connect_by_root sales root_sales,
7 level,
8 sys_connect_by_path(t.dep,'/') hrchy_path
9 from t, t_hrchy th
10 where t.dep = th.dep(+)
11 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
12 and t.city='Moscow'
13 connect by t.dep = prior dep_par
14 and prior year = year
15 and prior city = city
16 /

YEAR CITY DEP DEP_PAR SALES ROOT_SALES LEVEL HRCHY_PATH
---- ------ ---- ------- ---------- ---------- ---------- ----------------------------
2007 Moscow DEP1 762 762 1 /DEP1
2007 Moscow DEP2 DEP1 657 657 1 /DEP2
2007 Moscow DEP1 762 657 2 /DEP2/DEP1
2007 Moscow DEP3 DEP2 1020 1020 1 /DEP3
2007 Moscow DEP2 DEP1 657 1020 2 /DEP3/DEP2
2007 Moscow DEP1 762 1020 3 /DEP3/DEP2/DEP1
2007 Moscow DEP4 DEP3 487 487 1 /DEP4
2007 Moscow DEP3 DEP2 1020 487 2 /DEP4/DEP3
2007 Moscow DEP2 DEP1 657 487 3 /DEP4/DEP3/DEP2
2007 Moscow DEP1 762 487 4 /DEP4/DEP3/DEP2/DEP1

10 rows selected

SQL>

As you see DEP1 is met four times:
- Firstly it stands for itself (level=1).
- Then we begin to build hierarchy starting from DEP2 and DEP1 is met as a "child" (remember we build a hierarchy in the opposite direction) for DEP2 (level=2)
- The next time we meet it as a "grandchild" for DEP3 (level=3)
- And finally as a "great-grandchild" for DEP4 (level=4).

In each row we have a value of sales for the root department. So if we group data by department and put a sum() aggregate function on connect_by_root(sales) column we get the sales of DEP1 and all the sales of all departments that are under DEP1 in one row.
The same stuff will happen to all the other departments:

SQL> select to_char(year,'yyyy') year,
2 city,
3 t.dep,
4 th.dep_par,
5 sales,
6 sum(connect_by_root sales) tot_salesh
7 from t, t_hrchy th
8 where t.dep = th.dep(+)
9 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
10 and t.city='Moscow'
11 connect by t.dep = prior dep_par
12 and prior year = year
13 and prior city = city
14 group by year,city,t.dep,th.dep_par,sales
15 order by 1,2,3
16 /

YEAR CITY DEP DEP_PAR SALES TOT_SALESH
---- ------ ---- ------- ---------- ----------
2007 Moscow DEP1 762 2926
2007 Moscow DEP2 DEP1 657 2164
2007 Moscow DEP3 DEP2 1020 1507
2007 Moscow DEP4 DEP3 487 487

SQL>

Model Solution

Now we are proceeding and will solve the same problem using Model clause without any connect by's.

To get less data to be outputed at every step - let's restrict our data to 2007 year and Moscow city only as we did in Non-Model solution:

SQL> select * from t, t_hrchy th
2 where t.dep = th.dep(+)
3 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
4 and t.city='Moscow'
5 order by 3,1,2
6 /

CITY DEP YEAR SALES DEP DEP_PAR
------ ---- ----------- ---------- ---- -------
Moscow DEP1 31.12.2007 762
Moscow DEP2 31.12.2007 657 DEP2 DEP1
Moscow DEP3 31.12.2007 1020 DEP3 DEP2
Moscow DEP4 31.12.2007 487 DEP4 DEP3

SQL>

Let's think of what woud be placed in PARTITION BY part of our model. Remeber that partitions should be absolutely independent one of another.
Yes - we would put YEAR and CITY there. As a task instructs us to treat sales of different years and cities separately.
In dimension we would place DEP and DEP_PART columns.
And in measures - we'll put SALES, of course. And create one more measure called TOT_SALES with initial value of zero.

SQL> select * from t, t_hrchy th
2 where t.dep = th.dep(+)
3 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
4 and t.city='Moscow'
5 model
6 partition by (year,city)
7 dimension by (t.dep dep,dep_par)
8 measures(sales, 0 tot_sales)
9 ()
10 order by 1,2,3
11 /

YEAR CITY DEP DEP_PAR SALES TOT_SALES
----------- ------ ---- ------- ---------- ----------
31.12.2007 Moscow DEP1 762 0
31.12.2007 Moscow DEP2 DEP1 657 0
31.12.2007 Moscow DEP3 DEP2 1020 0
31.12.2007 Moscow DEP4 DEP3 487 0

SQL>

Now we are going to write a rule for calculating the tot_sales.
For any cell in our model the tot_sales should be equal to a sum of sales of the current department and all the tot_sales of all departments under the current.
We can write it as:

tot_sales[any,any] = sales[CV(),CV()]+nvl(sum(tot_sales)[any,CV(dep)],0)

BTW it would be correct in case when the tot_sales of the departments under the current one already included all the tot_sales of the departments under them.

So what is important here is the ORDER in what the rules are executed.
If we don't specify anything about the order of execution to our model it uses the default SEQUENTIAL ORDER and can easily throw out an error for us:

SQL> select * from t, t_hrchy th
2 where t.dep = th.dep(+)
3 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
4 and t.city='Moscow'
5 model
6 partition by (year,city)
7 dimension by (t.dep dep,dep_par)
8 measures(sales, 0 tot_sales)
9 rules
10 (tot_sales[any,any] = sales[CV(),CV()]+nvl(sum(tot_sales)[any,CV(dep)],0))
11 order by 1,2,3
12 /

...

ORA-32637: Self cyclic rule in sequential order MODEL

SQL>

But putting AUTOMATIC ORDER causes all rules to be evaluated in an order based on their logical dependencies.
Let's check:

SQL> select * from t, t_hrchy th
2 where t.dep = th.dep(+)
3 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
4 and t.city='Moscow'
5 model
6 partition by (year,city)
7 dimension by (t.dep dep,dep_par)
8 measures(sales, 0 tot_sales)
9 rules automatic order
10 (tot_sales[any,any] = sales[CV(),CV()]+nvl(sum(tot_sales)[any,CV(dep)],0))
11 order by 1,2,3
12 /

YEAR CITY DEP DEP_PAR SALES TOT_SALES
----------- ------ ---- ------- ---------- ----------
31.12.2007 Moscow DEP1 762 2926
31.12.2007 Moscow DEP2 DEP1 657 2164
31.12.2007 Moscow DEP3 DEP2 1020 1507
31.12.2007 Moscow DEP4 DEP3 487 487

SQL>

Voilà! This is exactly what we were looking for.

Let's find a detailed description of what's happening when we don't use AUTOMATIC ORDER and what's changing when we begin to use it.
First of all, the equivalent of our single rule would be actually a list of four rules for every row (unique set of dimension values) in our data:

tot_sales['DEP1', null ]=sales['DEP1', null ]+nvl(sum(tot_sales)[any,'DEP1'],0),
tot_sales['DEP2','DEP1']=sales['DEP2','DEP1']+nvl(sum(tot_sales)[any,'DEP2'],0),
tot_sales['DEP3','DEP2']=sales['DEP3','DEP2']+nvl(sum(tot_sales)[any,'DEP3'],0),
tot_sales['DEP4','DEP3']=sales['DEP4','DEP3']+nvl(sum(tot_sales)[any,'DEP4'],0)

But using SEQUENTIAL ORDER doesn't guarantee us any order of these rules execution. Because as it stated in the documetation: SEQUENTIAL ORDER: This causes rules to be evaluated in the order they are written. This is the default.
But we have only one rule in our model. So the detailed rules can be run in any order:
for example first we find tot_sales['DEP1', null ] and then tot_sales['DEP2','DEP1'], or vice versa. And that is the problem, because this two possibilities will give us different results in the output.

What actually ORA-32637 stays for is:
Cause: A self-cyclic rule was detected in the sequential order MODEL. Sequential order MODELs cannot have self cyclic rules to guarantee that the results do not depend on the order of evaluation of the cells that are updated or upserted.
Action: Use ordered rule evaluation for this rule.

So if use order by DEP we shouldn't have such an error, let's try:

SQL> select * from t, t_hrchy th
2 where t.dep = th.dep(+)
3 and trunc(t.year,'y')=to_date('01.01.2007','dd.mm.yyyy')
4 and t.city='Moscow'
5 model
6 partition by (year,city)
7 dimension by (t.dep dep,dep_par)
8 measures(sales, 0 tot_sales)
9 rules
10 (tot_sales[any,any] order by dep = sales[CV(),CV()]+nvl(sum(tot_sales)[any,CV(dep)],0))
11 order by 1,2,3
12 /

YEAR CITY DEP DEP_PAR SALES TOT_SALES
----------- ------ ---- ------- ---------- ----------
31.12.2007 Moscow DEP1 762 762
31.12.2007 Moscow DEP2 DEP1 657 657
31.12.2007 Moscow DEP3 DEP2 1020 1020
31.12.2007 Moscow DEP4 DEP3 487 487

SQL>

Yes, the query returns no error - because we explicitly defined the order of rules execution. But as you see we have wrong results. Because actually in our case we should order by dep DESC! You can try it yourself.
While we don't know what are the dependences in the hierarchy data beforehand - let's give Oracle to decide - what are the dependencies between cells and simply use AUTOMATIC ORDER:

SQL> select * from t, t_hrchy th
2 where t.dep = th.dep(+)
3 model
4 partition by (to_char(year,'yyyy') year,city)
5 dimension by (t.dep dep,dep_par)
6 measures(sales, 0 tot_sales)
7 rules automatic order
8 (tot_sales[any,any] = sales[CV(),CV()]+nvl(sum(tot_sales)[any,CV(dep)],0))
9 order by 1,2,3
10 /

YEAR CITY DEP DEP_PAR SALES TOT_SALES
---- ------ ---- ------- ---------- ----------
2006 Moscow DEP1 562 2254
2006 Moscow DEP2 DEP1 457 1692
2006 Moscow DEP3 DEP2 890 1235
2006 Moscow DEP4 DEP3 345 345
2006 Omsk DEP1 23 264
2006 Omsk DEP2 DEP1 154 241
2006 Omsk DEP3 DEP2 87 87
2007 Moscow DEP1 762 2926
2007 Moscow DEP2 DEP1 657 2164
2007 Moscow DEP3 DEP2 1020 1507
2007 Moscow DEP4 DEP3 487 487
2007 Omsk DEP1 34 403
2007 Omsk DEP2 DEP1 213 369
2007 Omsk DEP3 DEP2 156 156

14 rows selected

SQL>

to be continued...

Reports: forecasting

... previous

We carry on with our reports (the needed input data).
Now what we need is to make a little forecast. We are requested to find out what the approximate sales for the next year would be.

The growth rate is determined as a ratio of current year sales (2007) to the previous year sales (2006), but it should be calculated separately for different cities.
So to find the growth rate for each row - we need to find total sales for the particular city for the current year and divide it on the total sales for the same city for the previous year.

Non-Model solution
Without using model - we can find the growth rate in a scalar subquery:

select sum(decode(to_char(year, 'yyyy'), '2007', sales)) /
sum(decode(to_char(year, 'yyyy'), '2006', sales))
from t
where city = t1.city

where t1.city should be a reference to the main outer query.
If we put this scalar subquery directly into the query we already had, we get the following result:

SQL> select city,
2 dep,
3 sum(sales) sales_2007,
4 round(sum(sales) *
5 (select sum(decode(to_char(year, 'yyyy'), '2007', sales)) /
6 sum(decode(to_char(year, 'yyyy'), '2006', sales))
7 from t
8 where city = t1.city),
9 2) sales_2008_forecast
10 from t t1
11 where trunc(year, 'y') = to_date('01.01.2007', 'dd.mm.yyyy')
12 group by rollup(city, dep)
13 /

CITY DEP SALES_2007 SALES_2008_FORECAST
------ ---- ---------- -------------------
Omsk DEP1 34 51,9
Omsk DEP2 213 325,15
Omsk DEP3 156 238,14
Omsk 403 615,19
Moscow DEP1 762 989,18
Moscow DEP2 657 852,88
Moscow DEP3 1020 1324,1
Moscow DEP4 487 632,19
Moscow 2926 3798,35
3329

10 rows selected

SQL>

As you see - we don't get a total for year 2008. This happens because city value in this total row is equal to NULL. And while we calculating the growth rate for particular cities - we get no rate at all for this row. So as a result we get NULL.

The workaround here would be first to get forecasting for detailed sales and then group with ROLLUP the sales for 2007 year and also for the predictable 2008 year.

So it would look like the following:

SQL> select city,
2 dep,
3 sum(sales) sales_2007,
4 sum(sales2) sales_2008_forecast,
5 round(100 * (sum(sales2) / sum(sales) - 1), 2) || '%' growth_rate
6 from (select t0.*,
7 round(sales *
8 (select sum(decode(to_char(year, 'yyyy'), '2007', sales)) /
9 sum(decode(to_char(year, 'yyyy'), '2006', sales))
10 from t
11 where city = t0.city),
12 2) sales2
13 from t t0) t1
14 where trunc(year, 'y') = to_date('01.01.2007', 'dd.mm.yyyy')
15 group by rollup(city, dep)
16 /

CITY DEP SALES_2007 SALES_2008_FORECAST GROWTH_RATE
------ ---- ---------- ------------------- ---------------
Omsk DEP1 34 51,9 52.65%
Omsk DEP2 213 325,15 52.65%
Omsk DEP3 156 238,14 52.65%
Omsk 403 615,19 52.65%
Moscow DEP1 762 989,18 29.81%
Moscow DEP2 657 852,88 29.81%
Moscow DEP3 1020 1324,1 29.81%
Moscow DEP4 487 632,19 29.81%
Moscow 2926 3798,35 29.81%
3329 4413,54 32.58%

10 rows selected

SQL>

Now all the totals are where they should be. And we added an extra column with growth rate. So we can see, that sales in Omsk grew up ~53% and in Moscow increased ~30%. The average growth rate from 2006 until 2007 years was ~33%.

Model solution #1
With the model solution instead of scalar subquery - we can use reference model clause.
We put the following query into the reference model:

SQL> select city, to_char(year,'yyyy') y, sum(sales) sum_sales from t group by city,year;

CITY Y SUM_SALES
------ ---- ----------
Moscow 2006 2254
Omsk 2006 264
Moscow 2007 2926
Omsk 2007 403

SQL>

And then we'll use the values from that resultset.

SQL> select city,dep,sales,sales_fc,round(100*(growth-1),2)||'%' growth_rate
2 from t where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
3 model
4 reference r on (select city, to_char(year,'yyyy') y, sum(sales) sum_sales from t group by city,year)
5 dimension by (y, city)
6 measures(sum_sales)
7 main m
8 dimension by (0 total,city, dep)
9 measures(sales, 0 sales_FC, 0 growth)
10 rules upsert all
11 (sales[1,any,null]=sum(sales)[0,CV(),any],
12 sales[1,null,null]=sum(sales)[0,any,any],
13 growth[any,any,any]=r.sum_sales['2007',CV(city)]/r.sum_sales['2006',CV(city)],
14 sales_FC[any,any,any]=round(sales[CV(),CV(),CV()]*growth[CV(),CV(),CV()],2))
15 order by 2,3;

CITY DEP SALES SALES_FC GROWTH_RATE
------ ---- ---------- ---------- -----------------------------------------
Omsk DEP1 34 51,9 52.65%
Moscow DEP1 762 989,18 29.81%
Omsk DEP2 213 325,15 52.65%
Moscow DEP2 657 852,88 29.81%
Omsk DEP3 156 238,14 52.65%
Moscow DEP3 1020 1324,1 29.81%
Moscow DEP4 487 632,19 29.81%
Omsk 403 615,19 52.65%
Moscow 2926 3798,35 29.81%
3329 %

10 rows selected

SQL>

So we put our query into the reference clause, so that we can use it's cell values in the main part.
We created a new measure growth and in the rules part wrote:

growth[any,any,any]=r.sum_sales['2007',CV(city)]/r.sum_sales['2006',CV(city)]

So for each cell we find a growth rate for the current row (current city) by dividing summary sales of the current year for this particular city to the same of the previous year.
The next our step (rule) is finding the predicted sales for the next year:

sales_FC[any,any,any]=round(sales[CV(),CV(),CV()]*growth[CV(),CV(),CV()],2)

And we are facing the same problem we had in the Non-Model part. We can't define any city in the total row - so we can't find a growth rate for this row.
As you already know - we can easily write a rule to find the total sales for the next year as a sum of other cells:

SQL> select city,dep,sales,sales_fc,round(100*(growth-1),2)||'%' growth_rate
2 from t where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
3 model
4 reference r on (select city, to_char(year,'yyyy') y, sum(sales) sum_sales from t group by city,year)
5 dimension by (y, city)
6 measures(sum_sales)
7 main m
8 dimension by (0 total,city, dep)
9 measures(sales, 0 sales_FC, 0 growth)
10 rules upsert all
11 (sales[1,any,null]=sum(sales)[0,CV(),any],
12 sales[1,null,null]=sum(sales)[0,any,any],
13 sales_FC[any,city is not null,any]=round(sales[CV(),CV(),CV()]*r.sum_sales['2007',CV(city)]/r.sum_sales['2006',CV(city)],2),
14 sales_FC[1,null,null]=sum(sales_FC)[1,any,null],
15 growth[any,any,any]=sales_FC[CV(),CV(),CV()]/sales[CV(),CV(),CV()])
16 order by 1,2;

CITY DEP SALES SALES_FC GROWTH_RATE
------ ---- ---------- ---------- ------------------
Moscow DEP1 762 989,18 29.81%
Moscow DEP2 657 852,88 29.81%
Moscow DEP3 1020 1324,1 29.81%
Moscow DEP4 487 632,19 29.81%
Moscow 2926 3798,35 29.81%
Omsk DEP1 34 51,9 52.65%
Omsk DEP2 213 325,15 52.65%
Omsk DEP3 156 238,14 52.65%
Omsk 403 615,19 52.65%
3329 4413,54 32.58%

10 rows selected

SQL>

You see that we changed the order of the rules and the rules themselves (that is because we want to find the growth rate for the overall total row, and hence we would find growth rates AFTER we get overall total sales value).
First we find the forecasting sales for the next year for all the rows excluding the very total:

sales_FC[any,city is not null,any]=
round(sales[CV(),CV(),CV()]*r.sum_sales['2007',CV(city)]/r.sum_sales['2006',CV(city)],2)

Then we find this total by summing up all the city subtotals:

sales_FC[1,null,null]=sum(sales_FC)[1,any,null]

And finally we get the growth rate by dividing sales_FC (forecast) on the actual sales (current year sales):

growth[any,any,any]=sales_FC[CV(),CV(),CV()]/sales[CV(),CV(),CV()]

After all this is the same result we had in the Non-Model solution.

Model solution #2
Well, in both solutions (Non-Model & Model #1) we accessed the table twice. First to get the main result and second one - to get the growth rate.

Now we are going to access table only once by using extra dimension in Model and refusing from the reference model clause.

SQL> select city,dep,sales,sales_fc,round(100*(growth-1),2)||'%' growth_rate from t
2 model
3 return updated rows
4 dimension by (0 total,city, dep, to_char(year,'yyyy') year)
5 measures(sales, sales sales_FC,0 growth)
6 rules upsert all
7 (sales[1,any,null,'2007']=sum(sales)[0,CV(),any,CV()],
8 sales[1,null,null,'2007']=sum(sales)[0,any,any,CV()],
9 sales_FC[any,any,any,'2007']=round(sales[CV(),CV(),CV(),CV()]*
10 sales[1,CV(),null,CV()]/sum(sales)[0,CV(),any,'2006'],2),
11 sales_FC[1,null,null,'2007']=sum(sales_FC)[1,any,null,'2007'],
12 growth[any,any,any,'2007']=sales_FC[CV(),CV(),CV(),CV()]/sales[CV(),CV(),CV(),CV()])
13 order by 1,2
14 /

CITY DEP SALES SALES_FC GROWTH_RATE
------ ---- ---------- ---------- -----------------------------------------
Moscow DEP1 762 989,18 29.81%
Moscow DEP2 657 852,88 29.81%
Moscow DEP3 1020 1324,1 29.81%
Moscow DEP4 487 632,19 29.81%
Moscow 2926 3798,35 29.81%
Omsk DEP1 34 51,9 52.65%
Omsk DEP2 213 325,15 52.65%
Omsk DEP3 156 238,14 52.65%
Omsk 403 615,19 52.65%
3329 4413,54 32.58%

10 rows selected

SQL>

What you should mention first is that we removed

where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')

So now all the data (for all years: 2006 and 2007) is pumped into model clause.

And you probably didn't miss that we put RETURN UPDATED ROWS, so only rows where cells were updated will be returned in the resultset.
That's how we can avoid additional outer WHERE clause for our MODEL: we update only rows that are corresppondent to 2007 year, but not the 2006.

Another thing changed is we added a dimension

to_char(year,'yyyy') year

So now we can reference to sales of a particular year by putting it in the format like [...,'YYYY'].

And the final amendment - would be adding a value for a new dimension in every cell reference ([<total_dim>,<city_dim>,<dep_dim>,'2007']). If we put it on the left side of a formula we can put just CV() on the right side in the place for <year_dim>.

to be continued ...

Reports: ratios & percentages

... previous

Let's continue with our spreadsheet report (input data you can find here).

Imagine that we want to add a column that would represent sales percentage of each department in the total of it's city sales. So we need to know how much every department contributes to it's region sales.

Non-model solution:

Developing the group by solution with rollup - we could use RATIO_TO_REPORT function for that purpose.

Our query would look like:

SQL> select city, dep, sum(sales),
2 decode(grouping_id(city, dep),
3 0,
4 round(2 * 100 * ratio_to_report(sum(sales))
5 over(partition by city),
6 2) || '%') perc
7 from t
8 where trunc(year, 'y') = to_date('01.01.2007', 'dd.mm.yyyy')
9 group by rollup(city, dep)
10 /

CITY DEP SUM(SALES) PERC
------ ---- ---------- ---------
Moscow DEP1 762 26.04%
Moscow DEP2 657 22.45%
Moscow DEP3 1020 34.86%
Moscow DEP4 487 16.64%
Moscow 2926
Omsk DEP1 34 8.44%
Omsk DEP2 213 52.85%
Omsk DEP3 156 38.71%
Omsk 403
3329

10 rows selected

SQL>

Let's take a closer look.
First of all we use GROUPING_ID function - to understand when the data is detailed (comparing it to 0). When it is detailed - we return the ratio of the value in the current row to the total sales of the region. Otherwise we return NULL.
But we need to remember that analytic functions are executed after the grouping has been done. So when our ratio_to_report would be applied - we will have doubled sales for each city: on the one hand - the sales of each department, and on the other hand, the sum of all departments sales (as a city subtotal) will also influence our ratio_to_report.
That's why we multiply the function by 2, so we get correct results.
With multiplying by 100 we get percentage values.

Model solution
For getting the same result with Model clause - first of all we need to add a new measure:

cast(null as varchar2(7)) PERC

So it would be our percentage column in the result set:

SQL> select * from t where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
2 model
3 dimension by (0 total,city, dep)
4 measures(sales, cast(null as varchar2(7)) perc)
5 rules upsert all
6 (sales[1,any,null]=sum(sales)[0,CV(),any],
7 sales[1,null,null]=sum(sales)[0,any,any])
8 order by 2,3
9 /

TOTAL CITY DEP SALES PERC
---------- ------ ---- ---------- -------
0 Moscow DEP1 762
0 Moscow DEP2 657
0 Moscow DEP3 1020
0 Moscow DEP4 487
1 Moscow 2926
0 Omsk DEP1 34
0 Omsk DEP2 213
0 Omsk DEP3 156
1 Omsk 403
1 3329

10 rows selected

SQL>

Now we need to create a rule for our column.
So first of all we need to calculate values for all the cells with detail data. On the language of our model it would be perc[0,any,any]. By using a "Total" dimension with value=0 we will get only cells with detailed data. City and Department could be any. That is what we put on the left side of our formula.

On the right side we need to find for each detailed row a ratio of the current row sales - to it's region sales total.
So we would write sales[0,CV(),CV()]/sales[1,CV(),null]
In the denominator we just put a reference to the cell with a region total. While it has been already calculated - we don't need to sum up all the sales of a particular city again.

SQL> select * from t where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
2 model
3 dimension by (0 total,city, dep)
4 measures(sales, cast(null as varchar2(7)) perc)
5 rules upsert all
6 (sales[1,any,null]=sum(sales)[0,CV(),any],
7 sales[1,null,null]=sum(sales)[0,any,any],
8 perc[0,any,any]=round(sales[0,CV(),CV()]/sales[1,CV(),null]*100,2)||'%')
9 order by 2,3
10 /

TOTAL CITY DEP SALES PERC
---------- ------ ---- ---------- -------
0 Moscow DEP1 762 26.04%
0 Moscow DEP2 657 22.45%
0 Moscow DEP3 1020 34.86%
0 Moscow DEP4 487 16.64%
0 Moscow %
1 Moscow 2926
0 Omsk DEP1 34 8.44%
0 Omsk DEP2 213 52.85%
0 Omsk DEP3 156 38.71%
0 Omsk %
1 Omsk 403
1 3329
0 %

13 rows selected

SQL>

Ooops. As you can see there are three redundant rows with only a '%' in our new column.
Why did they appear? Because we placed UPSERT ALL.
If we don't want a particular rule to be under a general semantic, which is mentioned after the RULES word, we can put a needed behaviour name directly before the rule. In our case it is UPDATE (no new rows generation -> only update, for more information look Reports: totals & subtotals):

SQL> select * from t where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
2 model
3 dimension by (0 total,city, dep)
4 measures(sales, cast(null as varchar2(7)) perc)
5 rules upsert all
6 (sales[1,any,null]=sum(sales)[0,CV(),any],
7 sales[1,null,null]=sum(sales)[0,any,any],
8 update perc[0,any,any]=round(sales[0,CV(),CV()]/sales[1,CV(),null]*100,2)||'%')
9 order by 2,3
10 /

TOTAL CITY DEP SALES PERC
---------- ------ ---- ---------- -------
0 Moscow DEP1 762 26.04%
0 Moscow DEP2 657 22.45%
0 Moscow DEP3 1020 34.86%
0 Moscow DEP4 487 16.64%
1 Moscow 2926
0 Omsk DEP1 34 8.44%
0 Omsk DEP2 213 52.85%
0 Omsk DEP3 156 38.71%
1 Omsk 403
1 3329

10 rows selected

SQL>

This is actually what we were looking for!

to be continued ...

Reports: totals & subtotals

Starting from the current post I decided to write several notes on queries with Model clause. Mostly they would be dedicated to the topics of producing diverse reports.
In each case I'll give an opportunity how it can be done without using the model clause. And then - how the model clause could be used. So let's begin.
Input data:

SQL> drop table t;

Table dropped

SQL>
SQL> create table t as (select 'Omsk' city, 'DEP1' dep, to_date('31.12.2006','dd.mm.yyyy') year, 23 sales from dual union all
2 select 'Omsk' city, 'DEP2' dep, to_date('31.12.2006','dd.mm.yyyy') year, 154 sales from dual union all
3 select 'Omsk' city, 'DEP3' dep, to_date('31.12.2006','dd.mm.yyyy') year, 87 sales from dual union all
4 select 'Moscow' city, 'DEP1' dep, to_date('31.12.2006','dd.mm.yyyy') year, 562 sales from dual union all
5 select 'Moscow' city, 'DEP2' dep, to_date('31.12.2006','dd.mm.yyyy') year, 457 sales from dual union all
6 select 'Moscow' city, 'DEP3' dep, to_date('31.12.2006','dd.mm.yyyy') year, 890 sales from dual union all
7 select 'Moscow' city, 'DEP4' dep, to_date('31.12.2006','dd.mm.yyyy') year, 345 sales from dual union all
8 select 'Omsk' city, 'DEP1' dep, to_date('31.12.2007','dd.mm.yyyy') year, 34 sales from dual union all
9 select 'Omsk' city, 'DEP2' dep, to_date('31.12.2007','dd.mm.yyyy') year, 213 sales from dual union all
10 select 'Omsk' city, 'DEP3' dep, to_date('31.12.2007','dd.mm.yyyy') year, 156 sales from dual union all
11 select 'Moscow' city, 'DEP1' dep, to_date('31.12.2007','dd.mm.yyyy') year, 762 sales from dual union all
12 select 'Moscow' city, 'DEP2' dep, to_date('31.12.2007','dd.mm.yyyy') year, 657 sales from dual union all
13 select 'Moscow' city, 'DEP3' dep, to_date('31.12.2007','dd.mm.yyyy') year, 1020 sales from dual union all
14 select 'Moscow' city, 'DEP4' dep, to_date('31.12.2007','dd.mm.yyyy') year, 487 sales from dual)
15 /

Table created

SQL> commit;

Commit complete

SQL>

SQL>select * from t
2 order by 3,1,2
3 /

CITY DEP YEAR SALES
------ ---- ----------- ----------
Moscow DEP1 31.12.2006 562
Moscow DEP2 31.12.2006 457
Moscow DEP3 31.12.2006 890
Moscow DEP4 31.12.2006 345
Omsk DEP1 31.12.2006 23
Omsk DEP2 31.12.2006 154
Omsk DEP3 31.12.2006 87
Moscow DEP1 31.12.2007 762
Moscow DEP2 31.12.2007 657
Moscow DEP3 31.12.2007 1020
Moscow DEP4 31.12.2007 487
Omsk DEP1 31.12.2007 34
Omsk DEP2 31.12.2007 213
Omsk DEP3 31.12.2007 156

14 rows selected

SQL>

This is a table of sales for a company in Russia, which has it's departments in two cities: Omsk (Dep 1,2,3) and in Moscow (Dep 1,2,3,4). The data about sales is given for two years: 2006 and 2007.

Now we want to get sales for the year 2007. But we need not only detailed sales (as in a table), but also subtotals for each city. And a total for the whole company.

Non-Model Solution:
Usually such reports are created by using extensions to Group By: Cube, Rollup & Grouping Sets expression.

So our query would look like:

SQL> select city, dep, sum(sales) sales
2 from t
3 where trunc(year, 'y') = to_date('01.01.2007', 'dd.mm.yyyy')
4 group by rollup(city, dep)
5 order by 1, 2
6 /

CITY DEP SALES
------ ---- ----------
Moscow DEP1 762
Moscow DEP2 657
Moscow DEP3 1020
Moscow DEP4 487
Moscow 2926
Omsk DEP1 34
Omsk DEP2 213
Omsk DEP3 156
Omsk 403
3329

10 rows selected

SQL>

Model solution:
To create the same report using model clause, we have to execute the following query:

SQL> select city,dep,sales
2 from t
3 where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
4 model
5 dimension by (0 total,city, dep)
6 measures(sales)
7 rules upsert all
8 (sales[1,any,null]=sum(sales)[0,CV(),any],
9 sales[1,null,null]=sum(sales)[0,any,any])
10 order by 1,2
11 /

CITY DEP SALES
------ ---- ----------
Moscow DEP1 762
Moscow DEP2 657
Moscow DEP3 1020
Moscow DEP4 487
Moscow 2926
Omsk DEP1 34
Omsk DEP2 213
Omsk DEP3 156
Omsk 403
3329

10 rows selected

SQL>

Let's elaborate how it works.

Using the condition in the WHERE clause - we get only data for the year 2007 in the result.
Then we create dimensions: City and Dep - are the dimensions from the table. But we add one more dimension called Total and by default assign value 0 to all the cells for that dimension.
As a measure we take the field from our table Sales.
So at this stage we get as a result:

SQL> select *
2 from t
3 where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
4 model
5 dimension by (0 total,city, dep)
6 measures(sales)
7 ()
8 order by 2,3
9 /

TOTAL CITY DEP SALES
---------- ------ ---- ----------
0 Moscow DEP1 762
0 Moscow DEP2 657
0 Moscow DEP3 1020
0 Moscow DEP4 487
0 Omsk DEP1 34
0 Omsk DEP2 213
0 Omsk DEP3 156

7 rows selected

SQL>

Now we need to create extra lines for the subtotals and total.
We use rules for this.
You should remember that there are two ways to refer to a cell in a rule of a model clause: positional or symbolic.

Example of positional: sales['Omsk','DEP3'] or sales['Omsk','DEP4'].
When a cell which is positionally referenced exists - it's value is updated. When it doesn't exist - the model clause will create a cell with such dimensions.

Example of symbolic reference:
sales[city='Omsk',dep='DEP3'], sales[city is any,dep='DEP3'] ,sales[any,dep='DEP3'].
Actually the last one [any] is a positional reference - but I put it in the symbolic group because there's a remark in the documentation:
Note that ANY is treated as a symbolic reference even if it is specified positionally, because it really means that (dimension IS NOT NULL OR dimension IS NULL).
A typical behaviour for symbolically referenced cells: when it exists - the value gets updated. When it doesn't exist - no cells are created.

Don't forget about the cells which are referenced symbolically and positionally at the same time. By default they behave like all the references are symbolic.

Well, actually we could use 3 types of behaviour: UPDATE, UPSERT and UPSERT ALL.
For explicit determining we should place the name of behaviour directly after the RULES keyword. In this case it would be adjusted to all the rules.
If we want to specify a particular rule - we can put the name of behaviour right before the rule (we'll see how it works in the next post).

If we don't put any - the default behaviour UPSERT is used. How it works - I mentioned earlier.

When we use UPDATE - all cells are got updated, and if they don't exist - no cells are created, whichever reference (positional or symbolic) is used.

UPSERT ALL is a scpecial case - it can create cells even for symbolic reference.
But not in all cases, for example - it wouldn't create any cells if we put a rule:
sales[city='Voronezh','DEP5']=100 (because there's no city called 'Voronezh' in our table), but at the same time it would create a cell (which doesn't exist) for the rule sales[city='Omsk','DEP5']=100.
Why?
Because UPSERT ALL works in the following way (from the doc):

Step 1 Find Cells
Find the existing cells that satisfy all the symbolic predicates of the cell reference.

Step 2 Find Distinct Dimension Values
Using just the dimensions that have symbolic references, find the distinct dimension value combinations of these cells.

Step 3 Perform a Cross Product
Perform a cross product of these value combinations with the dimension values specified through positional references.

Step 4 Upsert New Cells
The results of Step 3 are then used to upsert new cells into the array

Just look:

SQL> select *
2 from t
3 where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
4 model
5 return updated rows
6 dimension by (0 total,city, dep)
7 measures(sales)
8 rules upsert all
9 (sales[0,city='Omsk','DEP5']=100,
10 sales[0,city='Voronezh','DEP5']=200)
11 order by 2,3
12 /

TOTAL CITY DEP SALES
---------- ------ ---- ----------
0 Omsk DEP5 100

SQL>

By putting RETURN UPDATED ROWS we get as a result only updated or inserted cells.
So as you can see despite we had a rule with Voronezh - no cells were inserted.

Let's get back to our subtotals and total.
We had two rules:

sales[1,any,null]=sum(sales)[0,CV(),any],
sales[1,null,null]=sum(sales)[0,any,any]

But as you remember [any] is treated as a symbolic reference - and won't create any cells if we don't put UPSERT ALL semantics.

SQL> select city,dep,sales
2 from t
3 where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
4 model return updated rows
5 dimension by (0 total,city, dep)
6 measures(sales)
7 rules
8 (sales[1,any,null]=sum(sales)[0,CV(),any])
9 order by 1,2
10 /

CITY DEP SALES
------ ---- ----------

SQL>

If we put UPSERT ALL semantics, our rules would do the following:

First rule: sales[1,any,null] will look for all dimension values that are referenced symbolically - city dimension: Omsk and Moscow.
Then create a cross product with positionally referenced dimensions: we'll get two cells - sales[1,'Omsk',null] and sales[1,'Moscow',null]
And these cells would be inserted.
BTW the dimension total=1 would mean that these are totals or subtotals.

Next rule sales[1,null,null] - will create a company total cell.
On the right side of the rules we have grouping formulas which will sum up all the sales values needed. By using CV() we reference to the dimension value of the cell on the left side of the rule.

PS
We could get the same result without Total dimension:

SQL> select city,dep,sales
2 from t
3 where trunc(year,'y') = to_date('01.01.2007','dd.mm.yyyy')
4 model
5 dimension by (city, dep)
6 measures(sales)
7 rules upsert all
8 (sales[any,null]=sum(sales)[CV(),any],
9 sales[null,null]=sum(sales)[any,null])
10 order by 1,2
11 /

CITY DEP SALES
------ ---- ----------
Moscow DEP1 762
Moscow DEP2 657
Moscow DEP3 1020
Moscow DEP4 487
Moscow 2926
Omsk DEP1 34
Omsk DEP2 213
Omsk DEP3 156
Omsk 403
3329

10 rows selected

SQL>

Here the rules are a little bit different.
The company Total is calculated from the cities subtotals, that has already been calculated, while in the main part (with Total dimension) we calculated all totals and subtotals on detailed sales data.

to be continued ...