Remedy for a Bridge Table?

View previous topic View next topic Go down

Remedy for a Bridge Table?

Post  omurchuc on Mon Jun 15, 2009 11:07 am

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,

omurchuc

Posts : 4
Join date : 2009-03-05

View user profile

Back to top Go down

Re: Remedy for a Bridge Table?

Post  ngalemmo on Mon Jun 15, 2009 11:54 am

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...
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Thanks

Post  omurchuc on Tue Jun 16, 2009 4:58 am

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,

omurchuc

Posts : 4
Join date : 2009-03-05

View user profile

Back to top Go down

Re: Remedy for a Bridge Table?

Post  schnedar on Wed Jun 17, 2009 9:50 am

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.

schnedar

Posts : 4
Join date : 2009-04-23

View user profile

Back to top Go down

Re: Remedy for a Bridge Table?

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