creating dimensional model of log data

View previous topic View next topic Go down

creating dimensional model of log data

Post  charlie on Tue Jan 19, 2010 2:09 pm

I need some guidance on how to model user contact data.
We have various means of logging user contact information, i.e. user was sent an email, user received phone call, user's website was clicked.
Each contact method has different attributes tracked, i.e. phone number and call duration, name of email sender, etc.

I need to track the number or contacts, type of contacts, and the various attributes for each contact made. I have a user dimension and date dimension, but don't really know how to model the contact dimension(s). Does it make more sense to have a single table consisting of all attributes of the contact methods, or different tables for each contact method? Is there a better way than either of these methods.

thanks

charlie

Posts : 1
Join date : 2009-05-29

View user profile

Back to top Go down

Re: creating dimensional model of log data

Post  VHF on Wed Jan 20, 2010 6:58 pm

You can put all the possible attributes into a single Contact dimension table. Attributes not applicable to a particular contact type can be left blank, null, or have a value such as 'NA'. Consider the resulting output in queries/reports when determing which option is best.

You mentioned call duration, and that sounds like a fact rather than a dimension attribute.

You should probably have a fact table something like this:

DateKey
UserKey
ContactKey
ContactCount
CallDuration

The ContactCount will just have a value of 1 for every fact record, but as you aggregate facts in various ways it will give you an acurate contact count. CallDuration should have a 0 for contact types where it isn't applicable.

You should have one fact record for each contact, but try to avoid having one Contact dimension record for each contact--presumably there will be at least some contacts where all the attributes (e-mail address, etc.) will be the same. These can be reused by as many fact records as are relevant.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

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