This assessment addresses the following Unit Learning Outcomes:
1. Describe the common data sources that exist in organisations and their use in BI
2. Demonstrate practical skills in the processes associated with extraction, transformation and loading (ETL) of organisational data
3. Design and implement a simple data warehouse environment
There are a number of data sources from which you will need to draw your data. Below are the sources and the tables within them that are/may be of interest to you. These are discussed below (Primary Key, Foreign Key):
Source 1: Course Handbook
The Course Handbook is a FileMaker Pro Database. It contains data regarding all courses, units and offerings of units that are offered by Mudrock University. A course is made up of units, and a unit will have at least one offering each year.:
COURSE (CourseCode, Version, CourseName, SchoolName)
UNIT (UnitCode, CourseCode, Version, UnitName)
UNIT_OFFERING (OfferingNumber, UnitCode, Year, TeachingPeriod)
OFFERING_COORDINATOR (StaffID, UnitOfferingNumber)
Source 2: Student Information System
The Student Information System has its data stored in a relational DBMS (Oracle) at present.
STUDENT (StudentID, StudentName, DateOfBirth)
ENROLMENT (EnrolNumber, StudentID, UnitOfferingNumber, Grade)
Source 3: Human Resources System
The HR System is a proprietary system that is owned by the HR Department.
STAFF_MEMBER(StaffNumber, StaffName, SchoolCode)
SCHOOL(SchoolCode, SchoolTitle)
What you have to do:
TASK 1 (30%): Discuss two (2) issues that may be problematic in the creation of the data warehouse that are apparent from the description above. For both, explain what you see as being the issue, why it is problematic, and what you will suggest needs to be done. This should take no more than two (2) pages in total.
TASK 2 (25%): Discuss what you see as being the most appropriate level of granularity for your data warehouse. Your discussion will need to explain why you have made this choice, and why the alternatives have been discarded. This should take not more than one (1) page.
TASK 3 (30%): Assuming that the issues you have raised in TASK 1 have been addressed to your satisfaction, design a Star Schema that will support the analyses as listed above.
TASK 4 (15%): Provide the SQL statements you would use to create the tables if you were to be implementing this design using Oracle.
To Submit:
You will need to submit a single word-processed document that includes
- The written answers for Tasks 1 and 2
- Screen shot of the design you have created for Task 3 (please do not submit your visio file, or whatever file you use, just a screen shot, copied and pasted into a word document please).