Joining/Relating Fact Tables

[meb97me]

The first phase of our current project is to build a model to capture activities against accounts for the companies customers. The idea is to give us a clear indication of what activity occurs along the lifecycle of an account and to give the business a picture of how much it costs to service an account as each of these activities can have a cost associated with it.

Now there are a variety of activities that can happen during the life of an account, we can send letters, call the customer, receive phone calls, send them a SMS, receive payments, request payments etc etc so a wide range of activities.

Now obviously each of these activities/processes above can have their own fact tables as we would want to be able to embellish each of these facts with dimensions that are pertinent to it ie with phone calls we'd capture the call centre operator, duration, time of call etc and this would obviously be a very different set of data to what we would want to capture about any letters sent. So as I said there is a clear requirement for a separate FACT table for each of these.

However the business would like to see a clear vision of what activities occur to an account during its lifetime and i'm not sure how easy it would be to join all these different FACT tables up to give a clear consolidated view.

What I'm thinking therefore is that we have a general activities fact table that holds all the events that occur but that doesn't contain all the detailed dimensional information associated with each activity type.

Now the grain of this fact table would be the same as that of the other activity specific fact tables ie ea record for each activity that occurs for each account, so for example for each letter sent we would capture that in the FACT_Letters table but equally we'd have and event in the FACT_Activities table so if you had 100 letters sent there would be 100 records in each tbale and they would have a 1:1 relationship

My question is, whether this is a good approach? also whether there is any need to try and link the multiple fact tables together so that it would be possible to drill across from the account summary activities fact table across to the activity specific fact tables records where you'd be able to drill deeper into the fact details as it would be a 1:1 relationship between the records

I hope that makes some sort of sense

[umutiscan]

This solution seems like an object oriented design. You have one activity object which is the supertype of all other activities and you hold same number of rows in the supertype. In your case I prefer to link existing transaction facts not to create an activity fact. Because if you need to join different types of transactions, you will have to join your activity table again and again.

Maybe business needs some metrics like "number of letters sent", "first sent date", "last sent date" and they don't need the transaction details. In this case you can design another fact which has these special metrics. You track the transactions everyday and update these metrics.