Multivalued Dimension, bridge table, and constraints using SQL Server 2005

I'm developing a healthcare claims data mart. The diagnoses fields in the claims data are being implemented as a multivalued dimension, which consequently requires a bridge table between FactClaims and DimDiagnosis.

The bridge table contains two essential fields: DiagBridgeKey and DimDiagKey. The field DiagBridgeKey joins FactClaims and the bridge table, and DimDiagKey joins the bridge table to DimDiagnosis.

The difficulty I'm having is implementing constraints. SQL Server 2005 allows me to define a FK constraint between the bridge table and DimDiagnosis since DimDiagKey is the primary key in DimDiagnosis. But I can't get a FK constraint between FactClaims and the bridge table.

Ultimately, I want to make sure that ClaimsFact has integrity between ClaimsFact and the bridge table, and that the bridge table has integrity between it and DimDiagnosis. How can this be achieved?

Thanks in advance,
Pete

The claims fact contains DiagBridgeKey as a FK does it not? I don't understand the issue you are having.

I think the problem I'm running into is a technical issue: I lack a full understanding of creating constraints in SQL Server.

In "The Data Warehouse Toolkit (2nd ed.)" , pg 263 has a diagram of what I'm essentially attempting to do. SQL Server doesn't seem to like my attempts at defining a FK constraint in the fact table and in the bridge table on the DiagnosisGroupKey in the same way as pictured on pg263. SQL Server appears to need a PK; pg 264 illustrates this approach by introducing a DimGroup table between the fact table and the bridge table.

I have no difficulties defining a FK constraint between the bridge table and DimDiagnosis on the DiagnosisKey, however, because the DiagnosisKey is defined as PK in DimDiagnosis.

In "The Microsoft Data Warehouse Toolkit (with SQL Server 2005...)", interestingly, pg 307 makes a brief mention of a multivalued (many-to-many) dimension in the sample Adventure Works Cycles DW. I've just started to study the various foreign key definitions of the table called FactInternetSalesReason which records the multiple reasons for a sale. Apparently, this sample data warehouse has successfully defined a multivalued dimension scenario as I am attempting to do; thus, I believe I should take a closer look at the example.

Again, I believe my problem may be a gap in technical knowledge. Nonetheless, I thought it wise to inquire from the community if anyone has achieved this in SQL Server. Although I'm capable of defining integrity checks within a SQL stored procedure when populating the data warehouse, I'd rather ensure that integrity is defined at the design level of the server rather than having to ensure integrity at runtime.

I understand now.

Frankly, I never declare constraints other than unique indexes on dimension primary keys. They are superfluous given the surrogate key assignment process in the ETL.

I played with it in ERwin, and the DDL it generates is:

ALTER TABLE Fact
ADD CONSTRAINT R_2 FOREIGN KEY (bridge_key) REFERENCES Bridge(bridge_key)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


If you are modeling in ERwin, there is a trick to do this:

1. Define a unique alternate key on the bridge table using just the bridge key. In the options tab: turn on "Do not generate" and turn off "Generate as constraint" since this is a bogus index and is not unique. But it needs to be an AK for step 3 to work.
2. Create a non-identifying relationship from the bridge to the fact.
3. Alter the relationship. In the Rolename tab, there is a drop down list labeled "Migrated Index". Select the alternate key you created in step 1. ERwin will only migrate the bridge key to the fact table.
4. ERwin will create the constraint (above) and will not generate an index definition nor a unique constraint on Bridge.bridge_key.

Thanks -- this is useful to know.

I don't use ERwin, but one never knows when the company might invest in it. I'll save off your notes, nonetheless.

By the way, what exactly is an "alternate key"? I only came across the term recently.

An alternate key is a secondary unique key to a table. For example, a dimension table has a surrogate primary key as well as a natural key. The natural key would be an alternate key to the dimension table (type 1... or natural key and effective date in a type 2).

The other term is "inversion entry" which is any non-unique key.

Also, if you eventually use ERwin, the GUI changes depending on what physical database you are desiging for. The tab and check box references are for SQL Server... it is similar, but different, if you are doing an Oracle model or some other DBMS.

Good stuff -- thanks for the definitions. It makes sense. (Now lets see if I can put it into practice!)