Remedy for a Bridge Table?

[omurchuc]

Hi folks,

I'm looking to get rid of my bridge table.
(yes I know why whould I want to do that?)

Reason being that I am using Mondrian and as far as I can tell bridge tables don't fit.

My Situation:
I have many facts and many Participants each participant having a role.
So I created a bridge table to alleviate the situation.

Is there a way to facilitate Many to Many between Fact and dimension without using bridge tables?

Here's hoping......

Thanks,

[ngalemmo]

IF there are a fixed number of roles for participants and IF there is a fixed number of participants in a role, you could represent each participant as another dimension to the facts. If this is the case, you could define a view over you existing data structures to flatten the many-to-many relationship so it can be consumed by Mondrian.

You could potentially eliminate the many-to-many relationship by repeating the facts for each participant, distributing the measures in some manner among them (average maybe?). Not sure if you would be able to do any kind of reporting on this.

Or you could build two cubes using both methods. One has one or more key participants in fixed roles represented as dimensions while the other has all participants and repeated facts (distributed or not). Each provides opportunities to do different types of analysis.

Other than that, I can't think of anything. I mean, a many-to-many relationship is what it is...

[omurchuc]

Thanks ngalemmo,

In this implementation you can have a fixed number of roles (3-4) but any number of participants.

I have been thinking along the same lines.
There's simply no real way around 'many to many' without redefining how the overall model is implemented.

Thinking cap now on as to which method to use....

(A) New lower granularity fact table based on participants with allocated measures (fact table is very large ... may not do this)
or
(B) Additional fixed dimension roles but I don't think this will help considering the n number of participants..

Think I may have to go with (A) or else change business requirements to fit a more simplified model.


Cheers,

[schnedar]

When you say it does not handle the bridge table because it is many to many, does it handle a group dimension? DataThat is, Fact table has a FK to participant_group dimension. Then, participant_bridge has FKs to participant_group dimension and participant dimension. Would that work with your software? For more info, see ch. 13 pg. 264 (Multivalued Diagnosis Dimension) of The Data Warehouse Toolkit book second edition.

[Sponsored content]