CODEWITHSUNDEEP
sql-serverdatabase-designentity-relationshipdimensional-modelingstar-schema
user1254579
user1254579

Reputation: 4009

Dimensional modeling for waiting process

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

Upvotes: 0

Views: 52

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 1

Related Questions