Student Financial Services Data Mart

The Student Financial Services Data Mart is comprised of two subject areas: Student Financial Services and Financial Aid.

Student Financial Services is used by institutions to manage student receivables, billing, and collections, as well as the transactions that are supported by reports, such as Receipts Per Day and Refund Customers Trial Balance by Students. This subject area of the Campus Solutions Warehouse links student financial information to dimensions such as Person, Account, and Product, enabling the analysis of student financial aid information from various viewpoints.

The Financial Aid subject area automates federal and institutional aid processing and helps you to manage student financial aid activity for students and applicants. Reports for financial aid application provide details such as Year to Date Disbursement, Overawards, and Pell Originations. This subject area links financial aid information to dimensions such as Person, Account and Product, enabling you to perform analysis of aid information from various viewpoints.

With the Student Financial Services Data Mart you can answer questions such as:

What is the total amount of bills for a student or an external organization?
What is the total amount of tuition charged for a particular course?

Award Business Process

Student Financial Services Data Mart is related to the Award business process. The Award Business Process fulfills an institutions need to track and report student's payments for classes taken as well as financial aid awards given to students for an academic year.

Student Financial Services Data Mart Delivered Fact Tables

The following table describes the delivered Student Financial Services Data Mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	PeopleSoft Source	Description	Helps Answer
Award Disbursement	F_AWD_DISB	PS_STDNT_AWARDS PS_STDNT_AWRD_DISB	Contains term-based detail information associated with a student award such as amount, status, disbursement, aid type, and so forth. There is one row of data per person, institution, academic career, financial aid item type, aid year and disbursement ID.	Number and amount of awards accepted by source (federal, state, or institutional, for example) and type (grants, scholarships, loans, or work, for example) or for an individual item type. Average award accepted. Number and amount of awards declined by source (e.g., federal, state, institutional) and type (e.g., grants, scholarships, loans, work) or for an individual item type Declined awards as a percentage of total awards offered. Students with undisbursed awards for a particular term. Students with pending disbursements for a particular time period. Students with unaccepted awards.
Award Summary	F_AWD_SNPSHT	Award Disbursement (F_AWD_DISB) Fact Table	This snapshot summary table provides Award Amount and Award Count issued to a person for an academic year. Award amount represents the amount of a financial aid award to a student. This can represent the offered, disbursed, accepted or authorized amount depending on the award status. Award Count represents the count of financial aid award to a student. This can represent the offered, disbursed, accepted or authorized counts depending on the award status. The frequency at which this table is loaded determines the snapshot period. The snapshot period is determined by the last run of the ETL process and the new run of the ETL process. There is one row of data per snapshot date, academic career, financial aid item type, institution, person and aid year.	Total award offered, accepted, disbursed, and authorized by institution, academic career and aid year. Top 10 careers with highest award offered by aid year and institution. Number of awards offered by institution, academic career, and aid year.
Bill Summary	F_BIL_SNPSHT	PS_BI_BILLING_LINE PS_BI_BILL_HEADER	Contains accumulated snapshot billing information for a student or external organization between a period of time. The information the table provides is the total summary billing information (billed and paid amount) for a student or external organization for a specific snapshot period. There is one row of data per snapshot date, business unit, person, external organization and student/organization type. Source aggregated data is defined using time interval parameters, namely, FROM_DATE and TO_DATE. The snapshot data is identified by a SNAP_DT_SID. Data that is present in the source between these dates is picked up by the source query and is aggregated over the total amount billed to a student or external organization per month. Then it is populated along with the total amount of payments made by a student or external organization, the number of bills unpaid, the number of bills paid late.	Total billed and paid amount by business unit, person or external organization in a specific time frame (snapshot period). Total billed/paid amount by career, program, plan for a specific time frame (snapshot period). Top billed career in the snapshot period.
Credit History	F_CREDIT_HIST	PS_CREDIT_HISTORY	This table allows you to analyze current credit history information and credit history trends for a given business unit, account type, student, external org, and so forth. Contains information on student and external organization accounts by aging set and aging category. This fact table also enables you to track whether the credit history process has assigned any service indicators to a particular student. There is one row of data per business unit, person or external organization, student/organization type, account number, account term, effective date (date when process credit history was run) and aging set category.	Students that are regularly delinquent on payments based on credit history trends. Total balance past due per external organization/student when process credit history is run for a given effective date. Number of student accounts with service indicators placed on them based on credit history process. Number of accounts with cleared service indicators. Student accounts with service indicators placed by process credit history (with detailed service indicator code and reasons). Balance past due for a particular aging category and aging set for a given student and effective date when process credit history is run.
Pending Payments	F_PYMNT_PENDING	PS_S_PAYMENT_TBL PS_ITEM_SF	Contains summary information of payment and charges. This table aggregates payment amount and charge amount by business unit, person/external organization, student/organization type, academic year and term.	Total amount of payment/charges by SFBU, academic year and term. Percentage of total amount that corresponds to student accounts and percentage that corresponds to external organization.
SF General Ledger Accounting Entries	F_SF_ACCOUNT_LN	PS_SF_ACCTG_LN PS_JRNL_HEADER	Contains the accounting entries produced by the Student Financials system and enables you to analyze the accounting transactions generated (by business unit, ledger, fiscal year, accounting period and so forth) and whether such transactions have been transferred to the general ledger. There is one row of data per run date, sequence number and student financials line number.	Transactions passed/not passed to the general ledger for a specified date range. Also, number of transactions and transactions amount. Transactions passed to the general ledger analysis, by department, chartfield1, chartfield2, chartfield3 and/or operating unit.
Payment Detail	F_SF_PAYMENT	PS_S_PAYMENT_TBL PS_ITEM_SF	Contains information about payments at a detail level and enables you to analyze payments by business unit, payment method, item type, term, academic year, and so forth. There is one row of data per business unit, person or external organization, student/organization type and payment ID.	Number of payments from self-service and lockbox by SF business unit, term and academic/calendar year. Total amount of payment (checks or credit card, for example) applied to a student ' account in this SFBU, by term, academic or calendar year, career, program, or college. Students with unapplied payments. Students with an overall credit balance and unapplied payments.
Student Financials Transactions	F_SF_TRAN	PS_ITEM_SF	Contains information about student financial transactions and enables you to analyze number of transactions, transaction amounts, paid amounts and encumbered amounts by academic year, calendar year, accounting term, account type, billing date, item type and so forth. This table serves as a roll-up, or summarization, of the Student Financial Transaction Detail fact table. There is one row of data per business unit, person/external organization, student/organization type and transaction number (item number).	Total tuition charged for a particular academic/calendar year, institution, or career. Top 10 external organizations with higher waiver amounts in a particular academic or calendar year. Total tuition/encumbered amount for this term, career, or program. Average tuition (tuition and fees) charged for students in this SFBU, this term, academic or calendar year, career, program or college. Total amount of waivers applied against tuition (tuition and fees) in this SFBU, term, academic or calendar year, career, program, or college. Number of enrolled students who have not had tuition calculated.
Transaction Detail	F_SF_TRAN_DTL	PS_ITEM_LINE_SF PS_ITEM_SF	Contains information about student financial transactions at a detail level and enables you to analyze line items by business unit, item type, account type, term, academic year, and so forth. There is one row of data per business unit, person/external organization, student/organization type, transaction number and line sequence number.	Number of transactions not posted to the general ledger, by business unit, career, or program. Number of transactions/transaction amount set to "aging transactions" in the last month, by business unit, career, or program. Number of transaction with a refund and amount, by calendar or academic year. Amount of disputed transaction by career, program, or college. Top 10 students with higher dispute amounts.
Payments and Charges Cross Reference	F_PYMNT_CHARGES	PS_ITEM_XREF PS_ITEM_SF	Provides information about payments applied to charges within student financials and enables you to analyze payments applied to charges by business unit, item type, account type, term, academic year, and so forth. There is one row of data per business unit, person/external organization, student/organization type, item number charge and item number payment.	Payment method used to apply payments to specific items. Total amount of payments applied to a given item charge by business unit, account term, item term, academic career, academic year, program, and so forth. Federal Financial Aid audits to provide evidence that financial aid was used to pay for eligible charges or ensure tuition scholarships were used to pay for tuition and course/class fees.

Student Financial Services Data Mart Delivered Dimension Tables

The following table describes the delivered Student Financial Services Data Mart dimension tables.

Dimension Name	Dimension Record Name	Description
Account Term	D_ACCT_TERM	Contains term value data that relates to an institution quarter or semester definition regardless of academic unit.
Account Type	D_ACCT_TYPE	Contains account types used for student financial transactions.
Federal Aid Year	D_AID_YR	Contains details about the federal aid year.
Award	D_AWD	Contains a list of all the awards which an Institution confers to its students.
Award Period	D_AWD_PRD	Contains the different award periods that can be used for financial aid awards.
Award Status	D_AWD_STAT	Contains details of the status of a financial aid award.
Contract	D_CONTRACT	Provides a list of contracts.
Disbursement	D_DISB	Contains the disbursement plan and split code values established for each financial aid item type, by career.
Dependency Status	D_DPNDCY_STAT	Defines the dependency information of the student applying for financial aid.
Financial Aid Item Type	D_FA_ITEM_TYPE	Contains attributes that may be associated with financial aid item types used to define the type of financial aid award.
Financial Aid Application	D_FED_AID_APP	Contains attributes necessary to maintain the FAFSA (Free Application for Federal Student Aid) application and changes to Institutional Student Information Record (ISIR) data.
Financial Aid Type	D_FIN_AID_TYPE	Contains the category or type of financial aid offered by a financial aid item type.
Item Code	D_ITEM_CODE	Provides a list of item codes.
Item Type	D_ITEM_TYPE	Contains details associated with item types, which are used to define the type of financial transactions.
Line Reason	D_LINE_REAS	Provides a list of line item reason codes.
Line Action	D_LINE_ACTN	Provides a list of line item actions.
Line Status	D_LINE_STAT	Provides a list of line item status values
Payment Method	D_PYMNT_MTD	Contains a list of payment methods.
Tuition Group	D_SEL_GRP	Provides a list of tuition groups.
Session Code	D_SESSION_CODE	Contains a list of all of the session codes.
Student Aid Attributes	D_STU_AID_ATTR	Defines records for students applying for aid and contains the attributes, high level information relating to the application, awarding and processing of financial aid for the year. The status fields indicate if aid-processing steps such as SSNMatch, Verification Status, Scholarship status etc., have been completed for a particular application.
Student Financial Aid Terms	D_STU_FA_TERM	Defines a consolidated view of student term data for Financial Aid processing purposes.