Data Modeling question (not really dimensional though)

View previous topic View next topic Go down

Data Modeling question (not really dimensional though)

Post  marc_brown98 on Tue Dec 13, 2011 6:10 pm

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

marc_brown98

Posts : 1
Join date : 2011-12-13

View user profile

Back to top Go down

Re: Data Modeling question (not really dimensional though)

Post  ngalemmo on Tue Dec 13, 2011 7:55 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum