views:

27

answers:

0

I have designed a fact table that stores the facts for a specific date dimension and an action type such as create, update or cancelled. The facts can be create and cancelled only once, but update many times.

myfact
---------------
date_key
location_key
action_type_key

This will allow me to get a count for all the updates done, all the new ones created for a period and specify a specific region through the location dimension.

Now in addition I also have 2 counts for each fact, i.e. Number of People, Number of Buildings. There is no relation between these. And I would like to query on how many of the facts having a specific count, such as how many have 10 building, how many have 9 etc.

What would be the best table design for these. Basically I see the following options, but am open to hear better solutions.

  1. add the counts as reference info in the fact table as people_count and building_count

  2. add a dimension for each of these that stores the valid options, i.e. people dimension that stores a key and a count and building dimension that stores a key and a count. The main fact will have a people_key and a building_key

  3. add one dimension for the count these is used for both people and building counts, i.e. count dimension that stores a key and a generic count. The main fact will have a people_count_key and a building_count_key