Consolidated fact table or separate facts?
2 posters
Page 1 of 1
Consolidated fact table or separate facts?
I'm a newbie in DM design and I need help.
I'm designing a DM for an automotive business. We want to be able to track customers who have a high probability of being unsatisfied. We do have elements that help identify such customers: number of calls, number of claims, number of roadside assistance calls, etc. I do have my main dimensions well started (Date, dealership, vehicle, owners). My problem is: should claims, calls, etc. be reunited in a consolidated fact table? Or should they each have their own fact table?
At the moment, we're using vehicle serial numbers as a proxy for customers, but I eventually would like to be able to track the facts per owner, so that if an owner has had a vehicle for 18 months and had to call many times, etc. he would be flagged for a customer winback call but if he's had the vehicle for 2 months and hasn't had any problems, he doesn't get flagged. Where do I put the dates of start and end of ownership of vehicle? In the vehicle dimension? In the owner dimension? In a factless fact?
Thanks for any insight anyone of you can provide!
I'm designing a DM for an automotive business. We want to be able to track customers who have a high probability of being unsatisfied. We do have elements that help identify such customers: number of calls, number of claims, number of roadside assistance calls, etc. I do have my main dimensions well started (Date, dealership, vehicle, owners). My problem is: should claims, calls, etc. be reunited in a consolidated fact table? Or should they each have their own fact table?
At the moment, we're using vehicle serial numbers as a proxy for customers, but I eventually would like to be able to track the facts per owner, so that if an owner has had a vehicle for 18 months and had to call many times, etc. he would be flagged for a customer winback call but if he's had the vehicle for 2 months and hasn't had any problems, he doesn't get flagged. Where do I put the dates of start and end of ownership of vehicle? In the vehicle dimension? In the owner dimension? In a factless fact?
Thanks for any insight anyone of you can provide!
badaza- Posts : 2
Join date : 2012-05-08
Re: Consolidated fact table or separate facts?
I would put it in a factless fact table and use it to support FK assignment in the other facts. Problem is, how would you know ownership changed? I continue to get mailings from dealers for cars I have owned in the past... apparently they can't seem to get that information.
Re: Consolidated fact table or separate facts?
At this point, we're mostly concerned about ownership during the warranty period. Dealers do update the information in the system, so, at the latest, at the next maintenance or the next incident that necessitates creating a claim, the information would be updated.
badaza- Posts : 2
Join date : 2012-05-08
Similar topics
» Multiple Fact Tables vs. Consolidated Fact Table
» separate fact table/different grain - do I need a bridge table
» Location and population dim/fact
» Transaction Hour in Fact table or Separate Time Dimension?
» Multiple facts in a fact table
» separate fact table/different grain - do I need a bridge table
» Location and population dim/fact
» Transaction Hour in Fact table or Separate Time Dimension?
» Multiple facts in a fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum