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

View previous topic View next topic Go down

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

Post  ian.coetzer on Thu Jul 07, 2011 8:08 am

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:

avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 37
Location : South Africa

View user profile

Back to top Go down

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

Post  hang on Thu Jul 07, 2011 9:12 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  ian.coetzer on Thu Jul 07, 2011 10:49 am

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?
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 37
Location : South Africa

View user profile

Back to top Go down

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

Post  hang on Thu Jul 07, 2011 4:42 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  John Simon on Thu Jul 07, 2011 8:04 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

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

Post  hang on Thu Jul 07, 2011 10:42 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  John Simon on Thu Jul 07, 2011 11:08 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

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

Post  hang on Fri Jul 08, 2011 12:26 am

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

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum