Re: Calculated "virtual" rows
Posted by: Hauke P.
Date: August 01, 2012 12:38PM

> > All other figures should ...
> Please explain further. How many other figures?

Based on the current design status, the database will have to provide about 1500 figures, most of them calculated, i.e. non-basic figures. Most calculated figures actually base on other calculated figures, so you could think of it as a hierarchy of figures, or more precise: a figure-dependancy hierarchy.

The main idea behind the setup is: Basic figures such as costs and profits on a very low organizational level are aggregated to more higher-level calculated figures which again are aggregated to even higher levels. (By the way: The highest dependancy hierarchy depth is around 7 or 8 IIRC.) Unfortunately, lots of those calculations/aggregations (or 'calculation rules' as I call them) involve conditions and cannot be calculated with 'normal' SUM-like aggregate functions. Besides the examples in my original post, another typical example for a figure calculation rule would be: "if administrative costs for sale are not provided, use 20% of the total administrative costs for further calculations" or:
actual_sales_admin_costs = IIF(sales_admin_costs IS NULL, 0.2*admin_costs, sales_admin_costs)


Concerning the number of basic figures: I'd have to look up how many of all figures are actually basic figures, i.e. figures that don't need a calculation rule. But I think there are "only" about 300 of them.

> Will you use any of them in WHERE clauses? Could
> they be thrown into a single BLOB column of 'other
> stuff'? (I recommend JSON; XML is another
> possibility.)
Yes, I have to filter out figures with a NULL value quite often. I also have to use ORDER BY quite a lot. So just dumping them into a BLOB wouldn't work for me.

And wouldn't it make actually harder to implement those calculation rules?

Options: ReplyQuote


Subject
Written By
Posted
July 11, 2012 04:03AM
July 18, 2012 03:56AM
July 29, 2012 05:07AM
Re: Calculated "virtual" rows
August 01, 2012 12:38PM
August 02, 2012 08:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.