Hello,

I'm new to data warehousing, and have a simple question. Let's say I
have a fact table called Tests, and it stores test scores. Now each
test is assigned a grade, given the score. For example 100-90 is an A,
89-80 is a B and so on. While, I could just store the letter grade in
the fact table, the grading scale will change over time. If someone
looks up a score a year from now, the grade needs to reflect the new
grading scale.
Therefore, I would assume that I would want to create a "grade"
dimension. Now grades are thought of in ranges, not as specific
scores. I'm not sure how to implement this dimension. Should simply
add an entry for each possible score (0-100) and assign each score
it's grade, or do I somehow convey that information using the ranges
(100,90, 89-80, etc)? The problem that I see with using a range is
that I wouldn't be able to have a foreign key to the fact table.
My initial guess is that I would need to store each possible score in
my grade dimension table, but this seems to be a bit redundant. In
real world scenarios, "scores" might be in the 1-1,000 range or
possibly much higher. Does the dimension table really need to contain
a record for each possible item?
Any input would be much appreciated. Keep in mind that I'm new to
these topics, so my assumptions may be way off.

Thanks,
Attila

ThisIsAFakeAddress35 (AT) hotmail (DOT) com (Attila) wrote:

Hi Attila,

Create a dimension table called Grade. This will have
Grade_Id(Pseudo-primary; Surrogate key with values like 1,2,3,...) ,
Grade (Ex: A, B, C...). Now create a foreign key column in your fact
table called Grade_Id. In ETL calculate based on Score which Grade_Id
need to be populated in the fact. Keep the Score also in your fact
table since this might be of use later.

Hope this helps,

Thanks and Regards,
Srinath M. K