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
|
||
|
Cost Centers:
|
||
|
Cost Center Subobjects to Project Y:
|
||
|
Account Subobjects:
|
||
| 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 |
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
- IBIS_DETAIL#1 and IBIS_DETAIL#2 Tables:
- 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 |