many to many troubles

I am modeling an aspect of our business that is giving me trouble. Our company facilitates college students studying abroad. The central fact table is an accumulating snapshot of the application process. Each application is associated with a sending university (i.e. Yale) and a program (i.e spring semester at University of Madrid). Each university may or may not have one or more agreements with our company (i.e. billing, academic etc.). Each agreement is for a certain program and has a begin year-semester and end year-semester.

Currently I have Dim_Program, Dim_University and Dim_YearSemester in one-to-many's with Fact_Application. Everybody's happy. Agreements is where the sadness starts.

Cleary there is a many-to-many between Fact_Application and Dim_Agreements. That is many application can have the same agreement and many agreements can apply to the same application (and applications can be associated with universities with no agreements) . But creating this direct many-to-many does not support aggregations for drill through by university, program, year-semester etc. that is needed.

I am considering a covering factless fact table. This fact would include UniversityKey, ProgramKey and YearSemesterKey. I think it may support the aggregations required and allow for applications with no agreements.

Am I on the right track here?

Hi,

what is the lowest grain of the Fact_Application table is it UniversityKey, ProgramKey and YearSemesterKey ? or is it some thing else.
thanks

jmatt wrote:
1: Each application is associated with a sending university and a program .

2: Each agreement is for a certain program and has a begin year-semester and end year-semester.

3: many agreements can apply to the same application

If 1 & 2 are true, how is #3 true?

I guess the grain of the accumulating snapshot Fact_Application would be one row for each applicant, and Agreement would become multi valued dimension for the fact. I wonder why you don't have a coverage factless fact table, or bridge table if change history can be ignored, between Applicants and Agreements. The University can be denormalised into Applicant and Program into Agreement dimension. I think once you put the attributes/entities in right place, the query can be easily written for all kinds of reporting requirements.

Thanks for your responses. i did create a covering factless fact table that does everything i need. The fact i am recording is:

When a new application comes into the warehouse determine what agreement's conditions it satisfys. Create a row in the factless fact table for each agreement satisfied with all the keys to all the dimensions for that application that are required for slicing. If an application satisfys no agreement still create a row but with a -1 for the AgreementKey (the no agreement agreement).

Jonathan

Each program can have more than one type of agreement i.e "Billing", "Academic" ...

How does that affect an application? Where I am going with this is you may be doing too much in a single fact. A fact to record the nature of agreements with a provider is different than a fact to record applications.

The business wants to know things like, "What billing agreements have the most applications associated with them that made it to status complete for programs in China?"

You've not described any direct relationships from Application to Agreement other than both of these entites have a Program and Sending University (optionally for the Agreement). How do you know which Agreements are for which Applications?

During the nightly load i check to see if a new application satisfies the conditions of any agreements:

SELECT
ad.ApplicationDetailFactKey,
ad.CurrentStatusKey AS ApplictionDetailCurrentStatusKey,
ad.ApplicationKey,
ad.StudentKey,
ad.OrganizationKey,
CASE WHEN oa.OrganizationAgreementKey IS NULL THEN -1 ELSE oa.OrganizationAgreementKey END AS OrganizationAgreementKey,
ad.ProgramDetailKey,
ad.ProgramLocationKey,
yt.YearTermKey
FROM dbo.Fact_ApplicationDetail ad
JOIN Dim_Organization o ON ad.OrganizationKey = o.OrganizationKey
JOIN Dim_YearTerm yt ON ad.YearTermKey = yt.YearTermKey
JOIN Dim_ProgramDetail pd ON ad.ProgramDetailKey = pd.ProgramDetailKey
LEFT JOIN Dim_OrganizationAgreement oa ON
o.OrganizationProgramTypeId = oa.OrganizationProgramTypeId
AND pd.ProgramId = oa.ProgramId
AND yt.YearInt BETWEEN oa.AgreementBeginYear AND oa.AgreementEndYear
AND oa.Term = yt.Term

The result of this query ends up in the factless fact table.