Case for the having key of fact in another fact table

View previous topic View next topic Go down

Case for the having key of fact in another fact table

Post  Ashish Mishra on Tue Feb 22, 2011 4:59 am

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


Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Case for the having key of fact in another fact table

Post  Jeff Smith on Tue Feb 22, 2011 10:06 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Thanks

Post  Ashish Mishra on Wed Feb 23, 2011 3:15 am

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 .

Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Case for the having key of fact in another fact table

Post  Jeff Smith on Wed Feb 23, 2011 10:00 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Thanks for quick reply

Post  Ashish Mishra on Wed Feb 23, 2011 12:22 pm

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. ??


Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Case for the having key of fact in another fact table

Post  Sponsored content


Sponsored content


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