Anyone who has worked with BI Publisher would probably agree that designing layout templates for reports with complex formatting can be pretty tricky. Unless you’re an XSL wizard there is a lot of trial and error to get things working right. Of course most of us don’t have the luxury of spending lots of time on trial and error. One fairly obvious solution, that works for me, is to do as much as possible in the report’s SQL, rather than in the layout template. The benefit is three-fold: 1) development time is decreased, 2) most of the complicated stuff in centralized in the SQL and 3) performance is increased. Digging through MS Word form fields to uncover layer upon layer of business rules, aggregations, and formatting logic can be a nightmare for whoever has to maintain the reports. Chances are that my SQL is complex anyway, so adding some additional complexity is a reasonable trade-off if it means that my layout templates can be straight-forward and easy to understand. Increased performance is also a nice benefit.
The point of this rant is to expound on the benefits of Oracle’s Model clause. The Model clause gives me the functionality I need to do the necessary data manipulations that allow me to represent my data set exactly as I want. The more manipulations I can do to the data set through SQL the less I have to do in my BI Publisher layout template. A good example of this are subtotals. Subtotals are usually calculated in reports rather than stored in the database. Calculating subtotals is quite easy with some reporting tools, but up until fairly recently, doing so in BI Publisher was not. Like many BIP functions it had to be implemented in XSL or some variant syntax. However, I believe the latest version of BIP plugin for Word offers this functionality from a wizard/dialog box. I have found that implementing subtotals with the Model clause to be both fun and easy. I say fun because the Model clause itself is just so cool that using it for any purpose is fun. I say easy because once you wrap your brain around how the Model clause works it’s quite easy to make it work for you.
The Model clause allows you to iterate through your data set as if it were a multi-dimensional array. The syntax for referencing elements of the array is similar to that of procedural languages that many of us are familiar with. This gives the ability to manipulate the dataset cell-by-cell to make it “look” exactly as you want. So basically you can use it to model the dataset to a specific report layout.
Taking the idea one step further, it can be helpful to use the Model clause query as the source for a Materialized View. This can be quite useful in a data warehouse environment where data is refreshed on a schedule. The MV can be refreshed as part of the ETL process and available for use by the reporting system. The added benefit of this approach is a significant performance boost because now the reports can execute a very simple query against the MV and get back a report-specific dataset, very quickly. Why burden the report with running a complex query if that overhead can be handed off the ETL process and incurred during off-hours.
Link for more info on Model clause.
