CS614 Data Warehousing Assignment 2 Solution Spring 2014

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”.

Question:

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.

Solution: 

Solution:

Step-1: Identify Business Process

Considering the process exams_sessions_papers_aggregate the following entities are identified

1) Timeofday

2)Session

3) ExamcenterName

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:

Timeofday Primary key
Session Primary key
Examcenter Primary key
TotalPapers:  

 

 

 

 

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.

Facts:

Total_papers is the only fact and hence the fact table will be:

Timeofday Primary key
Session Primary key
ExamcenterName Primary key
TotalPapers:  

 

 

 

 

Dimensions:

There are three dimensions: Time of day, Session, Exam center Name.

ID Time_of_Day
1 Morning
2 Evening

 

ID Month Year
1 Spring 2013
2 Fall 2013

 

 

 

ID Exam_Center_Name
1 VU001
2 VU002
DOWNLOAD SOLUTION HERE
loading...