How do you properly model with a bridge table

I like a lot of people have to deal with modeling an n depth organizational hierarchy. Page 164 of the Data Warehouse Toolkit second edition talks about how to do this. The problem is that I'm not sure how to intergrate it into my design and follow all the normal rules for dimensional modeling. In general my design looks like this:

dim_customer(cust_ck PK, ...)

bridge_org_hierarchy(parent_cust_ck PK, child_cust_ck PK, ...)

fact_some_facts(cust_ck, date_ck,...)

I can connect the customer table to the bridge table no problem. It's a 1:M. I run into problems connecting the bridge table to the fact table. I either wind up with a M:N connecting the bridge to the fact table or I get a 1:M connecting the fact table to the bridge which also isn't quite right because the key of the fact table is also concatenated.

Any ideas on how to resolve this mess of competing rules would be helpful.

The only thing I can think of is to just make a contrived key in the bridge table, turn the two key columns into FKs and enforce referential integrity some other way.

What do you mean 'the key to the fact table is also concatenated'?

A bridge allows you to traverse a hierarchy without the need of recursive SQL.  To go up a hierarchy, connect the fact customer key to the child customer key and the parent key to the dimension. (or visa-versa to drill down).  

Use attributes in the dimension to select the customers you wish to report.  Though the bridge all children (or parents depending on the join) of those selected rows will be used. You can also you bridge attributes (level, distance) for more specialized filtering.

It works really well.

Records in the fact table are uniquely identified by date and customer key so together they make the primary key.

I should clarify that this isn't just a strict modeling question. I'm really asking about physical implementation. I'm modeling in Toad right now and it's giving me static because I'm trying to ask it to do something that breaks modeling rules.

As you can see in the book (if you have it handy) there is no indication of cardinality in the figures. I think the expectation is that you create views for when you want to go up or down which is fine but doesn't work well for creating a physical model to share with the rest of the business.

The average user won't know or care how to navigate the hierarchy. It's there for people with SQL skills to do advanced analysis. They'll be the ones that get that you have to write your code in such a way to go up or down. For the average user using Power Pivot I'll probably just implement so it only looks at records on the 0 level.

All that said, my central problems still exist. There is supposed to be a 1:M relationship between dimension and fact tables. If I treat the bridge table like a dimension table, I'll break that rule because bridge records are only unique with the parent and the subsidiary key. Technically BOTH of those values should go into the fact table instead of just the customer dimension key as suggested by the diagram in the book.

So, like I said, my thought was to create a contrived key in the bridge table and drop THAT into the fact table. That seems to work from a modeling stand point but then creates the problem of how to do in enforce referential integrity in the bridge table. The contrived key works from a modeling stand point unless somebody has a better idea. The latter problem is probably a discussion in a forum on a totally different website.

On paper a bridge is M:M.  In use, the fact-dimension relationship through the bridge is M:1.

I don't know about TOAD as a modeling tool, but you can do tricks in ERWin by defining alternate keys and using those as roles when drawing the relationship (wither you link the parent or child key to the fact doesn't really matter, at least it gives you lines between the boxes).  It is also important to understand is the bridge is an adjunct structure that inserts itself between the normal fact-dimension relationship.  If the modeling tool is giving you fits, just make it an independent table.  You don't want to make up keys that shouldn't be there just to satisfy a particular diagraming tool.

I just processed what you're saying. I've been pretty focused on making sure my documentation is super clear for the next guy that looks at my work. Speaking with someone in the Toad forum somebody make this statement, "You cannot model many to many in physical because it cannot be properly implemented by any relational database." I'm working with SQL Server and question the validity of that statement.

That aside, in my diagram I can leave the bridge table off to the side, connect the dimension to the fact table normally and everything will still work properly if you want to analyze things by a single organization. If you want to roll up things by a company and its subsidiaries THEN you bring the bridge table into the equation.

Basically what I'm saying is that the bridge table is sufficient but not necessary. Do you concur?

'Necessary' depends. A bridge is used to resolve M:M relationships and provide an non-recursive way to traverse a hierarchy. It is necessary in the former case because there is no other way to do it, and necessary in the latter if the DBMS does not support recursion.

Well by necessary I mean I HAVE to implement the bridge table by connecting it the fact table and the dimension table with full referential integrity. In other words, in order to have a properly implemented data model I HAVE to insert the bridge table everywhere there is a relationship between the customer dimension and a fact table. From our discussions so far, it sounds like it's actually optional. I can put my warehouse together and enforce RI between the fact table and the dimension table. I can enforce RI between the dimension table and the bridge table which is fine because that's naturally 1:M. But I DON'T have to enforce RI between the bridge table and the fact table which is M:N (which is where my struggle on this started).

In my customer dimension there is one and only one instance of a customer. If one wanted to look at invoices from just one customer you could do that without the bridge table because there is a 1:M relationship between just one customer and all their invoices. If you wanted to look at a customer and all of their subsidiaries without recursion THEN you'd insert the bridge table to resolve the M:N.

So there are two use cases here. Look at one company. Look at a company and their subsidiaries. In the first use case where people only want to see one company I can implement that in the OLAP cube (SSAS) for your average user. If people need the second use case then developers can write code to roll that up properly using the bridge table.

Is what I'm saying making any sense?

Yes, but RI enforcement at the database level in a dimension model using surrogate keys is optional overall. In fact, I never do it. The very process of performing a natural key lookup to locate or create new surrogate keys ensures RI. Doing it at the database level is redundant and only serves to slow down loads.

In some cases, a DBMS may use FK declarations to improve its query plan, and many BI tools use that information to build relationship information in its metadata. So in those cases, I may declare the FK but disable enforcement at the DBMS level.

As far as the modeling tool goes, if you want FK declarations for a bridge and the tool doesn't let you do it, you can always manually write the FK script for that table… I know… it's annoying.