Fact table's changing measures

View previous topic View next topic Go down

Fact table's changing measures

Post  ryno1234 on Tue Apr 21, 2015 6:54 pm

I have a business process that I'm modeling. The process is "Service Installation" and the measures are the amount of time it takes for various sub processes within the main process.

Example:


  • Time to greet customer: 2 minutes
  • Time to establish installation location: 5 minutes
  • Time unpacking tools: 4 minutes
  • etc.


Typically I'd design this like so:


fact_service_installation_time

  • id
  • date_id
  • customer_id
  • greet_customer_time
  • establish_installation_location_time
  • unpack_tools_time
  • etc.


Unfortunately, these sub-processes measured may very well change. New ones added, old ones removed, etc. and at an unpredictable rate.

This makes me want to model this as such:


fact_service_installation_time

  • id
  • date_id
  • customer_id
  • installation_sub_process_id
  • time_spent


This is somewhat shunned however http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/measure-type-dimension/

What are some good / manageable ways to handle such a situation?
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Fact table's changing measures

Post  BoxesAndLines on Wed Apr 22, 2015 10:42 am

I still like option 2 given the variety and variability of events. If you go wide how do you add up the total time or for that matter, which events were even applicable? Option 2 filters down to only applicable events and summing total time is easily accomplished. Performance can be addressed via partitioning or an aggregation fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact table's changing measures

Post  ngalemmo on Wed Apr 22, 2015 4:18 pm

I agree with B&L that your second option is probably the best choice. You may also consider an aggregation that presents a total and the more common measures as columns. But getting agreement on what the latter should contain could be challenging.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact table's changing measures

Post  ryno1234 on Wed Apr 22, 2015 6:33 pm

Thank you both - you have each come through for me on several occasions and your input is highly valued.
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Fact table's changing measures

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