handling price changes.

[pociwasiats]

how to track price periodically for each product? assuming that each product has 3 type of price. price for wholesaler, retail and cellar door.

put the price on the fact table or create a mini dimension?

my idea was put the price on mini dimension to avoid huge fact table. but that was my idea. which one is better?

 

[Hans63]

From what you describe I would opt for a fact table.

But it is depended on what the business requirements are. How long do you need to access the history of the prices. How often do they change. Are the prices free (they are not restricted to a certain price range), etc.

 

[pociwasiats]

if i put the unit price on my fact table. how can i track the history of my unit price? assuming that i want to track how many times that i changed the unit price during the frst quarter?

 

[MDXer]

A Count Distinct on the unit price would show you how many proces exist in the fact table for a given product.


I also would opt for the fact table. A price dimension would be more complicated to do on the fly calculations as you would need to include a join to this dimension when querying the facts. The other option would be to make the Price an Attribute of the product and then handle changes with SCD logic but this would be the most complex of all solutions.

 

[johnherman]

I would have a separate table for each price with effective dates for them. Then establish a query, view, materialized view, or summary table pulling them together.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[pociwasiats]

Do we need to recalculate on fact table? i think we already have revenue on fact. my idea was put unit sold and revenue on fact table.

ProdKey TimeKey UnitPrice
100 M1Q1 10
100 M2Q1 15
100 M3Q1 20
100 M4Q1 25
100 M1Q2 32

which one is better if there are a lot of type of price for an item? put the price on other dimension or dump it on fact table? wat i was tinking because, we dont need unit price oftenly. so i put the price on other dimension. is a UnitPrice a fast changing dimension??

thanks..

 

[Hans63]

If there are lots of types of prices, I would opt for a new dimension price_type. And add another dimension key to the fact.

Your business is the one that decides how often a Unitprice changes. It is up to the designer to handle it as a (slowly) changing dimension or as a fact. It is all in the business needs / requirements. And what the business expects to need / require in the forseeable future.

There is no single design that works for all situations, neither is there a single BEST design.

Several designs may all work out well in a given situation.

Putting several related facts (like revenue, items sold)in the same fact table is a logical approach.