Relationship issues. I need a single model but it...

Anonymous · ‎10-12-2021

No, not asking for marriage advice. My marriage is fine but thanks for checking! 😄

I'm having trouble with a complex dataset that doesn't seem to fit the "star" model and I don't really see a way to make it (at any type of scale). I feel like this is probably a common problem but I haven't been able to find a solution that works for me yet. Maybe I'm not searching the right terms or am missing the point on something but I need help figuring out how to handle a modeling issue I've run into that is making me question my ability to use a single data model vs having to split it out based on specific ways I need to view the data.

Since this is a relatively large and complex SQL database for a CRM/ticketing system, I'll try to focus it on a specific set of the data. I think if I can solve this piece it will unlock how to actually deal with this everywhere else.

At a high level, the data is structured like this: I have employees ("Employees" table) that work on tickets ("Tickets" table) and projects ("Projects" table) in our system. They may or may not be assigned to ticket or project and may or not be scheduled ("Schedule" table) on them directly, but they can put time ("Time Entries" table) against them.

Here is a sample I mocked up of the tables and their relationships.

Employees.Employee RecID -> Projects.Project Manager RecID (inactive)

Employees.Employee RecID -> Tickets.Assigned Owner RecID (inactive)

Employees.Employee RecID -> Schedule.Employee RecID (inactive)

Employees.Employee RecID -> Time Entries.Employee RecID (active)

Projects.Project RecID -> Tickets.Project RecID (active, not all tickets are associated with a project though)

Tickets.Ticket RecID -> Schedule.Ticket RecID (active, not all tickets have a schedule, not all schedules are tied to a ticket)

Tickets.Ticket RecID -> Time Entries.Ticket RecID (active, not all tickets have a time entry, not all time entries are tied to a ticket)

Other key points:

An employee can put a time entry in against tickets they are associated with or schedule on (impromptu escalation).
Time can be entered that is not associated with a ticket (lunch break for example).
The employee's Manager and Report Card form a hierarchy that is used for reporting on a team or role's performance.

One main goal is to be able to have a slicer or filter on the Manager column and the Report Card column of the Employees table and be able to see the Projects, Tickets, Schedule, and Time Entries for the various employees that match. Again, this is a very trimmed down version of the different ways we need to slice and view the data but it highlights the problem I have.

For example, if I want to show a table with time entries for a specific Manager/Report Card combination, no problem. If I want to then show all the projects assigned to those employees that match the filter, it breaks because the relationship is inactive.

Some things I've thought about or come across:

Adding columns/flattening: I've thought about just adding columns to each table for the specific things I need to filter on but that gets messy and causes confusion. It doesn't scale well.
Combining/flattening: I've thought about combining things like the Tickets and Projects tables, but again, that doesn't scale well.
DAX measures: I can't filter properly on the measures if I am bringing in the Manager/Report Card. I've seen things that can alter a relationship or do "on the fly" joins, etc., but I haven't been able to drag Employee.Name, Project.Title, and a total of hours for the Time Entries (for example) into a table and have it work.
Bidirectional filtering: slow and causes other issues. Doesn't scale well.
Bridge tables: I've tried various combinations of things but can't seem to find a combination that would help.

So I'm basically at the point of thinking I may need one dataset that is focused on Time Entries analysis and another dataset that is focused on Projects analysis. I don't think that scales well either because then I might need one for Schedule and other perspectives, and this sample doesn't include things like Companies, Contacts, Agreements, Sales Orders, Quotes, Opportunities, etc. that would all be unique perspectives. It would be a LOT of duplication between the datasets just to get different active relationships based on the way I need to display the data.

Any help would be appreciated. Hopefully I'm just dense and missing a simple concept.