Using The SQL MODEL Clause To Generate Financial Statements
June 30th, 2005 by Mark Rittman
One of our clients that I worked at recently had a rather tricky report that
they wanted me to help with. At present, the report was put together using
Excel, and looked something like this:
The report consisted of two sections, the top section being the actual,
budget and variance figures for a department, and the bottom section being a set
of percentages based on the top section. The first line of the report, net
sales, was a memorandum item detailing the net sales for the department. The
next two lines, gross profit and other income were then added together by a
spreadsheet formula to give the next line, total net income. Next, wage costs,
rent costs and other costs were totalled to give total costs, with the figure
for total costs then being subtracted from total net income to give net
contribution. The percentages at the bottom of the report were then calculated
using these figures. Finally, variances were calculated, again using an Excel
formula, for each of the line items.
As well as producing a report for each department, the client also wanted to
produce another copy, this time for the company as a whole and derived from the
figures in the department level reports. The figures in the top half of the
report would need to be summed, whilst the percentages in the bottom half would
need to be averaged.
The client had tried to produce the report using Discoverer and an account
hierarchy, but the figures wouldn’t add up properly. Fundamentally, you can’t
(easily) produce this sort of report in a tool like Discoverer because of all
the inter-row calculations that are going on - the figures for row 4 are the sum
of row 2 and 3, the figures for row 12 are the sum of rows 5 to 7 divided by row
1, and so on. Now they could write some PL/SQL code that would load the data
into a PL/SQL collection and then process each figure individually, but a
thought that came to mind when we were discussing it was to try out the new SQL
MODEL clause.
For the full article, read on here.
July 1st, 2005 at 12:31 am
Hi mark
When I stepped through your code on model clause on a 10g database , after creating the final view, when I select from it I get an error as shown below
stdm10g=>select * from financial_statement_view;
select * from financial_statement_view
*
ERROR at line 1:
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
So Should I have the OLAP option installed in my database to use this code (that means I need OLAP license , right? )
Thanks
Santhosh
July 1st, 2005 at 11:00 am
Hi Santhosh
Interesting. The documentation makes no reference to the need to have the OLAP Option installed for the MODEL clause to work, but it would appear from your error message that it does in fact need this. In retrospect of course that would be obvious, as the MODEL clause creates a temporary AW to perform the model.
I think we need to make a distinction here between installing and licensing the OLAP Option. For the model clause to work, you need to have installed the OLAP Option, but (I think) you don’t need to have licensed it - at least I can find no reference to needing to license the OLAP Option to use the MODEL clause.
If anyone from Oracle is reading this and can comment, that’d be good.
So - bottom line. For the model clause to work, you need the OLAP Option to be installed. However my understanding is that you don’t need to have licensed the OLAP Option, but you should check this with your Oracle office (and can you install it *but not license it*, anyway?
July 1st, 2005 at 4:56 pm
I believe the “OLAP must be installed for MODEL to work” problem is a bug in 10.1.0.2 - I *think* 10.1.0.3 fixes this. It may also work if you relink Oracle after turning OLAP off (gmake -f something.mk olap_off).
I’m not an authority, but I don’t believe you need to have OLAP licenses to use MODEL.
July 1st, 2005 at 7:46 pm
HI
I already have 10.1.0.3 but it I dont think that fixed it (If its a bug) . I am on EM64T , so patchset 4 is not ready for EM64T yet, I will apply it as soon as its available . Another interesting thing is I think because of the 10.1.0.3 patchset , It v$option show that OLAP is installed eventhough you havent selected it .
I really like the MODEL clause and the idea of creating it with views and use them through discoverer (without OLAP option ). We are in the process of migrating our Oracle DW built using owb (having tough times in migrating to 64bit owb on EM64T ) from 9i to 10g and we use MS-Analysis Server for OLAP
Thanks
Santhosh
Santhosh
July 1st, 2005 at 9:02 pm
I’ll try to track down the people who would know, but it’s a holiday weekend in the US so I may not have anything until Tuesday or Wednesday…