creating dimensional model of log data
2 posters
Page 1 of 1
creating dimensional model of log data
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
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
Re: creating dimensional model of log data
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.
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
Similar topics
» Question - creating a dimensional model for facility management
» Question - creating a dimensional model for incident management
» Creating YTD, PTD provision in Data Model
» Creating a data model for pregnancy
» Tracking of historical data using SCD2 in a non-dimensional data model
» Question - creating a dimensional model for incident management
» Creating YTD, PTD provision in Data Model
» Creating a data model for pregnancy
» Tracking of historical data using SCD2 in a non-dimensional data model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|