OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?

View previous topic View next topic Go down

OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?

Post  plditallo on Mon Mar 31, 2014 5:21 pm

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?

plditallo

Posts : 2
Join date : 2014-03-31

View user profile

Back to top Go down

Re: OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?

Post  Jeff Smith on Tue Apr 01, 2014 5:51 pm

Wouldn't that be handled in an aggregate table? Median price is probably better than Average price - just saying.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

median vs. average

Post  plditallo on Tue Apr 01, 2014 10:20 pm

Thanks so much for your response.

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!

plditallo

Posts : 2
Join date : 2014-03-31

View user profile

Back to top Go down

Re: OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?

Post  BoxesAndLines on Wed Apr 02, 2014 4:45 pm

If the data in the OLTP application is dependent on the same key information, then put all three prices in the same fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?

Post  Sponsored content


Sponsored content


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