Help with call model

Hello to all ,

I'm developing a star model and would like everyone's opinion .

The business to be modeled is a control of appointments of an IT company .

The control service works as follows .

A customer finds a problem / improvement and informs through the system , which in turn forwards it to a technician, this technician opens the call and the service changes the situation open to being analyzed , after solving the technical reports for customer solution and service changes this situation .

The service has the following important fields for BI :
- Date of opening of AT(the service is called AT)
- Completion date of the AT
- Customer
- Technical
- System
- Manages Situation - service (open , completed , pending client; correction; Evolution)

Questions about this model are:
- What is the evolution of open , completed and pending calls ?
To answer this question three metrics was created : Open AT , completed AT and Pending AT.
- What is the SLA (service- level agreement ) per customer , system and management?
To answer this question was created two metrics : time used and time limit, when the time used is bigger then time limit the SLA is negative otherwise is positive.

I used SCD type 2 in dimensions(active_line, begin_date and end_date).

The ID_Number_AT and Id_Year_AT are the composite key of the business model(source). I had to use this keys, to get the minimal grain of the business.

The SK_Time_Open represents tha open of the call and the SK_Time_Closed represents the closed of the call.

The begin_time and end_time is used because the director thinks people are changing the values.

Follows the link to model.

https://lh3.googleusercontent.com/-LbcfjOurZkA/UxdvsdR5alI/AAAAAAAAARA/1jX23R-Suko/w1517-h532-no/CONTROL+SERVICE.png

I appreciate the opinion of all who wish to speak on this model.

It feels like both your original fact tables are a step or two aggregated from the most granular level, which might ease things if fixed, and the number of dates feels wrong for such a process.
My analysis - process:
As you say you have a number of process events

• Client logs issue in system
  
• System notifies IT engineer
  
• IT engineer opens call
  
• IT engineer works
  
  
  
  • investigate
    
  • resolve
    
  • test
    
  • re-resolve
    
  • re-test
    
  • completed
    
  
  
  

etc...

Each of these has a fixed point in time - i.e the timestamp it was entered into the system.
Grain suggestion:
I'd suggest starting with a fact table grain of call (_Number_AT and Id_Year_AT) plus process event

Dimensions are then

• customer
  
• date/time (of entry)
  
• management type (not sure I understand 'management' and 'technical' exactly, dimensions should generally be nouns not akectives)
  
• technical type
  
• situation
  
• employee
  
• system
  
• process event

I don't see any measures at this level as we are simply recording occurence of events

Then from there, you should be able to aggregate and pivot around key process events (filter, too) into a call level accumulating snapshot grain fact table ( primary key ID_Number_AT and Id_Year_AT) that tracks all the key timestamps for the steps. Here you will be able to work out the differences between the different dates. This could be done in the table or in reports.

Secondly, isn't SLA firstly another deep granular fact table that can be used in its own right, and be aggregated along the relevant dimensions into the aggregating snapshot fact table?