Multivalued Dimension & Bridge Table

[abercrombieande]

In the example of having multiple diagnoses per bill in several of the Kimball books why is a bridge table needed? I know its suppose to always be a 1 to many relationship between the dimensions and the fact table. Why cant I just create a table like this and join it to the fact table? Thanks for your input

Dim Diagnosis
--------
Diagnosis Group --Key in fact table
Diagnosis Name
Weight Factor

[ngalemmo]

It is customary to have a diagnosis dimension. Usually their are attributes relating to a diagnosis that the business may use for analysis, plus you may need such a dimension for conformance to other facts, integration of different coding systems, and so forth. The structure you propose does not support such functionalities.

Besides, the table you describe will be very large... it will be many orders of magnitude larger than the diagnosis dimension. Do you really want to repeat the diagnosis name 1000's of times in the proposed table and at the same time limit your ability to perform analysis on diagnosis?

[abercrombieande]

Thanks for the information. I guess I didn't really think of using the diagnosis dimension again in another start schema as a conformed dimension. I also under stand the point about not having redundant attribute columns.

In my situation even if I combined the groups with the details we are only talking a few thousand rows and there are only two attribute columns, code and desc. I think this is why I did not see the full picture.

[Sponsored content]