SIS Reporting

Charge and PaymentDatamart

Introduction

The Charge and PaymentDatamart provides a new way to look at SIS Student Financial and Financial Aid data, along with related Student Records information. It was created mainly to assist the graduate school with answering questions about how their students are funding tuition. However, it includes all students--undergraduate, graduate and professional--and all types of charges and payments. This is not new data; it is all information that is already available to you in the SIS Reporting Database. However, the data has been put together in a different way to enable the building of reports that connect data in ways that were not possible before.

The datamart is built in a star schema. This is the second star schema built in the SIS Reporting database (see Class Enrollment Datamart.) Star schemas have several advantages:

  • Speed. The datamart is much faster than our traditional reporting views. A report combining Student Financials, Student Records, and Financial Aid data would likely be very slow. The datamart should be considerably faster. If users do report performance problems with reports they create, we can take a look at your report and suggest ways to make it faster.
  • There are fewer tables to sort through to find what you are looking for. While this datamart is more complex than the Class Enrollment Datamart, with 11 folders instead of 5, this is still a vast improvement over the 123 folders currently available in the Student Records, Student Financials and Financial Aid business areas. In addition, the folders generally have fewer columns to choose from—just the most commonly used items.
  • You don’t have to worry about joins. Joins are a difficult concept to understand, and sometimes in Discoverer you can be asked which join you want to use to connect the data in two folders. If you make the wrong choice, you might get the wrong data back. In the datamart, the folders connect in one way only, so you will never be asked about joins. There is no need to understand the difference between standard and outer joins.
  • Sums from the fact table are automatically recalculated when you change the columns in your query (more on the fact table later.)

On the other hand, the datamart contains only a subset of the data available in the SIS Reporting Database. It is good at answering the questions it was designed for, but may not answer all your questions. Also, you cannot pull in data from other folders from outside the datamart.

Charge/PaymentDatamart – Background

A student account in SIS Student Financials contains multiple Items. An item can be a Charge, such as tuition, or a library late book fee, or a Payment. In the datamart we use the term Payment in a broad sense to mean anything that is used to satisfy a charge. Thus, in addition to the things we normally think of as payments (e.g. cash, checks, credit cards), SIS payments may include such things as Loans and Financial Aid, or a credit because the charge has been billed to a third party (corporate account).

SIS has built-in rules that govern what types of payments can be used to satisfy what types of charges, and using these rules applies payments to charges. A payment may be used to satisfy multiple charges. In the same way, a single charge may be satisfied by multiple payments. This is called the Payment Cross-Reference. The following images illustratea simplified example of how it works.

SIS determines which charges to apply the payments to:

(Continued on next page)

The Cross-Reference shows the amount applied by each payment to each charge:

Financial Aid awards, such as that in the illustration above, are funded by one or more PTAEOs. Most Financial Aid item types are tied to a single PTAEO, but the graduate schools can choose what PTAEOs to use to fund a Financial Aid award. The item types used for Departmental Aid are very broad and not very useful for reporting purposes. The schools put in the PTAEOs that they want to use to pay for the aid, and this information is more meaningful to them.

In the following illustration, the school has chosen to fund the $9,000 financial aid award from three different PTAEOs, one paying 10%, one 25%, and the remaining 65% from the third.

While all this information is already stored in SIS, nothing there will tell you how much of each PTAEO was used to pay the Tuition, and how much the Refund for living expenses. When more than one PTAEO is used, as in this example, the datamart allocates the amount paid by each PTAEO proportionately among the charges the award is paying for, as in the following illustration:

There are even more complex situations. If a student’s aid is being funded by a grant, the rules of the grant may require that the aid be paid using a clearing account. The SIS award will only show the clearing account, which doesn’t give information about how the aid is actually being funded. For those schools using the Student Aid Funding Module (SAFM), the actual PTAEO to ultimately be charged is recorded there. In this case the datamart links to SAFM in order to obtain this information.

(continued on next page)

Class Enrollment Datamart – the Star Schema

The Charge/Payment datamart has its own business area in Discoverer:

However, the relationship between the folders is not clear as displayed in Discoverer. The following illustration shows how the folders are connected:

This diagram makes it clearer why this is called a star schema. A star schema consists of one fact table, and several dimensions, or tables of attributes.

See the SIS Data Catalog for full details about the folders and items available in the datamart. As additional fields are added, the catalog will be updated. We will not attempt to duplicate all that information in this document. Follow this link: Charge/PaymentDatamart.

Charge/Payment Datamart Folders

