Handling many-many relationship between fact and dimensions.

We have a scenario where there is a many-many relationship between fact and dimensions.

One approach as Kimball mentioned in his articles such as creating a Bridge table between fact and dimension.

But, is it a good idea to maintain m-m relationship in base fact and then create another fact with distinct of lowest grain (such as flashnumber in our case.) and count?

Here is an example: Base fact with M-M between SplSvcs and Flashnumber
Factid SplSvcs Flashnumber date
--------------------------------------
1 s1 f1 5/21
2 s2 f1 5/21
3 s1 f2 5/21

Another Fact for counts with distinct of flashnumber:

Flashnumber count date
------------------------------
f1 1 5/21
f2 1 5/21

So, is it a good idea to go with this approach of another fact on the base fact?

It depends on wither there are meaningful measures at the higher grain. If there are, it would make sense to create a fact table at the lower grain. If the many-to-many relation is simply providing greater context and does not affect the measures, then a bridge is appropriate.

Actually, both the facts are at the same grain (flash number). We consider 1 as a detail fact to store all the many-many relationship dimensions in that fact table where the flashnumber alone is not unique. Since it has m-m relationships so, to uniquely identify a row in that fact it is a composite key of flashnumber + dimension ids (where m-m exists). Because of this reason, we cannot have measures in this fact as the counts will not be correct.

So, we created another fact (master fact) where the flashnumber is unique and this will not have m-m relationship dimension ids. We maintain measures at this level.

Is it a good idea to have 2 facts at the same grain for these kind of M-M relationship between fact and dimensions?

any suggestions?

both facts are not the same grain.

If the grain of the fact is "flash_number", then effectively that means one record per flash. That equates with your "master-fact".

from your example, it looks like each flash can be associated with more than one SplSvcs.

A typical solution would include a bridge table between your fact and the SplSvcs dimension.

Instead of bridge table is it a good idea to create a factless fact table for special svcs ? and another fact for all other dimensions where they have 1-m relationships between dimensions - facts and measures ?

But, the grain for both factless fact and fact is the same "flashnumber".

I was reading Kimball's design tip 133 ( http://www.kimballgroup.com/html/11dt/DT133FactlessFactTablesSimplification.pdf) which says factless fact table is a better solution than bridge table if we have many-many relationship between facts and dimensions .

So, i'm really confused in what scenario we should use factless fact table if we have m-m relationship between fact and dimension and in what scenarios we should use the bridge table approach.

I really appreciate if someone can explain me on this as we need to take a decision on the design solution.