Using The SQL MODEL Clause To Generate Financial Statements
Oracle Database Tips by Burleson Consulting
By Mark Rittman, June 2005
Also see these notes on the SQL
So what we're looking for the MODEL clause to do is to fill in
the actual and budget figures for each of the departments, create
additional rows to hold totals and averages for the organisation as
a whole, then fill in the variances.
The first step then is to write the base SELECT statement for the
create or replace view financial_statement_model as
select rep_line_id, rep_line_item, department, actual, budget, variance
Now, we add in the first part of the MODEL clause, that defines
the dimensions and measures.
dimension by (rep_line_id, department)
measures (rep_line_item, actual, budget, variance)
The dimensions are the line number for the line item (net sales,
other income, wages % of sales) and the departments for which
were are going to produce the reports. The measures are the
items in the MODEL that we are going to either reference or
calculate. You may well at this point be noticing the similarity
between an SQL MODEL and an analytic workspace - both have
dimensions, both have measures - as what Oracle is going to do
when we kick the MODEL clause off is to create a temporary
analytic workspace in the background, load our data in, then use
the MODEL facility within the OLAP engine to perform our
calculations. Quite cool actually and it doesn't require you to
have licensed the OLAP Option to use it.
(UPDATE 1/7/05: It looks like, although you
don't need to license the OLAP Option to use this feature, you
need to have it installed. I can't find any reference to the
OLAP Option in the context of the MODEL clause within the online
docs, and no note that you need to have licensed it to use the
MODEL clause, but a reader wrote in last night and let me know
that the examples don't work without the OLAP Option installed.
To confirm licensing, the best bet is to check with your local
What happens now is that the MODEL clause in the background
creates three variables (measures), each dimensioned by
rep_line_item and department, which we can then manipulate via our
MODEL. The next bit is where we start to add the rules for the
actual [4,department] = actual[2,cv(department)] + actual [3,cv(department)],
budget [4,department] = budget[2,cv(department)] + budget [3,cv(department)],
Note the RULES UPSERT bit - the RULES part tells the MODEL clause
that this is where the rules come in, and the UPSERT bit allows the
model to create additional cells in the model - referred to as
custom dimension members - which we'll need so that we can store
additional entries for the figures for the company as a whole.
The first rules of the model tell Oracle to calculate the figure
for line item 4 (total net income) from the sum of lines 2 and 3.
Note that we reference actual
[4,department] - we need to specify both dimension values to
get to our actuals figure, and the "department" bit tells the model
to calculate figures for all members for this dimension - we could
reference just one department dimension member rather than all of
them by specifying actual
The cv(department) bit tells the model to use the "current value"
of the dimension as specified on the left hand side of the equation;
therefore, when the model is calculating the total net income for
"Retail", it uses the gross profit and actual income for "Retail" as
the calculation inputs.
actual [8,department] = sum(actual)[rep_line_id between 5 and 7, cv(department)],
budget [8,department] = sum(budget)[rep_line_id between 5 and 7, cv(department)],
The next two lines, rather than individually specifying other
line item dimension members for addition, specify a range of
dimension members instead.
actual [9,department] = actual[4,cv(department)] - actual [8,cv(department)],
budget [9,department] = budget[4,cv(department)] - budget [8,cv(department)],
These next two lines are the same as our first two lines, but
this time subtract line 8 (total costs) from total net income (line