Using Databases

• Using the Undergraduate Applicant Database
• Using the SRTE Database
• Using the IBIS Financial Database

Using the Undergraduate Applicant Database

Below is information on how to use the Undergraduate Applicant database in the Data Warehouse:

• When looking for Admissions data, check the "applicants" table first. It is the main data warehouse table and contains most of the information that is typically requested. It has a little information from most of the other Admissions Warehouse tables.
• Two weeks after a semester begins, Admissions data for that semester is no longer updated. It is "frozen" or copied until the end of the academic year. This is important when verifying data that is pulled. If the data doesn't match for a particular applicant, don't forget to check when his record was last updated.
• The SAT test scores are on the "applicants" table in addition to the SAT table. The difference is that the scores on the applicant table are the always the ones used to determine how a student is reviewed and are usually official. The SAT table has ALL SAT scores both official and non-official.
• Admissions reviews students based on SAT scores not ACT. For those students taking ACT tests only, we convert their ACT score over to an equivalent SAT. The converted ACT is then placed on the SAT table, too. Is is easy to recognize an ACT that has been converted over to an SAT. When it is converted we add a '1' to it. This does not significantly alter their score but still allows us to quickly identify it.
  • Example: If an applicant only takes the ACT and receives the following scores--Verbal 27, Math 30--we convert the score over to an equivalent SAT to get Verbal 620, Math 670.  We then add another 1 to each to change them to get Verbal 621, Math 671.  This is helpful when doing statistics on scores and also saves you from having to join to the ACT table to get their scores.
• Tables "letters" and "comments" are very large and take a long time to create. In an effort to reduce the run time for AIS, Admissions has agreed not to pull this information weekly like the other files. It is pulled only once each semester just before the file is "frozen".
• The "SAMX" data listed on the "applicants" table represents the most recent Admissions information. The "Matrix" table includes this information but also shows all of the entries, not just the most recent. This is important because in most cases you will need the 'SAMX' information and since it is on the main file (applicants) you don't need to merge the two tables together.

Using the SRTE Database

Below is information on how to use the SRTE database in the Data Warehouse:

• Student responses in fields responses_dept1 through responses_dept15 are valid only if a corresponding pool item number occurs in fields dept_numbers_used1 through dept_numbers_used15.
• If a one character code occurs in field instructor_code, then only one corresponding instructor's name occurs in the fields instructor1 through instructor5. If the field instructor_code is blank, then all instructors' names for the course will appear.
• Records are present only for SRTE scan sheets for which the student completely coded the fields for semester, course number, and section number.
• If the course and section information coded on the SRTE scan sheet does not match a course registration listing, then the enrollment and instructor name fields are blank.

Using the IBIS Financial Database

• Types of Subobjects
• Detail Tables in the IBIS Financial Database
• IBIS_Detail #2 Data Table Field Definitions
• Operators
• Joins/Relationships
• Tips for Using Microsoft Access to Query the IBIS Financial Database

Types of Subobjects

In order to determine which table in the IBIS Financial Database contains the information you need, it is necessary to know what types of subobjects are related to an account. Since more than one type of subobject can be related to an account, it is necessary to separate them into different tables. The 6 types of subobjects are:

Subobject Type	Values in Tables	AUSO Option (Screen 3)
Account (Acct-Sub)	SACT	F5
Cost Center (Cctr-Sub)	CCTR	F6
Cost Center/Activity		F7
Agency (Global)	SAGC	F8
Fund Type (Global)	SFTY	F9
Budget Fund Range (Global)	SBUD	F10

The following AUSO screens take you through the steps involved in relating subobjects.

Screen 1

Screen 2

Screen 3

Detail Tables in the IBIS Financial Database

IBIS_DETAIL #1 Table (with Account Subobjects where applicable)

• Contains ALL accounts.
• Contains Account Subobject details (Acct-Sub) when they exist.
• If there is no Account Subobject detail (Acct-Sub), then Account/University Detail (Univ) is used.

IBIS_DETAIL #2 Table (with Cost Centers and Cost Center Subobjects where applicable)

• Contains ALL accounts.
• Contains Cost Center Subobject details (Cctr-Sub) when they exist.
• If there is no Cost Center Subobject detail (Cctr-Sub), then Cost Center detail (Cctr) is used.
• If there is no Cost Center Subobject detail and no Cost Center detail, then Account/University Detail (Univ) is used.

Notes:

• Cost Center/Activity Subobjects (F7 from AUSO Screen 3) do not currently exist.
• The Globally assigned subobjects (listed below) will be handled differently and do NOT go into IBIS_DETAIL #1. These types of subobjects will be in the SUBOBJECT#1 table:
  • Agency Subobjects (SAGC)
  • Fund Type Subobjects (SFTY)
  • Budget Fund Range Subobjects (SBUD)

