Measures at different hierarchy

could someone please provide me some suggestion on this?

Users are using an application for scoring a party location by observing the environment in the store.

Application calculates scores at different hierarchies.



I dont want to recreate a formula so I stored the scores in the dimension tables itself.

I am going in right path?

please suggest Thanks a million.

No, if you are trying to implement a dimensional model. The scores are measures which belong in a fact table. Your dimensions are observation, subscale, item and observation.

Since many of the measures are at different grains, there would be multiple fact table depending on the dimensionality of the measure.

Your "Party Location dimension" should have the address of the location. Party location score exist on the fact table. You also need a date dim to show what date that event happened and what is the score for that event on a specific day.