Below is a brief description of each of the folders in the datamart.

  • The Charges and Paymentsfolder contains the measures, or numeric amounts, that can be reported on. The principal measure is Amount. All the other measures are derived from Amount for convenience, and will be discussed later in this document. In addition this folder contains a few miscellaneous items that didn’t fit in any of the other folders, such as Aid_Year, Balancing_Transaction_Flag, GA_Batch_Number, and SAFM_Assistantship, as well as Charge_Item_Number andPayment_Item_Number.
  • The Students folder contains demographic data about students: name, ID numbers, addresses, phone numbers, etc. It contains essentially the same information as in the SR_Students folder used throughout the Reporting Database.
  • Most accounts in Student Financials are Student accounts, but there are also Corporate Accounts. In those cases where the University has agreed to bill a third party for student charges, the Corporate accounts contain the charges, adjustments and payments of the third party, and you would use this folder to obtain the name and organization id of the corporate entity. If you are looking at a student account transaction, the fields in this folder will be blank. Similarly, if you are looking at a Corporate Account, there will be no information about the student from the Students folder or the Student Terms folder.
  • 3d Party Payers is very similar to Corporate Accounts. You use this folder when you are looking at student account transactions that have been transferred to a Corporate Account (this shows as a “payment” on the student account) and you want to know the name of the third party.
  • Use the Terms folder to select the date range of data you wish to report on. Selections can be made by Term, Academic_Year, Calendar_Year, and the Term_Fiscal_Year, for example. (In Academic_Year, summer is the final term of the year. InTerm_Fiscal_Year, summer is the first term of the year.) Term_Type is a simple way to select by the type of semester: Fall, January, Spring, or Summer.
  • The Classes folder contains Schedule of Classes information about each class section. Cancelled classes are not included. This folder is only relevant when a student account contains a charge (tuition or fee) that is specific to a class—then you will be able to see information about that class here.
  • Student Terms contains term information about the students, and information about the program and plan(s) in which the student was activated for the term. As such, the folder does not contain all programs and plans in which a student is enrolled. A dual degree student for example may be Active in Program in two programs and plans, but they will only be activated in one per term; that’s the one included in the Student Terms folder. Likewise, a student’s program and plan may change during a term; the folder contains the latest program and plan information effective during the term.
  • Each transaction contains both charge and payment information. The Charges folder contains information about the Charge side of the transaction, namely the Charge_Item_Type and its attributes, such as the Item_Type_Code and up to three Item_Type_Keywords.
  • The Payments folder contains similar information about the Payment side of the transaction. For those transactions Financial Aid transactions where the item type in Student Financials and Financial Aid are not the same, the folder includes the Related_FA_Item_Type and its description.
  • For Financial Aid transactions, the PTAEOs folder contains the PTAEOs used to fund the financial aid. In addition to Project, Task, Award, Expenditure_Type and Organization and their descriptions, this folder contains other related attributes such as Funding_Type, Award_Prefix, and Organization_Prefix and its description. It also contains the SAFM_Academic_Group and its description and, for those transactions which are paid through a clearing account, the components of the clearing account PTAEO.
  • GA Accounting Periods contains information about the accounting period in which a charge to a PTAEO for Financial Aid was posted in Grants Administration. In the unusual cases where a transaction posts more than once and in different accounting periods, only the first period will be listed.

Transaction Types

Each row in the fact table has a Charge_Transaction_Type and a Payment_Transaction_Type. These different categorizations of transactions came out of meetings with the reporting working group and are intended to simplify reporting. Each transaction type (e.g. Endowments) has a corresponding column in the fact table to be used to easily sum the amounts of these types of transactions. In the example of the Endowments column, if the payment_transaction_type of the row is equal to Endowments, then the Endowments column contains the amount of the transaction. If not, it contains zero.

The logic that determines the correct transaction_type for each row can be complex. For a description of how transaction type determined, see the Data Catalog.

If you find that the transaction types in the datamart do not meet your needs, the Reporting team can help you create your own.

Special Considerations When Creating a Worksheet using the Datamart

At this point, the SIS team has not built any workbooks based on the datamart for generalized use. The needs of each school are distinct, so no requirements emerged during the project for a report that would be useful to all or most.

The goal of the datamart however is for it to be simple for users to create their own reports from it. We recognize of course that not all users will be comfortable doing so. If you would like assistance from the reporting team to create a workbook, please contact us at to set up an appointment.

To create a new Discoverer worksheet using the Datamart, select the Charge/Payment Datamart business area (the list of Business Areas you see in Discoverer depends on your security).

When creating a new Discoverer worksheet, we recommend that you select a column from the Charge and Payments (Fact Table) folder first. Remember that when creating a new Discoverer worksheet, once you select a column to be in your query, all the other folders gray out except for those that are joined with the folder you are working with. Since all the folders in the datamart are joined with the fact table only, they will mostly gray out until you select from the fact table.

If a student has more than one plan or sub-plan for the active program in a term, the plans are combined in one field. This is done to prevent duplicate rows and incorrect amounts being created in reports because of a student having more than one plan. This means, however, if you want to limit your query to a certain plan, you must be careful how you do it.

For example, if you want to search for the English (BA) plan, the following condition will return incomplete data:

The problem with this condition is that it returnsonly the rows where a student has only one plan and not English (BA) combined with another plan.

The list of values for Academic_Plans and Academic_Plans_Desc includes all the existing combinations in the datamart. So when entering the value for your condition, choose Select Multiple Values.

This brings up the same dialog box that you often get when you click the button next to a parameter. Then search for all values that contain English (BA), and use the button to select all the combinations:

Particular data elements only appear in one of the folders of the star. For example, the Primary_Name of the student can only be obtained from the Students dimension. Likewise, if you wish to know the term in which a particular class was held, you will have to select Term from the Terms folder.

This may seem inconvenient at first, but it reduces the clutter of repeated elements, and makes it easier to find what you are looking for, as well as improving the speed of the datamart.

The Charges and PaymentsDatamart does not include all terms available in SIS. At this time complete data for Summer2013 (1136) forward is included. (Isolated transactions from earlier terms may also be included for data consistency.)

Only financial aid that has been disbursed to the student account is visible in the datamart. You cannot use the datamart to see planned aid.

We hope you find the datamart useful. If you need any help using it, the Reporting team can provide one-on-one assistance. Please contact us at to set up an appointment.

November 2014Page 1