Modelling Service Visits

View previous topic View next topic Go down

Modelling Service Visits

Post  monserob on Wed Jan 30, 2013 6:16 am

I need to model service visits by engineers to customers. Some visits involve parts being added to the job and we need to capture this level of detail; the problem is some visits are completed with no parts added to the job. We still need to record these "non-part" visits somewhere e.g. to get the total number of visits by job type in a given period.

Is there a standard way of handling this scenario. It has been suggested that a dummy row be added to a line level fact table for jobs that have no lines but this sounds like it could cause confusion. My gut feeling is that we need at least two fact tables; one to record the visit at line level (jobs with no parts added will not appear in this table) and one for all jobs at a summary level (jobs with no lines will have zero amounts recorded). The problem is how could users analyse all jobs and then be able to drill down into lines all within the same report (bearing in mind that the grain of the two fact tables are different)

monserob

Posts : 4
Join date : 2013-01-30

View user profile

Back to top Go down

Re: Modelling Service Visits

Post  BoxesAndLines on Wed Jan 30, 2013 9:51 am

You're on the right track with two fact tables. This is the classic header/footer pattern, think order, order line, invoice, invoice line, etc. Visit level metrics are in your header fact, which can contain your summarized line level metrics. For the visit part fact, you put all parts for a given visit. You would drill across on the common dimensions, most likely a visit identifier degenerate dimension. I would also consider adding the "No part" row to the visit part fact to clearly highlight in the report that no parts were used for a given visit.
avatar
BoxesAndLines

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

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