We are porting a stock forecast solution from Excel into Oracle using OWB 10.2 and facing issues when translating the forecasting model from the spreadsheet.
The model stores the forecast monthly stock level against time. The stock level in a forecast month determines whether or not an order is placed to replenish stock, and an order being placed affects the stock level in the future, dependent upon the lead time for the spare part in question.
The primary issue with this is that a loop is formed in the OWB mapping, which is of course invalid. It therefore appears that we need a more procedural solution to implement the model.
On investigation, it appears that using a MODEL clause would be an ideal solution. However, I can find no information as to how a MODEL clause can be encapsulated in such a way as to be included in an OWB mapping. I think it may be possible to wrap it in a Table Function, but can find no worked example of this.
Does anyone know how this can be done? My fallback position would be to code the problematic part of the forecast model into a pure PL/SQL Table Function, but I'd prefer to do it using a MODEL clause if at all possible.
You could use the get out of jail free card and use either a view or inline SQL containing the MODEL clause.
See below for using the inline SQL approach which lets you define arbitrary SQL, caveat is that the entire SQL snippet up to the MODEL clause would need to be defined.
With 10.2, you'll have to go the view route and have the view defined in the database. The only advantage of the inline SQL is that it is defined in the mapping and you don't need to maintain the DDL for the view. Going the table function route isn't a great way its kind of over the top doing PLSQL context switch for a pure SQL statement.
I'd thought about using a view, but unfortunately that involves the overhead of putting some 30 million rows into an intermediate results table for the view to access. The other drawback is that the application needs to be multi-user, and since you can't pass parameters into a view, there's no way of saying whose view of the world is being forecast.
It appears to me that what would be needed would be:
a) some way for an OWB mapping to pass the intermediate results into a temporary table (in memory?) for the view to reference, and
b) a way to pass a user ID parameter into a view in order for the view to determine what data to process.
Do you have any pointers to how these might be accomplished?
As I said at the top, my fallback position is to code the knotty part of the forecast algorithm in pure PL/SQL, but I would like to avoid that as I feel MODEL is much the better way to go from a code readability and maintenance point of view.
Not sure why the rows need to be staged prior to the MODEL clause, you can build up a complex SQL query with the MODEL clause in the view. Also not sure what you are talking about with the multi-user aspect, it should be fine (no different).