Dimension Table

[cguy970]

I have a fact table that holds a score for each survey then I have a dimension table that tells me the hierarchy grouping for the survey, which is how it should be reported, such as Company, Division and Department. One additional requirement is they want to weight the score of the survey by the company hierarchy (example survey A they might want the score to be weighted at 25% at the department level, 15% at the division and 5% at the company). To calculate that would I need another fact table to hold that number or could I add it to the existing dimension table (company hierarchy) and calculate the weighted score in the cube?

Thanks for any suggestions!

[Jeff Smith]

Say you had 4 different companies and each company had a different number of divisions and departments. Each company would have to have it's own weighting because I would think that the weighting would have to total 100%.

You could weight the scores by multiples instead of percents. Company has a weighting of 1. Division is weighted at 3, and department is 5.

Multiply the Scores by the weighting and sum. Sum the weighting. Divide the Sum of the weighted scores by the sum of the wieghting.

If the weighting never changes, then it could go on the fact table, allowing the scores to be used without the linking to the dimension table. If there is a chance that the weighting could change, then put it on the Dimension table.