To view a list of field names, labels, descriptions, values etc. for the IBIS_DETAIL tables, visit the Field Definitions, Formats and Values for IBIS_DETAIL#2 section below.

Example of IBIS_DETAIL #1 and IBIS_DETAIL #2 Table Contents

Admin Area: 046 Account: 0803311 UP10010
	Cost Centers: Project X Project Y
		Cost Center Subobjects to Project Y: Object Code 0336 Smith Jones Object Code 0452 PC Mac
	Account Subobjects: Object Code 0030 UP Non-UP
Account: 0104259 UP10010 (no Cost Centers, no Subobjects)

The IBIS_DETAIL #1 Table would look like the following with some selected sample data from the account layout example.

Fiscal Year	Account	Object Code	Subobject	Amount	Abbreviation
19971998 19971998	0803311 UP10010 0803311 UP10010	0030 0030	UP NON-UP	5000.00 5000.00	Acct-Sub Acct-Sub
19971998	0803311 UP10010	0452		6500.00	Univ
19971998	0803311 UP10010	0301		100.00	Univ
19971998	0104259 UP10010	0301		50.00	Univ

Each row represents a transaction:

• The first transaction for $10,000 was resolved into 2 Account Subobjects, UP for $5,000 and NON-UP for $5,000.
• The second row is a $6,500 transaction.
• The third row is a $100 transaction.
• The fourth row is a $50 transaction.

The IBIS_DETAIL #2 Table would look like the following with some selected sample data from the previous account layout example.

Fiscal Year	Account	Object Code	Cost Center	Subobject	Amount	Abbreviation
19971998	0803311 UP10010	0030	PROJECT Y		10000.00	Cctr
19971998 19971998	0803311 UP10010 0803311 UP10010	0452 0452	PROJECT Y PROJECT Y	PC MAC	3000.00 3500.00	Ccrt-Sub Ccrt-Sub
19971998 19971998	0803311 UP10010 0803311 UP10010	0301 0301	PROJECT X PROJECT Y		75.00 25.00	Cctr Cctr
19971998	0104259 UP10010	0301			50.00	Univ

Each row represents a transaction:

• The first transaction for $10,000 was resolved into the Cost Center Project Y.
• The second row is a $6,500 transaction which was resolved into the PC Subobject to Cost Center Project Y for $3,000 (Cctr-Sub) and the MAC Subobject to Cost Center Project Y for $3,500 (Cctr-Sub).
• In the third row, the $100 transaction was resolved into Cost Center Project X for $75 (Cctr) and into Cost Center Project Y for the remaining $25 (Cctr).
• Since Account 0104259 UP10010 on the fourth row does not have any Cost Centers or any type of Subobjects whatsoever, it will always go directly into a University (Central Accounting) detail which covers this $50 transaction.

Comparison Between IBIS_DETAIL #1 and IBIS_Detail #2

What one can observe when comparing the contents of IBIS_DETAIL#1 and IBIS_DETAIL#2 is that the grand total amounts should equal. It doesn't matter how complicated the resolution was, the total transaction amount is always the same. The grand total for Account 0803311 in the IBIS_DETAIL#1 example is $16,650, and the grand total for Account 0803311 in the IBIS_DETAIL#2 example is also $16,650. There are different subtotal amounts, but the total by transaction and by the grand total should be equal.

Transaction #	Transaction Total	IBIS_DETAIL #1	IBIS_DETAIL #2
1	$10,000	$5,000 $5,000	$10,000
2	$6,500	$6,500	$3,000 $3,500
3	$100	$100	$75 $25
4	$50	$50	$50

IBIS_Detail #2 Data Table Field Definitions

The table below details the IBIS_DETAIL #2 Data Table field definitions. In this table, I = Index. Additionally, the subobject #2 table contains the same fields. The fields for the ibis_detail #1 table and the subobject #1 table are the same except that the CODE-CCTR (cost center) field is not in these tables. Finally, for dates less than or equal to 12/31/99, the m/d/yy format is correct.  For dates greater than or equal to 1/1/2000, the m/d/yyyy date format is correct.

Index	Field Name	Label	Type	Length	Format Example	Values	Description
I	CODE-BUDG-ADMIN-AREA	Admin Area	A	3.0	015 (need all 3 characters)		Admin area associated with the budget contained in the NUMB-ACCT field.
I	DEPT-MNEMONIC	Dept Code	A	7.0			Department-level mnemonic on which security is based
I	NUMB-ACCT	Account	A	16.0	0803311 UP10010		The number of the account. The first 10 bytes is the CODE-CBUD (budget number). The last 6 bytes is the CODE-FUND (fund number).
I	DATE-FNST-FISCAL-YR	Fiscal Year	A	8.0	19961997		The fiscal year
	DATE-FNST-DTL-POSTING	Post Date	D	8.0	m/d/yy or m/d/yyyy		Date when encumbrance is committed or when the income/expense is realized.
	CODE-COBJ	Object Code	A	4.0	0105 (need all 4 characters)		Numerical code assigned by the University to identify and categorize different types of income and expenses.
I	CODE-CCTR	Cost Center	A	10.0			A 10 byte character code created by the dept/college to establish projects, tasks, cost centers.
	DESC-FNST-SUB-OBJ	Subobject	A	30.0			This field will contain the description of the sub-object. The field must be unique to CODE-COBJ and organizational entity.
	CODE-UNIV-REC-TYPE	Record Type	A	4.0		IPBD, ITBD, IACT, IENC, IENS, IENP, PPBD, PTBD, PACT, PENC, PENS, PENP, RPBD, RTBD, RACT, RENC, RENS, RENP	Code identifying a specific record type within the Univ database file. Codes starting with I mean the detail was resolved on CRFN already. Codes starting with P or R mean the detail was resolved automatically to Central Acctg, but it is still on CRFN.
	AMNT-FNST-DTL	Amount	N	11.0			This field will contain the dollar amount that the user enters at resolution time.
	CODE-FNST-DTL-TYPE	Detail Type	A	4.0		SRFC, VRES, PAYS, WAGC	The type of detail document, such as ROCR, SRFC, IDCC, etc.
	TEXT-FNST-DTL-DEPT-FREE-SPACE	Free Space	A	15.0			15 bytes of space which the dept can use as they please.
	DESC-FNST-DTL	Description	A	10+30.0			The description of the detail
	NUMB-FORM-ID	Form #	N	8.0			This is the document number created on EASY forms
	DATE-FORM-ENTERED		D	8.0	m/d/yy or m/d/yyyy		The date the form was entered
	NUMB-DOCT-FNST	Document #	N	8.0			The system-generated number of the document (starts w/8).
	NUMB-ACCT-ENCM	Encumbrance #	N	4.0			Contains an encumbrance number unique to each account number.
	NUMB-FSNT-DTL-TIEBACK		N	8.0			Detail tieback #
	NUMB-FNST-STL-CENT-REF	Central Reference #	A	7.0			A temporary field representing the central reference number
	AMNT-FNST-DTL-DISCOUNT		N	9.0			Discount amount
	DATE-FNST-DTL-DEPOSIT	Deposit Date	D	8.0	m/d/yy or m/d/yyyy		The date of the deposit. (This can be different from the posting or recording dates).
	NUMB-VEND	Vendor #	N	10.0			Permanent or misc vendor number
	NAME-VEND-ABBREV	Vendor Code	A	10.0			The code for the vendor from whom this order will be purchased.
	NUMB-VEND-DET-INVOICE	Vendor Invoice #	A	11.0			The number of the vendor's invoice.
	NUMB-ORDER-FNST	PO #	N	9.0			The number of the purchase order.
	NUMB-CHCK-FNST	Check #	N	10.0			The number of the check
	INDC-FNST-DTL-PARTIAL-RECG-RPT		A	1.0			Indicates whether the items received are only a portion of the entire order LABEL: PARTIAL RECG RPT.
	CODE-FNST-SOBJ-TYPE	Subobject Type	A	4.0		SACT, CCTR, SAGC, SFTY, SBUD	Type of Subobject assignment: Account, Cost Center, Agency (Global), Fund Type (Global), Budget Fund Range (Global).
	NAME-FNST-SOBJ	Subobject Group Name	A	15.0			A name given to a set of department subobjects
	NUMB-FNST-GROUP		N	5.0			This field will contain a number that is in numerical order for an organizational entity and object code. It is used to determine a break in subobject resolution.
	NUMB-FNST-SUB-OBJ		N	13.0			This field will contain a system-generated number of the subobject. It will be retrieved from codeset "CSYSNUM".
	CODE-CBUD-ACCESS1-MNEMONIC	Mnemonic	A	7.0		OTC, CESDEAN	Mnemonic associated with the budget contained in the NUMB-ACCT field.
	CODE-FNST-DTL-FO		A	1.0
	NUMB-DOCT-ACCT-SEQ		N	4.0			Sequences accounts within a document.
	NUMB-ACCT-DTL-SEQ		N	4.0			Sequences resolved details within an account.
	INDC-FNST-AUDIT		A	1.0		Y, N	Indicates that a detail record is an audit record.
