Best Practices Data Modelling- Using the same source data for multiple dimensions
What are the best practices when creating a model that uses the same source data for multiple dimensions?
In particular, let’s say I have an Issue tracking system. An issue belongs to a particular Project (BI, Data, etc.). An issue is Reported by an employee. The issue is then Assigned to an employee. Hours worked are then tracked to this issue. The traditional data model looks like this:
Note that this model requires employee source data be read twice. Though it is makes charts like this very straightforward:
But, what if I want to create a table that shows number of issues assigned/reported to each employee and the hours spend on assigned and reported issues. Maybe even include some comparison metrics. See example table below:
Given this data model, there is no way to contract this table. I was only able to create this chart using a heavily altered data model that makes other forms of analysis a pain.
Can a model be created where that allows all three charts to be created (A, B, C)?