Dimension Table

View previous topic View next topic Go down

Dimension Table

Post  cguy970 on Wed Feb 15, 2012 11:43 pm

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!

cguy970

Posts : 1
Join date : 2012-02-15

View user profile

Back to top Go down

Re: Dimension Table

Post  Jeff Smith on Fri Feb 17, 2012 1:29 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum