Question

course.content.assessment.attempt.menu

Assignment

Wk 3 - Apply: U.S. Student Aid Data Warehouse Development [due Mon]

Wk 3 - Apply: U.S. Student Aid Data Warehouse Development [due Mon]

Assignment Content

This week you will continue your work on the project to evaluate higher education student aid data. You will transform your operational data structure and schema into a data structure and schema for a data warehouse, which will be exclusively used for reporting. 

 

Develop a plan to integrate this new data warehouse with an internet application. Include in your plan: 

A description of the transformation process 

A schema diagram identifying the changes needed

Revise the diagram you submitted in Week 2 per your instructor’s feedback. 

 Specific integration plans 

 

Document your plan as either: 

A 1- to 2-page Microsoft^® Word document 

A 6- to 7-slide Microsoft^® PowerPoint^® presentation with detailed speaker notes 

Answer 1

Let us Understand few basics terms used in this project

What is Data Warehouse?

A Data Warehouse is a

• subject-oriented
• integrated
• time-variant
• non-volatile

collection of data for supporting management’s decisional needs.

Subject-Orientation

Operational systems are application oriented

financial control

materials management

HR management

Subject areas might be

customers

products

orders

bill of materials

Each type of company has its own unique set of subjects

e.g. product customer account

Operational Database

An operational database is a database that is used to manage and store data in real time. An operational database is the source for a data warehouse. Elements in an operational database can be added and removed on the fly. These databases can be either SQL or NoSQL-based, where the latter is geared toward real-time operations.

operational Data warehouse . Time horizon- current to 60-90 days Updates of records Key structure may/may not contain an element of time. Time horizon- 5-10 years Sophisticated snapshots of data Key structure contain an element of time.

Introduction

The U.S. Department of Education website presents a general view of U.S. post-secondary education, as well as in-depth information on U.S. educational structure, accreditation practices, and links to state and federal education agencies and organizations.

An important reference for understanding how U.S. institutions of higher education compare to one another is the Carnegie Classification of Institutions of Higher Education. The Carnegie classification system divides all accredited degree-granting institutions into categories that define aspects like the highest level degree they grant or the special fields of study they offer.

The U.S. higher education arena contains a variety of not-for-profit associations that promote the professional development of people within the field of international higher education and work to expand awareness of issues related to it, including international student recruitment, international student admissions and retention, international student services, and comprehensive campus internationalization.

The National Student Loan Data System (NSLDS) is the U.S. Department of Education's (ED's) central database for student aid. NSLDS receives data from schools, guaranty agencies, the Direct Loan program, and other Department of ED programs. NSLDS Student Access provides a centralized, integrated view of Title IV loans and grants so that recipients of Title IV Aid can access and inquire about their Title IV loans and/or grant data.

Financial Aid Data Resources

Below are examples of the type of data you will find from each resource.

• NASFAA Resources
• National Level Data
• State Level Data
• Other Post-Secondary Research Organizations

NASFAA Resources

• The National Student Aid Profile annually provides detailed information about the federal programs authorized under Title IV of the HEA.

National Level Data

U.S. Department of Education; National Center of Education Statistics

• The Condition of Education looks at annual retention rates and graduation rates at 4-year degree-granting institutions and the percentage of 16- to 24-year-old college students who were employed while in school. It also takes a closer look at first-time undergraduate students and provides data on their total cost of attendance, their average total price, grants and scholarship aid, net price, and the percentage who receive financial aid at 4-year institutions.
• The Digest of Education Statistics provides information about average undergraduate tuition and fee rates and room and board rates for full-time students in degree-granting institutions. Data is also provided on the percentage of full-time, full-year undergraduates receiving financial aid, average amount of aid awarded, and the percentage of part-time or part-year undergraduates receiving aid. At the post-baccalaureate level, statistics show the amount borrowed, aid status, and sources of aid for full-time, full-year students.
• Integrated Postsecondary Education Data System (IPEDS), the core postsecondary education data collection program for U.S. Department of Education’s National Center of Education Statistics and the College Navigator tool, provides information on institution by type, institutional offerings, admissions and enrollment, cost of attendance and prices charged, and student financial aid and employment.
• The National Postsecondary Student Aid Study (NPSAS) examines student charges and student financial assistance, such as:
  • Average undergraduate tuition and fees and room and board rates charged for full-time students in degree-granting institutions, by level and control of institution
  • Average undergraduate tuition and fees and room and board rates by control and level of institution and state or jurisdiction
  • Percentage of undergraduates receiving aid, by type and source of aid and selected student characteristics
  • Average amount of financial aid awarded to undergraduates, by type and source of aid and selected student characteristics
  • Amount borrowed, aid status, and sources of aid for full-time and part-time undergraduates, by control and level of institution.
  The Projection of Education Statistics to 2026 offers actual and projected numbers for total enrollment in all postsecondary degree-granting institutions and for enrollment in public 4-year postsecondary degree-granting institutions.

U.S. Department of Education; Office of Postsecondary Education

• U.S. Department of Education's Office of Postsecondary Education Program Data page provides data on the federal student financial aid programs including the Federal Pell Grant, the Federal Family Education Loan Program, Federal Campus-Based Programs, Federal TEACH Grants, and the Federal Student Loan programs.
• The Federal Campus-Based Programs Data Book provides data on the history of funding and awards, participating institutions, and the final allocations for the year. Fiscal data is provided, as is recipient data on campus-based programs like Federal Supplemental Educational Opportunity Grant (FSEOG), Federal Work-Study, and Federal Perkins Loans.
• The Federal Pell Grant End-of-Year Report provides a summary of statistics for the distribution of Federal Pell Grant recipients broken down by multiple data points such as expected family contribution and family income, enrollment status and type and control of institution, family income and application type, grant level and type and control of institution, state and control of institution, state of legal residence and control of institution, type and control of institution, and EFC formula type. It also looks at Federal Pell Grant recipients by age and family income, family income and net asset level, educational cost and grant level, and family income and educational cost.
• U.S. Department of Education Budget News provides the latest news on funding of the U.S. Department of Education programs, including congressional action on appropriations.

U.S. Department of Education; Federal Student Aid

• U.S. Department of Education’s Federal Student Aid Data Center provides student aid data, school data, Federal Family Education Loan (FFEL) Program lender and guaranty agency reports, and business information resources.
• FAFSA Application Volume Reports offer data broken down by Postsecondary School and State of Legal Residence and by High School
• Three-Year Cohort Default Rates for Schools; U.S. Department of Education, Federal Student Aid

U.S. Census Bureau

• Current Population Survey Reports issued by the U.S. Department of Commerce’s U.S. Census Bureau include Educational Attainment in the United States.

College Board Trends Reports

• Trends In College Pricing offers annual information on topics including published tuition and fee and room and board charges, student budgets, regional variation in charges, average net price for public and private institutions, family income, and enrollment patterns over time.
• Trends in Student Aid has annual information on a number of student aid topics including: total student aid, 10-year trends in student aid; total undergraduate/graduate student aid by type, total aid per full-time equivalent student, total grants, and total loans. It also offers data on types of grants and loans, federal aid recipients, federal aid by sector, student loan default, student loans and debt, education tax credits and tuition deductions, state and institutional grants, and college savings plans.

State Level Data

• Since 1960, Grapevine has published annual compilations of data on state tax support for higher education, including general fund appropriations for universities, colleges, community colleges, and state higher education agencies. Each year’s Grapevine survey has asked states for tax appropriations data for the new fiscal year and for revisions (if any) to data reported in previous years.
• State Higher Education Executive Officers Association's (SHEEO) annual State Higher Education Finance (SHEF) report. The SHEF report is produced annually by SHEEO to broaden understanding of the context and consequences of multiple decisions made every year in each of these areas. No single report can provide definitive answers to such broad and fundamental questions of public policy, but the SHEF report provides information to help inform such decisions.
• Education Commission of the States (ECS) offers several opportunities to examine financial aid data at the state level. ECS tracks state education policy on a wide variety of education topics via their interactive tool. They also oversee the State Financial Aid Redesign initiative.

Other Post-Secondary Research Organizations

