Case for the having key of fact in another fact table

Hi,
Here is our case.

We have some information like churn band and segmentation code that we are getting as write back from the analytical process .Same information is expected to be available at reporting layer. Also more kinds of analytical scores are expected to add in later stages.

In our architecture we have standard star schema and reporting is done from several snapshot fact tables.
Now problem here is how should we deal with these attribute .In current architecture we have added those scores as part of the fact table itself but the problem is that code for this fetching of score from write back table and writing it to fact table is getting redundant for different fact tables also addition of new score can be problematic.

We were thinking of adding a separate dimension that will hold all the scores for the customer and then add the key of that dimension to the fact tables .Is it acceptable solution.

Thanks

When you refer to "scores", I assume they are modeled values. I think the modeled scores are like age. Age is an attribute which can also be used like a measure. On a member fact, you can use Age to create an Average measure. But you can also roll up members by age and members can be put into categories by age ranges, suggesting that Age be a dimension with hierarchies.

Credit scores, test scores, etc can be treated the same way. Modeled scores are typically used in marketing to tier customers.

The scores would basically be a sort of junk dimension.

Hi,

Yes I am calling score to the modeled values .

My trouble here is that since scroes will change at certain frequency how do i model them

Keeping them in fact table is easy but as we have alot many fact this can be seen as redundant data.

Hence i wanted to model them as dimension and connect that to fact tables trouble here is i m not sure whether i should model the dimension table and customer grain (grain of score ) or should i create i list of all possible value of these score bands and connect the appropriate row(combination) to the fact table this seems like a complicated ETL solution .

Has anyone ever done something like that .

I would only load the actual combinations of the scores into the dimension table. I would not create a cartesian product of the various scores and load into a dimension table.

Hi

thanks jeff for validating my asumption .
I have decided to go with approach where only valid combination will be kept.

Any obvious issues that i should be aware of before putting this design into motion. ??