Data Warehousing

1
Data Warehousing
Virtual University of Pakistan

• Lab Lect-2
• Lab Data Set

Ahsan Abdullah Assoc. Prof. Head Center for
Agro-Informatics Research www.nu.edu.pk/cairindex.
asp FAST National University of Computers
Emerging Sciences, Islamabad
2
Multi-Campus University
3
Degree Programs
4
Disciplines for BS
5
Disciplines for MS
6
The need

• Head Office wants a central data repository for
  decision support i.e. a DWH

7
Students Record Keeping Mgmt.
8
Data from Lahore Campus
9
Data from Lahore Campus Sample
10
Lahore Header of Student Table

• SID
• St_Name
• Father_Name

11
Lahore Header of Student Table

• Gender
• Address
• Date of Birth
• Reg Date

12
Lahore Header of Student Table

• Reg Status
• Degree Status
• Last Degree

13
Lahore Header of Course Reg. Table

• SID
• Degree
• Semester
• Course
• Marks
• Discipline

14
Lahore Facts About Data
15
Data from Karachi Campus
16
Data from Karachi Campus Sample
17
Karachi Header of Student Table

• St_ID
• Name
• Father
• DoB
• M/F
• DoReg
• RStatus
• DStatus
• Address
• Qualification

18
Karachi Header of Course Reg. Table

• SID
• Courses
• Score
• Sem
• Disp

Degree (BS/MS) is missing because separate
books are maintained, but the issue is critical
while loading data
19
Karachi Facts About Data
20
Data from Islamabad Campus
21
Data from Islamabad Campus Sample
22
Islamabad Header of Student Table

• Roll Num
• Name
• Father
• Reg Date
• Reg Status
• Degree Status
• Date of Birth
• Education
• Gender
• Address

23
Islamabad Header of Course Reg. Table

• Roll Num
• Course
• Marks
• Discipline
• Session

Degree (BS/MS) is missing, whereas same table
contains records for both. Only way to
differentiate is through discipline attribute.
24
Islamabad Facts About Data
25
Exercise
26
Problems with Adhoc Approach
27
Problem-1 Non-Standard data sources
28
Problem-2 Non-standard attributes
29
Problem-3 Non Normalized database
30
Notepad Issues
31
MS-Excel Issues
32
MS-Access Issues
33
Problem Statement
34
Data from Peshawar Campus

• Data at Peshawar campus is stored in Text files
• To store data regarding one complete batch 2 text
  files are used
• Lhr_Student_batch (Student record)
• Lhr_Detail_batch (Course Reg. record)
• 22 text files for 11 BS batches
• 8 text files for 4 MS batches

35
Data from Peshawar Campus Sample
36
Peshawar Header of Student Table

• Reg Student identity
• Name Student name
• Father Father name
• Address Permanent address
• Date of Birth Date of Birth
• lastDeg Last degree achieved
• Reg Date Date of Enrollment
• Reg Status Status of Enrollment (A/T)
• Degree Status Status of Degree (C/I)

37
Peshawar Header of Course Reg. Table

• Reg
• Courses Course code
• Score Out of 100
• Program CS/TC/SE/CE
• Sem Fall/Spring
• Year YYYY e.g. 1999
• We need to identify semester session (fall04)
  through combination of Sem and Year