Data Modeling question (not really dimensional though)
2 posters
Page 1 of 1
Data Modeling question (not really dimensional though)
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
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
Re: Data Modeling question (not really dimensional though)
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.
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.
Similar topics
» Invoice dimensional modeling question
» effect of data sources in dimensional modeling
» Data Modeling Question (Bridge Tables?) for Star Schema for Proposals/Awards for university
» Some very basic question regarding overall ETL and Modeling strategy/best practices for Data warehouse project
» effect of data sources in dimensional modeling
» Data Modeling Question (Bridge Tables?) for Star Schema for Proposals/Awards for university
» Some very basic question regarding overall ETL and Modeling strategy/best practices for Data warehouse project
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum