Fast changing status transactional fact table

Hi all,

I need to design a fact table for servicedesk calls. These calls have a status which can change rapidly, but the team who handles the calls changes rapidly too.
These changes do not always happen, but can also happen in a different order. So a call can start at team 1, go to team 2, back to 1 and eventually to 3, or any other combination. It's the same for status changes.
For every status/team change I need to know when it changed (date + time) so it can be derived which team spent the most time on a call (for example) and what the total time spent is on a call (lead time).

I found that an accumulating fact table is not going to do the job, as the status and team changes are random. So currently I'm working on a transactional fact table in which I create a new row for every call change, but how can a lead time for a call be derived from these transactions? Do I create a lead time fact in the transactional fact table, or do I have to create another (accumulating) fact table?

It currently looks like this:

Fact_CallUpdate
StatusID
TeamID
Date
Time
Lead time
Number of tasks
etcetera...

DimStatus
StatusID
Status

DimTeam
TeamID
Team

I'm quite new to datawarehousing, so excuse me if I'm not seeing the obvious, but I can use some help.

Hi

Accumulating probably isn't right for you as the workflow sounds random.

Transaction fact table is probably the right choice, using a start and end date on each row. This will allow you to calculate time between changes.

Thanks

Thanks for your reply! A starting and ending date should indeed cover the lead time.

If I want to know the time it takes to change from one team to another, can that be derived from the time difference between rows, and how would that work? It's impossible to create a starting and ending date for every teamchange, since the workflow is random. I'm guessing I do need to create an accumulating fact table for that next to the transactional one?

Hi

It does depend on your reporting requirements. At the very least you need a transaction fact table that stores details of changes to a call. There is no reason why you can't store every team change for each call, in fact this is what you will need to do if you want to generate any meaningful team/call stats.

You may need a second fact that presents a summarised view of the data by team or by call. Again it depends on your reporting requirements. As you long keep the detail (transactions and changes) you are able to present the data for most reporting requirements. It sounds like a snapshot (daily?) would be better than accumulating. Accumulating is not suited as the workflow is not linear / follow a set pattern.

This book is very useful and would help you decide your approach - Star Schema - The complete reference, by Christopher Adamson.

Thanks for all your help, things are much clearer now :). I'll go ahead with the transaction fact table and leave it at that for now. I'll discuss the need for a second fact table here.
Also the book seems very useful indeed, thanks for the tip.

I just did a transactional fact of a workflow that had no particular order. For me, I found it most useful to add a "stepDurationSeconds" field to the fact. At the end of my ETL process, I have a script that updates the stepDurationSeconds fields for all new & updated records. Now, we can sum that field to get durations for particular assigned groups or any other way you would slice and dice.