We have a system that takes a Request and sends it down a workflow. There are a number of different workflow routes and the route can change mid-process. Also, a user can diverge some routes for additional approvals. In all, there were too many options to model a workflow in 1 wide table as an accumulating snapshot.

I have a workflow step fact table that will track the step name, who took action, the action taken, and the request that step belongs to. That answers many questions, but leaves many unanswered questions. The request has a lot of attributes that have nothing to do with the workflow that need to be reportable. So, Request is basically a factless-fact dimension table. There is a lot of reporting that can be done using request, but my issue with it is how do I handle reporting hierarchies.

Example, A request has a inital requester and gets a request manager assigned to it along the line. Would I store those employeeIDs at the Workflow Step level, or at the Request level? They don't want to see the request manager assigned change, they just want to see by Business Unit, drill down to department, final drill down to RM assigned.

I have a habit of overanalyzing, so please, suggestions would be helpful. Thanks.