Data Modeling question (not really dimensional though)

[marc_brown98]

All,
I have been trying to work through a data model for storing formulas that will be consumed by ETL. We are trying to avoid storing the formulas in our ETL and want to have a generic data structure to use. My quick example is for an Item, say Item #1. Item #1 has a price that is determined by the following formula.

Item Number 1 is computed by 2 indexes ( IP1 and IP2) IP 1 = 62.5 and IP2 = 5.25


Item 1 = ((IP1 * .05) + IP1 ) + (IP2 * .90) + IP2)
Item 2 = (IP3 * .19222) IP3) / 10

Any help or thoughts would be appreciated. What I'm trying to find out is how this could be modeled in a relational db and then it could be consumed by our ETL processes. Just looking for a general design pattern, I have never attempted to store all the aspects of a formula in a table(s) before. To me this involves a complete meta model to consume a formula into all of its pieces, probably way overkill for normal data mart ETL, IMO.

Thanks,
Marc

[ngalemmo]

If all you are tying to do is allow dynamic calculations in a SELECT statement, then just store the expression as a string but require that the string be a valid SQL column expression and is approriate for the tables selected.

If you intend to evaluate the expression outside of SQL (i.e. in your own code), compile the expression into a series of Reverse Polish steps (command/value pairs) that are executed against a stack. Just store the pairs, a sequence, and other references to your metamodel. Have an engine that executes the stored steps at run-time.