I am modelling a datamart and have multiple measures (indicators ) and dimensions.
Is it better when modelling the fact table to make indicators by column or having one column that contains indicators like creating a dimension of indicators ?
Please give me your opinions and when to choose each option?
Dimensional modelling aims for each fact table to represent a business process where you take measurements, with each measurement stored separately as columns. These are separately named, with the aim being that these are things you can drag onto your BI tool's report without a user having to worry about going off to another table to work out what measure you're looking at.
The Kimball Group don't normally recommend the approach where you create a measure type dimension, and producing a 'generic' fact. It makes the number of rows in the fact table bigger (one for each measurement) and makes calculations between measurements in a single measurement event (fact) more difficult.
Where would this end? You could feasibly have one fact that represents all measurements, from all your facts. This might be easier to model and load into, and might be exactly what you need in your situation, but it doesn't make it easier to report from, and wouldn't be called a dimensional model.
The situation Kimball suggests this would be an acceptable technique, however, is when you could have hundreds of potential measurements, but only a few would be applicable to any particular fact.
