Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension Table

2 posters

Go down

Dimension Table Empty Dimension Table

Post  cguy970 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!


Posts : 1
Join date : 2012-02-15

Back to top Go down

Dimension Table Empty Re: Dimension Table

Post  Jeff Smith 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

Back to top Go down

Back to top

- Similar topics

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