What are some ways to implement a many-to-many relationship in a data warehouse?
In my experience, a recursive hierarchy is the most practical way of tackling this. It offers the following advantages:


Unlimited depth.
Compactness.
Flexibility.
Speed.


By contrast, it takes an extra table for each level of "-to-many" joins. This is hard coded and difficult to maintain against schema updates.

By using filtered indexes, a large table of hierarchical joins can perform at superior speed to dedicated tables. The reason is each join is only "parent-child" compared to "to join table to data table". The latter has more indexes to process and store.

I've been trying to solve this problem for many years. Recently, this is what I came up with.