This week you will continue your work on the project to evaluate higher education student aid data. You will evaluate your data warehouse data to ensure it can provide consistent, accurate query data,

Student’s Name

Data Warehouse Schema

University’s Name

Table of Contents

Introduction 2

Transformation process 2

Specific Integration Plans 4

References 5

Introduction

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 process

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

References

Begg, 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