0

Business Scenario: I am designing a dimensional model for a university application processing.The university has 15 colleges under it.While making an application,an applicant can give the choices like 1,2,3 etc.The thing is if the 1st college reject an application then it automatically goes to the 2nd college then the 2nd college can either offer or reject .If 2nd college reject then application automatically passed to 3rd and so on until student secure a place

The dimensional model would answer the queries such as, how many applicants got selected based on their first preference what is the time taken between each college decisions Acceptance / rejection rates etc

Is it a good idea to design this as a transactional fact table (with reject date reject count ,acceptance date,acceptance count ,waiting time) and a dimension say, Dim application preference (with application id ,preference number etc)

Please suggest some useful ideas

user1254579
  • 3,901
  • 21
  • 65
  • 104

1 Answers1

1

Yes, I think you can model this dimensionally by creating a fact table with a granularity of applicant and college, and degenerate dimensions for the preference number, the date of application, the result of the application ("accepted", "rejected", "n/a"), the date that the decision making processes started for that applicant-college, the date of the decision, and the waiting time.

I believe that would answer all the questions.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • I am going to create a fact table of granularity applicant preferences and not of individual applications .An application consists of 1 or more preferences .These preferences are going to keep in a dimension and the waiting time etc associated with each preferences on fctbtble – user1254579 Sep 04 '15 at 06:17
  • "Applicant and college" is effectively the same as "applicant and preference", so I think we agree on granularity. "College" would be a dimension in your table, right? – David Aldridge Sep 04 '15 at 06:45
  • yes college is one of the dimensions,which contains the details of the colleges under the university.Now the solution is as you mentioned.Kept the student preference number inside the fact table as degenerated dimensions.The main application dimension consists of individual application details.One application in application dimension conssits of 1 or more preferences in the fact able – user1254579 Sep 08 '15 at 10:58
  • 1
    Yes, the fact table is essentially a set of applications, in which the keys are applicant and college, with the preference number and outcome of the as degenerate dimensions. Maybe the application should be the dimension table instead of the applicant, with the applicant as an attribute of the dimension, so that applicants can apply in different years. – David Aldridge Sep 08 '15 at 11:09
  • Actually there is an applicant dimension ,Application dimension and Fact table (which contains Application surrogate key ,application preference number as the key of fact table ) .The fact table granularity of application preferences ,so it – user1254579 Sep 08 '15 at 21:39
  • So fact table consists of same Application Id with multiple preferences .The fact table is taken as transactional fact table .Because ,if the student doesn't have any preferences the system generate the pretences number in oltp. fact table also consists of date a college received the application ,decision date ,flags to indicate accepted ,rejected etc – user1254579 Sep 08 '15 at 21:43
  • Sounds good. The key issue to consider is whether the business questions that you want answered are capable of being answered with relatively simple SQL. – David Aldridge Sep 09 '15 at 09:31