Fellow Techies--I have a grappler in front of me on how best to model for 3 sets of prices per product per supplier over a 3 year period. The current OLTP model retains the data separately for all three (Low,Average,High) with the expected supplierId, productId, etc. The OLTP model also contains every price for every day, for every product and for every supplier in a similarly designed family of tables, which I have consolidated into the Fact.ProductPriceMovement table for the same 3 year period. I am not quite sure what to do with these ceiling,floor, average tables however. Do I treat them as a single consolidated fact table, keyed by product,supplier--or is there a better way to manage that data?

It is my understanding that the median of a set of numbers is that number where half the numbers are lower and half the numbers are higher. So in this case, the median would be the price where half of the product sold in the grouping were toward the floor price and the other half toward the ceiling price. The average would be the total of all the prices divided by the number of items in the grouping. So,the median and the average may be close--or may not be.

In this particular case, these attributes are not derived per se -- what one would traditionally think of as occurring in an aggregate table as derived values from existing price data from the price movement source--instead, this data imports "as is" by product from a feed, then is archived in the tables described as its own source. For now (until I can track down more details about the feed data), I will have to presume that average means the average-- and that the data may or may not be derived from all the prices available by grouped product @the feed source--so far, any derivation hashing I've tried against the price movement source doesn't appear to match! :-0 . This has left me unsure of how to categorize this entity!