Bridge Table - To simply group a dimension in more than one way?

Hi,
I am struggling with a simple dimensional model.
In most examples that I could find the Bridge table is references directly in the fact table (via surrogate key of course).

However when I try and model my scenario - the Bridge table is one level up (Fact -> Dim -> Bridge)
and to make it worse a have a fourth layer to store the list of possible group names.

Is this correct? something tells me I am snowflaking too deep - but I cannot figure out how to model this specific scenario any better?
please see image:

I guess the reason you have modelled this way is one agent can be in multiple groups therefore the AgentGroup becomes a multivalued dimension. Your model looks fine to me. The only thing I can think of is to add a primary AgentGroup key either in the DimAgent as an SCD2 attribute or directly in the fact table, so that you may have a proper one-many hierarchical dimension relationship for your fact.

Hi

Thanks for the reply, however an Agent can belong to more than one group at the same time.

So if I do have the AgentGroupKey in either DimAgent or the Fact table then which one of the groups that agent currently belongs to much i choose?

It depends on your business requirement. Normally in this case, there is one principle group that a member is dedicated to mostly full time (say FTE>0.5), and all other groups in which the member is seconded are sort of part time groups. Otherwise you cannot not have the relationship in the fact or DimAgent directly, and hence your current model is the only way around.

However if the groups are frequently reshuffled, and you are also interested in the historical grouping, you might need a periodical snapshot factless fact table. If size is not a big concern, use date key in the table and take the snapshot as frequently as to suffice the business requirements. Otherwise use effective date pair to have more compact form of relationship keeping.

Is the agent part of a group for the particular sale? If so, you could put the agent group as a separate dimension.

If not, why bother with the bridge table? You could just flatten it, and have an AgentGroup outrigger joining on AgentKey directly. I don't see what benefit the bridge table provides in this instance.

Do you mean to flatten the grouping within the DimAgent? Then what is the natural key of DimAgent. I assume it’s m-m relationship at any point of time and I think you can only flatten it when it’s 1-m hierarchical relationship. Otherwise only a junction table (bridge in DW) can sort out m-m relationship.

No I mean have DimAgentGroup, with AgentKey and AgentGroup as the unique constraint (forgetting about SCDs for the moment).
So this:
CREATE TABLE DimAgentGroup(AgentKey INT, AgentGroupName VARCHAR(200), PrimaryGroup BIT)
GO
CREATE UNIQUE INDEX IX ON DimAgentGroup(AgentKey, AgentGroupName)
GO

It would join to DimAgent on AgentKey. It is simply flattening the bridge table and DimAgentGroup.

Gocha! It's good dimensional thinking. However can you turn it into SCD dimension if needed?