We have a dimension table with day care facilities and a fact table where we receive the monthly number of taken spots per age category (3 cetagories). Per facility we also have the number of available spots per age category and want to calculate the facility's utilization (available spots vs. taken spots per age category). Where would the information about the number of available spots go? Naturally, I'd put that into the facility dimension as I view this as an attribute rather than a fact but our coworker says that this belongs into the fact table and has nothing to do with the dimension, so I'm a bit confused. Could someone please explain to me in which table the information about the available spots should go?
I tried to find the information in Kimball's DWH Toolkit but couldn't find a definitive example that would confirm or contradict this.
A piece of information can be both an attribute and a measure.
It sounds like it is an attribute so should be in the dimension - especially if you would need to filter or group by it.
If it can be used in calculations as data is loaded into the Fact (i.e. utilization can be pre-calculated) then there is no need to also hold it in the fact.
If there are calculations that can only be run at query time, and require this piece of information, then I would include it in the Fact as it makes the calculation easier