I	DATE-RECD-LAST-UPDATE-TRANS		D	8.0	m/d/yy or m/d/yyyy		Date the record was last updated
	NUMB-FNST-DTL	Detail #	N	15.0			This field will contain a system generated number that will be unique for every detail.

Other Tables in the IBIS Financial Database

IBIS_CODESETS Table

• Contains Central Object Codes (CODE_VALUE field)
• Contains Central Object Description
• Contains Category Indicator (see below)

Category	General Fund Category Indicator	Restricted Fund Category Indicator
Income	000B	000B
Salaries	010B	010B
Wages	020B	020B
Department Allotment	030B	030B
Equipment	030B	070B
Capital	030B	080B
Overhead	030B	090B

IBIS_HEADER Table

• Contains "Demographic" data about an account such as: Award Amount, Begin, and End Dates.
• Contains Predominantly Restricted Accounts.

SUBOBJECT #1 Table (with all types of Account/Global subobject details)

• Contains Account Subobject (Acct-Sub, SACT) details.
• Contains Agency Subobject (SAGC) details.
• Contains Fund Type Subobject (SFTY) details.
• Contains Budget Fund Range Subobject (SBUD) details.
• Contains same fields as IBIS_DETAIL #1 Table

SUBOBJECT #2 Table (with cost center subobject details)

• Contains Cost Center Subobject details
• Contains same fields as IBIS_DETAIL #2 Table

Operators

An operator tells a query to perform an operation. For example, the + operator tells a query to perform an arithmetic operation (addition). Query tools allow you to use arithmetic, comparison, and logical operators as described below in the tables below.

Note: Arithmetic operators may vary, depending upon your data source, but most data sources support the standard arithmetic operators. Some data sources support additional operators not listed in the tables. To find out which operators your data source supports, see the documentation that comes with the driver your data source uses or contact the driver vendor.

Arithmetic Operators

These operators are used to perform numeric calculations.

Operator	Meaning
*	Multiply two numbers
+	Add two numbers
-	Subtract one number from another
/	Divide one number by another

Example: The following expression uses the + operator to calculate the sum of Permanent Budget and Temporary Budget. IPDB + ITBD

Comparison Operators

These operators are used to compare two values.

Operator	Meaning
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
=	Equal
<>	Not equal to

Example: The following expression uses the <= operator to determine Object Codes in the Income category. CODE-COBJ <= 0099

Logical Operators

These operators are used with expressions that evaluate to true or false.

Operator	Meaning
AND	This criterion and another criterion must be true for the records in the result set. One criterion as well as another criterion must be true.
OR	This criterion or another criterion must be true for the records in the result set. Either one criterion or another criterion may be true.
NOT	This criterion must not be true for the records in the result set.

Example: The following example uses the AND operator to combine the expressions CODE-FNST-DTL-TYPE = 'SRFC' and CODE-COBJ = '0336'. The entire expression evaluates to true if the CODE-FNST-DTL-TYPE value equals "SRFC" and the CODE-COBJ is "0336". CODE-FNST-DTL-TYPE = 'SRFC' AND CODE-COBJ = '0336'

Other Operators

The following table lists other operators you can use.

Operator	Meaning	Example
BETWEEN	To determine if a value falls within a certain range	BETWEEN 7/1/97 AND 7/31/97
IN	To determine if a value is equal to any of several values in a list	IN ('IPBD', 'ITBD', 'PPBD', 'PTBD', 'RPBD', 'RTBD')
IS	With the keyword NULL to determine if a value is null (has no value) or not null (has a value)	IS NULL, IS NOT NULL
LIKE	To compare two values using a wildcard character of a specific length (?)	LIKE '??????????10010'
LIKE	To compare two values using a wildcard character of variable length (*)	LIKE 'Sm*'

Joins/Relationships

JOIN allows the user to get data from more than one table as long as there is at least one field that is common between two tables at a time. If you JOIN 2 tables, there must be at least one field that occurs in both of the tables. If you JOIN 3 tables, then Table 1 and Table 2 must have at least one field in common and Table 2 and Table 3 (or Table 1 and Table 3) must have at least one field in common. The field that is shared by Table 1 and Table 2 does NOT have to be the same field that is shared by Table 2 and Table 3 (or Table 1 and Table 3). Example 2 below demonstrates this last scenario.

Example 1

IBIS_DETAIL #1 Table and IBIS_HEADER Table can be joined because:

