Modeling price information collected at intervals

View previous topic View next topic Go down

Modeling price information collected at intervals

Post  jantzen on Thu Nov 12, 2009 5:01 pm

Hi All, I'd appreciate some feedback on a dimensional model for representing prices collected from source websites at varying intervals.

The incoming data is an observation of a price at a given point in time on the website. We want to be able to link prices back to when the data was collected, and also be able to query for the price that was in effect for a given period. My thought on how to accomplish this to have two fact tables: the first a transaction fact recording the price observation; the second an accumulating snapshot fact that records the start and end points during which the price was in effect.

"price_observation_fact"
price
purchase_unit_id -- the unit of measure for the purchase, e.g., liters
currency_unit_id -- the currency of the price
location_id -- the location where the price is effective
effective_date_id -- date the price went into effect
effective_time_id -- time the price went into effect
data_collection_run_id -- id of the job that collected the data

"price_accumulating_snapshot"
price
purchase_unit_id -- the unit of measure for the purchase, e.g., liters
currency_unit_id -- the currency of the price
location_id -- the location where the price is effective
start_effective_date_id -- date the price went into effect
end_effective_date_id -- date the price ceased to be in effect
start_effective_time_id -- time the price went into effect
end_effective_time_id -- time the price ceased to be in effect

The idea is to update the end_effective_date/time in the snapshot, such that if we collect the same price again, the window of time in which the price is in effect is extended. But, when the price changes, that row is complete and marked with appropriate end_effective_date/time. A new row with the new price is then created. Is this a reasonable use of the accumulating snapshot design? Is there a better way?

Many thanks,
David

jantzen

Posts : 2
Join date : 2009-11-12

View user profile

Back to top Go down

Re: Modeling price information collected at intervals

Post  ngalemmo on Thu Nov 12, 2009 5:55 pm

Is the observation timestamp different than the effective timestamp? If not, you can do this with just the accumulating snapshot table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Modeling price information collected at intervals

Post  jantzen on Thu Nov 12, 2009 8:00 pm

Thanks for the quick reply! Yes, the observation timestamp is different from the effective timestamp. Basically that is metadata about when the data was collected, whereas the effective timestamp represents when we believe the price to have been in effect.

Well how about this: instead of treating the observation as a fact, we have the snapshot table as above, but add a surrogate primary key. Then we create two more tables to construct a one-to-many relationship between the facts and the observations that underlie them:

"data_collection_run"
id
start timestamp
end timestamp

"price_observation"
price_accumulating_snapshot_id
data_collection_run_id

This way multiple observations can inform a single accumulating snapshot, and we can trace from snapshot back to observation. I know fact tables aren't supposed to have surrogate keys, but I don't see a good way to model the many-to-one nature of observations to accumulating snapshot without a mapping table like "price_observation" here. Is this a bad idea? Any other approach?

jantzen

Posts : 2
Join date : 2009-11-12

View user profile

Back to top Go down

Re: Modeling price information collected at intervals

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