Data Warehouse: Modelling Workload Allocations

Question

Hi,

We have a system that tracks the allocation of a unit of work, from receiving that unit of work till completing that unit of work.

A unit of work has a number of attributes, it's source, it's type etc. These I'm fairly ok with modelling, likely to be either dimensions of the fact or just attributes depending on the nature of them and how our users will desire reporting on them.

The problem is the allocation.

A unit of work may flow through multiple teams, within those teams it may flow through multiple individuals. Actions may be carried out on that unit of work.

And our users will be interested in reporting on this.

How many units of work were allocated, by team, in a specific time period for example.

I'm new to data warehousing, so I'm not sure how to model this. The candidates I've identified so far are:

1) Slowly Changing Dimensions

Using probably a Type 4, for the allocation to team and person within team.

2) Snapshot

Having Type 4 from/to attributes of the main fact that say when it was in account, so each unit of work occurs multiple times in the fact table that will populate the cube.

3) Accumulating Snapshot

I'm not sure I understand what this is, and I'm not sure it's relevant.

There are a number of things that would be like this SCD, team, team member, status, queue allocation of unit of work etc. So that seems to be a fair number of SCDs.

IS there something else I've missed in my list of options? Have I fundamentally mis-understood something?

Answer 1

If I understand correctly, one work unit can take several actions to complete. To start, I would choose a fact table with a grain of one action.

This shows dimPerson as type 2 dimension, it would also work as type 1, as long as CurrentTeamKey is copied into the TeamKey of the fact table on insert.