• IBIS_DETAIL #1 Table has a field NUMB-ACCT 0201544 UP508A0
• IBIS_HEADER Table shares that field (NUMB-ACCT 0201544 UP508A0) with IBIS_DETAIL #1 Table

Example 2

IBIS_HEADER Table, IBIS_DETAIL #1 Table and IBIS_CODESETS Table can all be joined because:

• IBIS_HEADER Table and IBIS_DETAIL #1 Table share the NUMB-ACCT field
• IBIS_DETAIL #1 Table and IBIS_CODESETS Table share the CODE-COBJ/CODE-VALUE (Object Code) field

Tips for Using Microsoft Access to Query the IBIS Financial Database

If you have some tips that would be helpful to others and which you would like to have published at this Web site, please send your suggestions to: njb121@psu.edu. Also, the IBIS Financial Database Listserv is available for financial database users. For information on subscribing, visit the IBIS Financial Database Listserv page.

Operational Tips

• For each query, set the query property ODBC Timeout to 0 (zero)
• To optimize your database file performance, compact and repair your database file on a regular basis. This can be done while in the database file. Use the tools menu item, select Database Utilities, and select Compact and Repair.
• The IBISFin database is scheduled to update nightly. A total refresh is typically scheduled for the second Friday of the month and incremental updates occur the rest of the month.
• Data Warehouse is taken off-line at 5 p.m. each day in preparation for nightly maintenance. The server is down only momentarily and is available again within a minute.
• Use Fields which are indexes in every query. Searching on an indexed field is much faster. Fields which are indexes are:
  • IBIS_DETAIL#1 and IBIS_DETAIL#2 Tables:
    • CODE-BUDG-ADMIN-AREA
    • DEPT-MNEMONIC
    • NUMB-ACCT
    • DATE-FNST-FISCAL-YR
    • CODE-CCTR
    • NUMB-FNST-DTL
  • HEADER Table
    • NUMB-ACCT
• When using the In statement or operand, make sure the text criteria is enclosed in either single or double quotes with a comma between multiple criteria. For example, In('IPBD','ITBD').

Value Definitions

CODE-UNIV-REC-TYPE Values
IPBD	Permanent Budget
PPBD	Pending Permanent Budget
RPBD	Resolved Permanent Budget
ITBD	Temporary Budget
PTBD	Pending Temporary Budget
RTBD	Resolved Temporary Budget
IACT	Actual
PACT	Pending Actual
RACT	Resolved Actual
IENC	Ordinary Encumbrance
PENC	Pending Ordinary Encumbrance
RENC	Resolved Ordinary Encumbrance
IENP	Planned Encumbrance
PENP	Pending Planned Encumbrance
RENP	Resolved Planned Encumbrance
IENS	Standing Encumbrance
PENS	Pending Standing Encumbrance
RENS	Resolved Standing Encumbrance

Notes:

• The record types that start with I have been posted BOTH to the university, and they have been resolved through CRFN.
• The record types that start with either P or R have ONLY been posted to the university, and they have NOT been resolved through CRFN.

Criteria Tips

• Income Object Codes are <=0099 and Expense Object Codes are >0099.
• Remember to run separate queries for Income information and for Expense information.
• Don't combine Budget amounts with Actual or Encumbrance amounts.
  • Use: Not In ('IPBD','ITBD','PPBD','PTBD','RPBD','RTBD') as criteria for the CODE-UNIV-REC-TYPE field when you want Actual and Encumbrance data.
  • Use: In ('IPBD','ITBD','PPBD','PTBD','RPBD','RTBD') as criteria for the CODE-UNIV-REC-TYPE field when you want Permanent and Temporary Budget data.
• Since all text data is in uppercase letters, remember to use upper case when querying. If you run a query with the criteria in lower case letters, you won't get any results.
• Remember to include the fiscal year of interest (ex: ³19971998´) in the criteria for the DATE-FNST-FISCAL-YR field.
• The account format is: 0803311 UP10010

Notes:

• There must be a space between the budget and location code if the budget is only 7 characters long.
• There is NOT a space between the location code and the fund.
• The location code is in CAPS

Logic Tips

AND	X as well as Y	Both conditions must be true to get back results
OR	Either X or Y	One or the other condition must be true to get back results

Joining Tables

Table 1	Table 2	Field from Table 1	Field from Table 2
IBIS_DETAIL (1 or 2)	HEADER	NUMB-ACCT	NUMB-ACCT
IBIS_DETAIL (1 or 2)	HEADER	CODE-BUDG-ADMIN-AREA	CODE-CBUD-ACCT-ADMIN-AREA
IBIS_DETAIL (1 or 2)	IBIS_CODESETS	CODE-COBJ	COBJ-VALUE