Imagine that you are a senior data warehouse architect for LECAL Corporation. Your company has been contracted by the U.S. Department of Education to develop a data warehouse which will track student performance on a series of planned nationwide tests as part of a new STEM (Science, Technology, Engineering, and Math) initiative.
Specifically, new tests will be created for the five subjects of: algebra, geometry, calculus, chemistry, and physics.
The data warehouse to be designed will produce annual average test scores for graduating seniors for each test in the five subject areas. All graduating seniors, regardless of the type of K-12 school they attended (e.g., public, private, parochial, home schooled, etc.), are to be included in the data warehouse.
The system must include all relevant courses in the students’ course history and the final course grades. Prerequisite courses and grades are to be included as well. Course names and numbers are non-standard among the nation’s school districts and educational institutions and tend to change from year-to-year. Also, course grades are non-standard.
The reporting system for this data warehouse will produce tabular statistics and graphical color-coded charts of year-to-year progress by student’s course preparation, race, gender, special education status, ESOL (English Speakers of Other Languages), and family income level. Note that these demographic statuses can change for students year-to-year and even within a school year.
Your Portfolio Project assignment is to prepare a proposal for a star schema data model to meet the requirements of this system. Determine what dimension and fact tables will be required, what data they will contain, what data is to be collected, and how the source data is to be uniformly integrated into the data warehouse. Describe the challenges, encountered or expected, with data collection, transformation, and loading that may occur. Describe the challenges to keep your data model flexible enough to handle the vast number of different evolving courses.
Your proposal should be 12-15 pages, organized and well-written in conformity with APA requirements
In support of your responses, cite and integrate at least five credible sources, Place these in a reference page formatted in accordance with APA style. This reference page does not count toward the total page requirement.