The next step in the project is to create a final project summary for the project sponsors. Use what you learned in the Weeks Two, Three, and Four Individual assignments to create a final summary for
Student’s Name
Data Warehouse Schema
University’s Name
Table of ContentsIntroduction 2
Transformation process 2
Specific Integration Plans 4
References 5
This is a plan that will transform our schema to a data warehouse schema. We will include a transformation process, a schema diagram that shows the changes and specific integration plans.
Transformation processWe will use the star schema to transform our data schema to a data warehouse schema. According to Begg C & Connolly T (2015) star schema is a “dimensional data model that has fact table in the center, surrounded by denomarlized dimension tables”.
The following diagram depicts our data warehouse schema
From the table we can see that we have 14 denormalized tables and one fact table known as USAidFact_table.
USAidFact_Table
SchoolId
LoanId
DLId
DLSubsidizedId
GEId
DLUnsubsidizedId
DLParentPlusId
GradPlusId
GrantId
FFELId
IraqAfghanGrantId
TeachGrantId
FederalPellGrantId
Grants
GrantId {PK}
GrantProgram
FFEL
FFELId {PK}
Recepients
NoOfLoans
AmountOfLoans
NoOfDisbursement
FFELLoanType
Loans
LoanId {PK}
LoanType
DirectLoans
DLId {PK}
LoanType
DirectLoanSubsidized
DLSubsidizedId {PK}
Recepients
NoOfLoans
AmountOfLoans
NoOfDisbursement
AmountOfDisbursement
IraqAfghanGrant
IraqAfghanGrantId {PK}
SumOfRecepients
SumOfDisbursement
TeachGrant
TeachGrantId {PK}
SumOfRecepients
SumOfDisbursement
FederalPellGrant
FederalPellGrantId {PK}
SumOfRecepients
SumOfDisbursement
DLGradPlus
DLGradPlusId {PK}
Recepients
NoOfLoans
AmountOfLoans
NoOfDisbursement
AmountOfDisbursement
DLParentPlus
DLParentPlusId {PK}
Recepients
NoOfLoans
AmountOfLoans
NoOfDisbursement
AmountOfDisbursement
DirectLoanUnsubsidized
DLUnsubsidizedId {PK}
Recepients
NoOfLoans
AmountOfLoans
NoOfDisbursement
GainfulEmployment
GEId {PK}
CIPCode
CIPName
CredentialLevel
RepaymentRate
RepaymentNumerator
RepaymentDenominator
School
SchoolId {PK}
SchoolCode
Address
City
State
Zip
Province
Country
PostCode
SchoolType
Specific Integration Plans
We will have data mart, OLAP for reporting and a network that supports it.
According to Begg C & Connolly T (2015) a data mart is a “database that contains a subset of corporate data to support the analytical requirements of a particular business unit (such as the Sales department) or to support users who share the same requirements to analyze a particular business process (such as property sales)”.
According to Begg C & Connolly T (2015) online analytical processing (OLAP) is the “dynamic synthesis, analysis, and consolidation of large volumes of multidimensional data”.
ReferencesBegg, C & Connolly, T (2015). Database Systems. A practical approach to design, implementation, and management. Retrieved from http://people.stfx.ca/x2011/x2011asx/5th%20Year/Database/Database%20Management%20Textbook.pdf