• Lumina Foundation’s mission is to expand access and success in education beyond high school, particularly among adults, first-generation college going students, low-income students and students of color. This mission is directed toward a single, overarching big goal – to increase the percentage of Americans with high-quality degrees and credentials1 to 60 percent by the year 2025.
• The Project on Student Debt is an initiative of The Institute for College Access and Success (TICAS), an independent, nonprofit organization working to make higher education more available and affordable for people of all backgrounds

FEDERAL STUDENT AID

In 2018-19, 33% of federal aid was based on students’ financial circumstances—a decline from 91% in 1988-89 and 58% in 1998-99. The introduction of unsubsidized student loans and education tax credits, followed by PLUS loans for parents and graduate students and the Post-9/11 GI Bill, outweighed increases in Pell Grants and smaller need-based programs.

Total federal grant aid increased by 56% in inflation-adjusted dollars between 2008-09 and 2018-19. Pell Grants increased by 35% ($7.3 billion); veterans’ benefits, which rose by 214% ($8.4 billion), grew from 15% of federal grants in 2008-09 to 30% in 2018-19.

In 2018-19, average benefits from the Post-9/11 Veterans’ Benefits program were $15,990, compared with $4,160 per Pell Grant recipient. Almost 10 times as many students received Pell Grants as veterans’ benefits—6.8 million vs. 699,000 in 2018-19

Federal
Federalloans to undergraduates fell by 18% between 2008-09 and 2018-19, rising by 7% over the first five years, but declining by 23% between 2013-14 and 2018-19.

FWS
FWSand Federal Supplemental Educational Opportunity Grants (FSEOG) combined provided $1.7 billion to undergraduate students in 2018-19—1% of the total aid.

TYPES OF STUDENT AID

In 2018-19, undergraduate students received an average of $15,210 per FTE student in financial aid: $9,520 in grants, $4,410 in federal loans, $1,210 in education tax credits, and $70 in Federal Work-Study (FWS).

Graduate students received an average of $28,140 per FTE student in financial aid: $8,920 in grants, $18,470 in federal loans, $700 in tax credits, and $50 in FWS.

Grant aid per FTE undergraduate rose by 40% between 2008-09 and 2013-14 from $5,940 (in 2018 dollars) to $8,340, and by another 14% to $9,520 by 2018-19.

Grant aid per graduate student rose by 13% ($950 in 2018 dollars) between 2008-09 and 2013-14 and by another 9% ($710) between 2013-14 and 2018-19. Federal loans per graduate student rose by 8% ($1,440) over the first five years of the decade and fell by 1% ($100) over the next five years.

DISTRIBUTION OF STUDENT AID

The share of dependent Pell Grant recipients from families with incomes below $30,000 (in 2014 dollars) rose from 50% in 2007-08 to 58% in 2015-16.

In 2017-18, 27 states considered students’ financial circumstances in allocating at least 95% of their state grant aid. Thirteen states considered students’ financial circumstances when awarding less than half of their state grant aid.

Between 2016-17 and 2017-18, need-based state grant aid per FTE undergraduate rose 5%, from $634 (in 2017 dollars) to $667; non-need-based aid rose 11%, from $196 to $218 per student. The share of state grant aid that was need-based declined from 76.4% to 75.4%.

In 2015-16, 78% of full-time students at public four-year colleges and universities had to cover an average of $14,400 in expenses beyond their expected family contributions (EFCs) and grant aid from all sources. For 12% of students in this sector, grant aid exceeded their documented financial need.

In 2015-16, 80% of full-time students at private nonprofit four-year institutions had to cover an average of $20,770 in expenses beyond their EFCs and grant aid from all sources. For 16% of students in this sector, grant aid exceeded their documented financial need.

The 41% of 2016 tax filers benefiting from the student loan interest tax deduction who had adjusted gross income (AGI) below $50,000 received 29% of the tax savings. The 20% with AGI over $100,000 received 28% of the tax savings.

STUDENT BORROWING

Much of the public discussion about college affordability focuses on student debt. This report includes a wide range of information about annual borrowing, students’ cumulative debt, outstanding education debt, and repayment patterns. Trends in Student Aid 2019 documents an eight-year decline in both total annual student borrowing (from 131.7 billion in 2018 dollars in 2010-11 to $106.2 billion in 2018-19) and loans per full-time equivalent undergraduate student (from $6,000 in 2018 dollars in 2010-11 to $4,410 in 2018-19). After rapid increases between 2007-08 and 2012-13, the average debt per borrower graduating from public and private nonprofit four-year institutions rose by $300 between 2012-13 and 2017-18 and the average debt per graduate declined as the share of students with debt fell slightly. These patterns do not by any means signal the end of the problems too many students face with debt—particularly those who do not complete their programs, African American students, and those who attend for-profit institutions. But they are nonetheless encouraging.

THE DATA

Much of the data on which Trends in Student Aid is based comes from the Federal Student Aid office of the U.S. Department of Education (ED), which provides precise information about the volume of federal student aid disbursed. The figures for 2017-18 in Trends in Student Aid 2019 are revisions of the numbers published last year, based on ED’s updated data. Similarly, next year we will revise the 2018-19 figures. Some of the other figures reported here are less precise. For example, the latest data on federal tax credits are for calendar year 2017. We have developed a methodology to translate IRS data into estimates of these policies’ benefits for tax filers. Similarly, our estimate of the volume of nonfederal student loans is based on reports from MeasureOne and estimates of their share of the market. We base our current estimate of private grant aid on information from the 2016 National Postsecondary Student Aid Study and more recent information from College Board’s Annual Survey of Colleges. These and other figures represent best estimates of the amount of aid students receive, rather than exact reporting. Each year, we review our data sources and methodology and make some modifications.

Total Student Aid and Nonfederal Loans in 2018 Dollars (in Millions), Undergraduate and Graduate Students Combined, 1988-89 to 2018-19, Selected Years.

Academic Year 88-89 98-99 08-09 13-14 14-15 1 5-16 16-17 17-18 Preliminary 18-19 10-Year % Change 30-Year % Change Federal Ald Grants 359 197% $9,518 $869 $11,169 $948 $20,956 $868 $33,957 $791 $32,395 $775 $30,157 $774 $28,163 $768 $29,517 $755 328,244 | $755 -13% -13% 973 III $389 $229 $3,940 $26.454 $1,836 $12,377 $12,952 $47,700 $13,068 $46,238 $13,334 $44,264 $12.613 $41,544 $12,015 $42,287 $12,355 $41,354 2148 573% 234% $13,940 58% $1,858 $17,353 16% $1,652 $25,183 $16.832 $4,566 $928 $22,678 $52.276 $13,161 -47% 6% $1,192 46% 982% Pell Grants FSEOG LEAP Academic Competitiveness Grants SMART Grants Veterans Total Federal Grants Loans Perkins Loans Subsidized Unsubsidized Parent PLUS Grad PLUS Total Federal Loans Federal Work-Study Education Tax Benefits Total Federal Ald State Grants Institutional Grants Private and Employer Grants Total Federal, State, Institutional, and Other Ald Nonfederal Loans Total Student Ald and Nonfederal Loans 121% $48,233 356% $20,403 $1,251 $1,101 $37,840 $46,313 $8,808 $4,957 $99,019 $1.116 $14,170 $140,759 $9,811 $36,297 $14,240 $1,264 $28,527 $59,685 $11,094 $8,747 $109,317 $1,058 $19,930 $178,005 $10,721 $52,126 $15,920 $1,227 $26,086 $55,773 $11,335 $8,833 $103,254 $1,038 $19,060 $169,591 $11,115 $54,768 $15,980 $1,104 $24,239 $53,561 $12,631 $9,338 $100,873 $1,036 $18,130 $164,303 $11,339 $67,720 $16,340 $649 $21,529 $50,218 $13.125 $10,599 $96.120 $1,010 $16,630 $156,046 $12,288 $62,984 $17,030 $20,198 349,002 312,898 310,957 $93,056 $1,010 $16,300 $151,720 $12,589 $64,666 $17,040 - 19% 15% $99,143 $1,027 $16,770 $158,485 $11,507 $60,612 $16,610 $34,031 33,647 38,464 $1,490 $5,880 $69,311 $5,692 $21,417 $7,030 28% 78% 346% 245% 664% 1044% 20% 416% $47,632 - $47,632 $103,451 $4,500 $107,951 $201.107 $13,300 $214,407 $256,772 $10,300 $267,072 $251,453 $10,700 $262,153 $249,703 $247,215 $11,300 $11,800 $261,003 $259,015 $248,348 $12,300 $260,648 $246,016 $13,100 $259,116 22% -2% 21% 4444

