Unit Price Fact Table

Hi

I am extracting unit prices for investment options on a daily basis. Is it best to create a fact table for these prices (see below)

Table - Fact_Investment_Option_Unit_Price

Columns:

Investment Option Key
Unit Price Sell Price
Unit Price Buy Price
Effective Date Key

Regards

Tim

Sure.

Is this a daily snapshot fact for unit price change? If so, it is the simplest solution. However, it should depend on how many investment options would have their price changed each day. If it is low percentage, I would create a transaction dimension with SCD start and end date to track changed Investment Options so that you may have its SK in other fact tables directly instead of having to connect between two fact tables.

If the percentage is high, say more than 50% of investment options will have their price changed each day, and you are only dealing with one transaction fact table and analysing options in the transaction, you may just leave the Unit Price in the transaction fact table.