Assignment No. 2
Consider the business process “exams_sessions_papers_aggregate”. The entities involved in the process are shown in above portion of ERD. The process calculates the information that can be sampled as follows:
“Totally 100 papers were conducted in exam center “VU001” in morning session in Fall 2013 exam”.
“Totally 110 papers were conducted in exam center “VU002” in evening session in Spring 2013 exam”.
“Totally 150 papers were conducted in exam center “VU001” in evening session in Fall 2013 exam”.
There are four steps of dimensional modeling. First step is to identify the business process. Consider the “exams_sessions_papers_aggregate” process described above. Apply the remaining steps of dimensional modeling to find out the fact and dimensional table(s).
Note: You are required to show these steps in solution as well.
Step-1: Identify Business Process
Considering the process exams_sessions_papers_aggregate the following entities are identified
Step-2: Identify Grain .
From the description of the given process and the sample output, the gain statement will be:
The total number of certain sessions with a certain exam centers with in a certain time period.
The resultant table may have the structure like:
Step-3: Choose facts.
Here only involves one fact i.e. the TotalPapers. Note that this is the only attribute that keeps changing; all the other attributes remain constant for a certain grain.
Step-4: Identify Dimensions.
Total_papers is the only fact and hence the fact table will be:
There are three dimensions: Time of day, Session, Exam center Name.