Grants, Loans, and Other Aid

Loans (including both federal and nonfederal) fell from 47% of the funds undergraduate students used to
supplement their own and their family resources in 2008-09 to 38% in 2013-14 and to 33% in 2018-19.

Undergraduate Students 4795 Percentage of Total Funds Grants Loans 45% 334 Other Aid 8% 0% 1 98-99 00-01 02-03 04-05 05-07 08-09 10-11 . 1-13 14-15 16-17 18-19

Graduate Students 68% 654 Loans Percentage of Total Funds 314 _ Grants 27% Lemmi_ Other Aid 02-08 099899 00-01 04-05 05-07 08 09 10 11 12-13 14 15 16-17 18-19

Grants rose from 45% of total undergraduate
funding in 2008-09 to 53% in 2013-14 and to 59%
in 2018-19.

In contrast, between 1998-99 and 2018-19, loans
consistently made up 63% to 70% of the funds
graduate students used to supplement their own
resources to finance their studies.
Grants have been the source of 26% to 33% of
funding for graduate students over this 20-year
period.
In 2018-19, the combination of federal tax credits
and Federal Work-Study (FWS) made up 8% of all
student aid and nonfederal loans for undergraduate
students and 3% for graduate students.

What is a Star Schema?

In the Star Schema, the center of the star can have one fact table and a number of associated dimension tables. It is known as star schema as its structure resembles a star. The star schema is the simplest type of Data Warehouse schema. It is also known as Star Join Schema and is optimized for querying large data sets.

Dimension Table Dimension Table Dealer Dealer_ID Location_ID Country ID Dealer_NM Dealer_CNTCT Date Dim Date ID Year Month Quarter Date Fact Table Revenue Dealer ID Model_ID Branch_ID Date ID Units Sold Revenue Dimension Table Dimension Table Branch Dim Branch ID Name Address Country Product Product ID Product Name Model_ID Variant ID

For example, as you can see in the above-given image that fact table is at the center which contains keys to every dimension table like Dealer_ID, Model ID, Date_ID, Product_ID, Branch_ID & other attributes like Units sold and revenue.

Characteristics of Star Schema:

• Every dimension in a star schema is represented with the only one-dimension table.
• The dimension table should contain the set of attributes.
• The dimension table is joined to the fact table using a foreign key
• The dimension table are not joined to each other
• Fact table would contain key and measure
• The Star schema is easy to understand and provides optimal disk usage.
• The dimension tables are not normalized. For instance, in the above figure, Country_ID does not have Country lookup table as an OLTP design would have.
• The schema is widely supported by BI Tools

Student Enrollment Star Schema.

Instructor_Group Instructor Group_key EL Timestamp Academic_Calendar_Dim Time key Academic Period Academic Period Dese Academic Year Academic Year Deso Semester Start Semester End Aid Year Aid Year_Desc Calendar Year HED_Regdate ACA SEM ETL Timestamp Relationship 100 Relationship Student Enrollment_Fact 4 Time_key 4. Student_key 4- Person_key Course_key Instructor Group_key Student_ld Program Program Desc Course_Credits Generated Credits Credit Hrs Attempted Credit_Hrs_Earned Final Grade Received Grade Points Received Course Start Date Course End Date Completed Flag IPEDS Values IPEDS_Values Desc ETL Timestamp Student_Dim Student key Student Id Person Uid Academic Period Academic_Period_Desc Program Code Program Code Desc Primary Program Ind Year Admitted Year_Admitted_Desc Age Admitted Catalog Academic Period Catalog Academic Period Desa Award Category Award Category_Desc New Student Ind Academic Period Admited Academic_Period_Admited Desc Admissions_Population Admissions Population Des Degree_Code Degree_Code Desc First Major_Code First Major_code_Deso First Major_CIP Code First_Major_CIP_Desc First Major Conct First Major_Conct_Desc First Major_Conc2 First Major Conc2 Desc First Major_Conc3 First Major_Conc3_Desc First_Major_Dept_Code First Major Dept Code Desc First Major_College_Code First_Major_College_code_Deso First_Major_Campus_Code First_Major_Campus_code_Dese Second_Major_code Second Major Desc Person Dim Personkey Person_ld Person Uid First Name Last Name Middle_Initial Middle Name Name Suffix Full Name FMIL Full_Name_LFMI Netid Gender Gender_Desc Birth Date Current Age Deceased States Deceased Date Confidentiality Ind Primary Ethnicity Primary Ethnicity Desc Prim Ethnicity Category Prim_Ethnicity_Category_Desc Hispanie Latino Ethnicity Ind IPEDS Values IPED S_Values_Desc Hispanic American Indian Asian African American Native_Hawaiian White Non Resident Alien Race Category Count Race Count Race Ethnicity Confirm Ind Race Ethnicity_Confirm_Date Citizenship Type Citizenship_type_Desc Citizenship Ind Nation of Citizenship Nation of citizenship Dese Visa Type Visa_Type_Desc Veteran_Category Veteran_Category_Desc Veteran_Special_Disabled_ind Military Separation Date MA_Address_Type MA_Address_Type_Desc MA Active Address Ind MA Street Linet MA_Street_Line2 MA Street Line3 MA City MA State Province MA_State_Province_Desc MA Postal Code MA_County_Code MA County Code Desc Relationship92 Course_Dim Course_key Academie Period Academic_Period_Desc Sub Academie Period Sub_Academic_Period_Desc Course Reference Number Subject Code Subject_Code_Desc Course Number Section Number Course Title Short Course_Status Code Course_Status_code_Dese Campus_Code Campus_code_Desc College_Code College_code_Desc Department_Code Department_Code_Deso Course_Credits Generated_Credits Min_Credit_Hrs

Course Enrollment Star Schema.

Instructor Group Instructor_Group_key ETL_Timestamp Academic Calendar_Dim e Time key Academic Period Academic Period_Desc Academic Year Academic Year_Desc Semester_Start Semester_End Aid Year Aid_Year_Desc Calendar_Year HED_Regdate ACA SEM ETL_Timestamp Course_Enrollment_Fact a Department_key q Instructor_Group_key Time_key - Course_key Cross_List_Group_Code Total_Enrollment Avg Grade of Students African American_Enrollment White_Enrollment American Indian Enrollment Hispanic Enrollment Grade A plus stu count Grade_A_stucount Grade A minus_stucount Grade_B_plus_stu count Grade_B_stucount Grade_B_minus_stucount Grade_C_plus_stu count Grade_c_stucount Grade_C_minus_stucount Grade_D_stucount Grade_E_stu count Grade_F_stucount Grade_NR_s tucount Grade_NC_stucount Course_Dim Course_key Academic Period Academic Period Desc Sub_Academic_Period Sub Academic Period Desc Course_Reference_Number Subject Code Subject Code_Desc Course_Number Section Number Course_Title_Short Course_Status_Code Course_Status_code_Desc Campus_Code Campus_code_Desc College_Code College Code Desc Department_Code Department Code Desc Course_Credits Generated Credits Min_Credit_Hrs Max Credit Hrs Min_Billing_Hrs Max Billing_Hrs Schedule_Type Schedule_type_Desc Instructional_Method Code Instructional_Method_code_Desc Integration_code Session Id Department Dim Department key Department_Code Department_code_Desc College Code College_Code_Desc Organization_Level_2 Organization_Desc_2 Campus_Code Campus Code Desc ETL_Timestamp

So , this project helps us to understand the US Student Aid data.

The students which does not able to do higher studies due to lack of resources like money, loans providibility etc.