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.
add the counts as reference info in the fact table as
people_count
andbuilding_count
add a dimension for each of these that stores the valid options, i.e.
people dimension
that stores akey
and a count andbuilding dimension
that stores akey
and a count. The main fact will have apeople_key
and abuilding_key
add one dimension for the count these is used for both people and building counts, i.e.
count dimension
that stores akey
and a generic count. The main fact will have apeople_count_key
and abuilding_count_key