Tuesday 31 December 2013

Dimensional Modelling Notes 3


Three types of facts
  1. Transactional
  1. Periodic Snapshot---Like bank account balances. They have fewer dimensions compared to transactional table but more facts then transactional. Summary tables are non additive
  1. 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)

No comments:

Post a Comment