Forum Discussion

BryMan_17's avatar
BryMan_17
Regular Visitor
1 year ago
Solved

Filtering across multiple columns, best practices? Many-to-Many or 1-to-Many (dim)

I have a case for something where either many-to-many or a 1-to-many relationship seems like my only options and am seeking advice on best practices and which to use:

My example is user-manager assignments and specific filtering criteria:

I have a table of users that all report up to “Thomas”, some users may have multiple levels of managers in-between them.  My table looks like this:

 

I also have a fact table of claims, with a unique key, and assignment, among other details.

 

The ask: For a filter in the filter pane or slicer where if the user selects a name, they see their claim assignments, but also the assignments of everyone any level below them.

Example:  Thomas would see everyone, David sees David, Francis, Jamie, and Mary, Mary only see Mary.

 

I have only come up with 2 solutions, a many-to-many relation, a bi-directional relationship with the many side of the 1-to-many being the dimension table.

Note: I am aware of a third somewhat solution of using a measure to check for selections and a filter applied to each visual, however, that causes some visuals to time out, so I excluded that.

 

I have two Many-to-many ideas:

Concatenating and repeating all combinations in both tables.

This seems inefficient, as it adds a column with a lot of characters.

OR

Creating a table that repeats users for all combinations and joining on the analyst name:

 

1-to-many example:

Repeating claim number and user, and join on claim number:

 

A drawback to the 1-to-many, is that the dimension table could reach over 10M rows in some models I wish to use this filtering concept.

Natruatlly, avoid both of these things in a data model and use a traditional star schema; however, it seems unavoidable for this ask. 

What method would be considered the best practice?

 

4 Replies

  • One to many is the better idea, here's another recommendation: if you have a User ID, ensure to have it on the Assignments/Claims table and then relate the Users ID single table containing all the hierarchy with it. That way you'll be able to filter the Claims table by the L3 Manager from your Users Catalog/Hierarchy.