Fact table's changing measures
3 posters
Page 1 of 1
Fact table's changing measures
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.
fact_service_installation_time
Unfortunately, these sub-processes measured may very well change. New ones added, old ones removed, etc. and at an unpredictable rate.
fact_service_installation_time
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?
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?
ryno1234- Posts : 33
Join date : 2015-01-07
Re: Fact table's changing measures
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Fact table's changing measures
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.
Re: Fact table's changing measures
Thank you both - you have each come through for me on several occasions and your input is highly valued.
ryno1234- Posts : 33
Join date : 2015-01-07
Similar topics
» Fact tables at different grains with measures in each
» Analyzing measures from two fact tables where records do not necessarily match due to SCD2 changes.
» Changing measures!
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Bridging Tables and Slowly Changing Dimensions
» Analyzing measures from two fact tables where records do not necessarily match due to SCD2 changes.
» Changing measures!
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Bridging Tables and Slowly Changing Dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum