many to many troubles

View previous topic View next topic Go down

many to many troubles

Post  jmatt on Sun Apr 08, 2012 8:18 pm

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?

jmatt

Posts : 9
Join date : 2010-07-19
Location : Portland, ME USA

View user profile

Back to top Go down

Re:many to many troubles

Post  hkandpal on Wed Apr 11, 2012 12:15 pm

Hi,

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: many to many troubles

Post  ngalemmo on Wed Apr 11, 2012 12:35 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: many to many troubles

Post  hang on Thu Apr 12, 2012 1:42 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

What i did.

Post  jmatt on Tue Apr 17, 2012 3:33 pm

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

jmatt

Posts : 9
Join date : 2010-07-19
Location : Portland, ME USA

View user profile

Back to top Go down

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

Post  jmatt on Tue Apr 17, 2012 3:36 pm

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

jmatt

Posts : 9
Join date : 2010-07-19
Location : Portland, ME USA

View user profile

Back to top Go down

Re: many to many troubles

Post  ngalemmo on Tue Apr 17, 2012 6:29 pm

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

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

View user profile http://aginity.com

Back to top Go down

agreements an apps

Post  jmatt on Fri Apr 20, 2012 11:03 am

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?"

jmatt

Posts : 9
Join date : 2010-07-19
Location : Portland, ME USA

View user profile

Back to top Go down

Re: many to many troubles

Post  BoxesAndLines on Fri Apr 20, 2012 1:13 pm

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?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Agreements

Post  jmatt on Fri Apr 20, 2012 4:42 pm

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.

jmatt

Posts : 9
Join date : 2010-07-19
Location : Portland, ME USA

View user profile

Back to top Go down

Re: many to many troubles

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