Three types of facts
- Transactional
- Periodic Snapshot---Like bank account balances. They have fewer dimensions compared to transactional table but more facts then transactional. Summary tables are non additive
- Accumulating snapshot --- Only fact where fact rows are updated as the process moves on. They are generally good for processes which have definate start and end. Like manufacturing. Not for bank account
How to design a
currency convertor fact table
Multiple Units of Measure
Consider you are
tracking number of product sold. Each manager sees this differently for retail
manager its number of products , for region manager it is number of cases and
for wholesale number of crates
So 100 soda bottles
= 10 retail boxes = 1 wholesale box
In such cases we
store the conversion factor in fact table and build a view over the fact to
give us quantities. Never leave it for the user to calculate the values as they
might wrongly calculate it.
Performance is not
affected by building view on fact table as we are doing row level calculations
and db are usually fast at row level calculations
Designing Periodic Snapshot tables (Summary Tables)
