Advice needed on modeling Partnership Dimension

I have a Dimensional Warehouse with the following situation:
Programs have Partner-Sponsors
Partner-Sponsors are made up of zero to many Partner-Sponsors and zero to many Organizations.

I originally saw this as a "Link Table" situation that I have used for solving Many to Many relationships. However now I have a "Link Table" that is self referencing. I am not sure how to handle this this sort of dimension.

Example:

Partner-Sponsor-01 is made up of Organization A, and Organization B
Partner-Sponsor-02 is made up of Partner-Sponsor-01 and Organization C
Partner-Sponsor-03 is made up of Organization D only

How would you suggest I model this dimension?

Thanks for your help
GregDC

Hi, please could you clarify what your reporting requirements are as that will help come up with a suitable design
- Do you just want to report by the Partner-Sponsor directly associated to a Program? In which case it doesn't matter what that Partner-Sponsor consists of, presumably?
- Do you want to report on the Organisations and, if so, how are you apportioning measures between all the Organisations associated to a Program (where the P-S consists of multiple Organisations)?
- Can a P-S be made up of any number of levels of other P-S's and Organisations and do those levels have any meaning? For example, do you want to report only on P-S-01 only when it is directly associated to a Program, or only when it is the direct parent of a P-S associated to a Program, or when it appears anywhere in the hierarchy associated to a Program?

The issue may be that you are trying to replicate the relationships in a transactional system instead of designing a dimensional model that supports your reporting requirements. I've often encountered this issue myself - the business say they just want to be able to report on the data in their transactional system but when you ask them how they expect (often m:m) relationships to be displayed in a report they don't know - until they can articulate (with your help) what they actually want to see in a report you probably shouldn't attempt to design a dimensional model

Reporting requirements for this DW are:

Display the status of all Projects that Organization B is involved.
This should show all projects associated with P-S 1 and P-S 2

Display the status of all Projects that Organization C is involved.
This should show all projects associated with P-S 2

Display the status of all Projects that Organization D is involved.
This should show all projects associated with P-S 3

Display the status of all Projects that P-S 1 is involved.
This should show all projects associated with P-S 1 and P-S 2

Display the status of all Projects that P-S 2 is involved.
This should show all projects associated with P-S 2

For reporting it should not matter how deep in the hierarchy a P-S or Organization is associated.
There is no limit to the depth of the hierarchies.

I know this sounds rather strange, but this DW is for a very large Cooperative International set of Agencies.

If there is no requirement to know how an Organisation or P-S is associated to a Project then I would just flatten out all the relationships into a (de-duped, if necessary) list of Organisations and a list of P-S's and then connect both lists to your Project via 2 FKs and 2 Bridge tables.
Whether you pre-define groups of Orgs and groups of P-S's depends on how many combinations you can have, how volatile the combinations are, etc.

So in your original examples:

Partner-Sponsor-01 is made up of Organization A, and Organization B
- 1 link to your P-S bridge table which in turn links to P-S-01 in your P-S Dim
- 1 link to your Org Bridge Table which in turn links to Orgs A and B in your Org Dim

Partner-Sponsor-02 is made up of Partner-Sponsor-01 and Organization C
- 1 link to your P-S bridge table which in turn links to P-S-01 and P-S-02 in your P-S Dim
- 1 link to your Org Bridge Table which in turn links to Orgs A, B and C in your Org Dim

Partner-Sponsor-03 is made up of Organization D only
- 1 link to your P-S bridge table which in turn links to P-S-03 in your P-S Dim
- 1 link to your Org Bridge Table which in turn links to Org D in your Org Dim

Does this look like it would meet your requirements?

I must admit that you may have given me the key to solving this problem, thank you.

I am, unclear on what should go in the the two bridge tables. Would you please elaborate?

Both bridge tables would work in the same way, so taking Organisation as an example:
- you have an Organisation Dim table
- your bridge table has as a minimum 2 columns, both keys: the Organisation Dim Key and a Bridge Key (you might have other attributes on this bridge table if you have a requirement for them)

For each Project, you would work out which Organisations were associated to it, say Orgs A, B and C, and create a Bridge table record for each of these; so you would end up with 3 reccords all with the same Bridge key but one referencing Org A, one Org B and the last Org C.

The choice you have is how you generate the Bridge key and populate the Bridge table. If you have a limited combination of Organisations then it might make sense to create the bridge table and populate it in a one-off process. Then when you load Projects in your normal daily/weekly/monthly process, you look up the correct, pre-existing Bridge table key that matches the Orgs you have against your project record and assign that to your Project fact. So the same bridge key would be assigned to multiple projects.
If you have a large number of combinations of Orgs it may make more sense to create the bridge table records 'on the fly' as you load your Projects, don't worry if a particular combination of Orgs already exists against a previous project and just create a new Bridge key per Project.
These 2 options are related to load performance, neither changes the functionality of your dimensional model.

Nick,
Thanks for the great input. I see what you are saying about the two bridge tables now.

But I am not sure how this solves the problem of Partner-Sponsor relations that contain other Partner-Sponsors that in turn contain Organizations (and maybe even more P-S's). I am really usually better at this than my questions may be showing, sorry.

Because you've broken down the P-S directly associated to each project into 2 lists: one of all the Organisations it contains and one of all the P-S's it contains. Then you've used the 2 bridge tables to associate all the Orgs and and the P-S's back to the Project - but as flat lists in your Dimensional model rather than as the hierarchies you have in your source system e.g.

P-S-1 consists of Org A and Org B
P-S-2 consists of P-S-1 and Org C

Project X is associated to P-S-2, therefore your list of associated P-S's and Orgs are:
P-S: P-S-1, P-S-2 => 2 records in your P-S bridge table
Org: Org A, Org B, Org C => 3 records in your Org Bridge table

OK I now see what you are saying. Sorry to be so slow. And thank you for all your help.

Greg DC