Data Warehouse (ODS) Data Dictionary and Common Datasets

General

This article is focused on helping you find the data your looking for in the Data Warehouse. It covers four topics:

Data Dictionary - a summary of the datasets available in the Data Warehouse
Commonly Used Datasets
Sample Queries
Hint for identifying CSU Student datasets

Data Dictionary

The Data Dictionary is available in the Data Warehouse in a pair of tables. It’s also available in WebFocus via a report, 'ODS Data Dictionary', in the Campus Reports folder.

The table CSU_BI_META.CSU_REPORTING_DATA_OBJECTS provides a list of all tables and views available along a description of the table, a comma delimited list of the fields, and a list of security roles that have access to the table or view. If you’re looking for access to one or more objects, knowing security roles that relate to the object(s) of interest is helpful.

The second table, CSU_BI_META.CSU_REPORTING_DATA_FIELDS, is oriented more to the field data. It includes the schema and table/view name, a description of the data field, the data type and length, what position the column resides within the table, and the source system (Banner, KFS, HR, etc.)

Here’s a couple of queries to get you started with the new Data Dictionary.

Data Objects: Tables and Views
- select owner, object_name, object_type, source_system, table_description, access_roles, field_list
  from csu_bi_meta.csu_reporting_data_objects
Data Fields: Fields of data objects
- select owner, table_name, column_name, source_system, data_type, data_length, column_seq_num, column_description
  from csu_bi_meta.csu_reporting_data_fields;
If you need both the list of datasets and the field level details together, here's a query that joins the two together.
- select data_objects.*, ‘/\/\/\/\/\/\’ Divider, data_fields.*
  from csu_bi_meta.csu_reporting_data_objects data_objects
  join csu_bi_meta.csu_reporting_data_fields data_fields on data_objects.owner = data_fields.owner and data_objects.object_name = data_fields.table_name;

Commonly Used Datasets

Accounts Receivable (ARIES_AR_DEPT)

CSUBAN.CSUT_AR_DEPOSITS
- A/R Deposits
CSUBAN.CSUT_AR_HOLD
- View of Accounts Receivable Holds (FA, FB, FC, FD, FE, FG, FL, FP, FR, FS, FT, FW, TR)
CSUBAN.CSUT_AR_TRN
- A/R Transaction ODS View

Financial Data (FINANCIAL_REPORTING)

CSUKFS.CSUF_ACCOUNT_MONTHLY_SNAP
- Account month end snapshot
CSUKFS.CSUF_CA_ACCOUNT
- Primary KFS Account Table
CSUKFS.CSUF_CA_ICR_AUTO_ENTR
- KFS Indirect Cost Recovery table
CSUKFS.CSUF_DETAIL
- Transaction level detail for each GL entry. It includes account, fund, sub-fund, object code, and other information about the entry
CSUKFS.CSUF_FIN_DOC_HDR
- KFS/RICE table contains document workflow and approvers
CSUKFS.CSUF_GL_BALANCE
- Detailed account balance information for an account. Budget, fiscal year beginning balance and actual balances by month are displayed by account, subaccount, object and subobject
CSUKFS.CSUF_GL_ENTRY
- KFS General Ledger table
CSUKFS.CSUF_GL_PENDING_ENTRY
- KFS General Ledger Pending Entries table
CSUKFS.CSUF_KRIM_PRNCPL
- KFS/Rice User (Principal) Id table
CSUKFS.CSUF_OBJECT_MONTHLY_SNAP
- Object month end snapshot
CSUKFS.CSUF_PUR_PO
- Primary KFS Purchase Order table
CSUKFS.CSUF_SH_UNIV_DATE
- University Date Table
CSUKFS.CSUF_SUMMARY_MONTH_ADJ

General Directory (WEID_QUERY)

CSUBAN.MIDP_DIRECTORY_PRIVACY
- Directory information for all CSU Faculty, Staff, Students, and Associates. Note that PRIVACY_* flags are present and implemented. All requested privacy / FERPA rules have been implemented and that data has been nulled out.
CSUBAN.MIDP_DIRECTORY_ALL
- Directory information for all CSU Faculty, Staff, Students, and Associates. Note that PRIVACY_* flags are present. When using this information you must follow FERPA regulations.

Human Resources (HR_DEPT_LEVEL_ACCESS)

CSUHR.CSUH_CURRENT_EMPLOYEE_HIST
- Active employee assignments and employee assignments terminated within the past 6mos including non-protected person-related information, current and historical records
CSUHR.CSUH_CURRENT_EMPLOYEE_PRIMARY
- All active primary employee assignments including non-protected person-related information, current record only
CSUHR.CSUH_CUR_FY_EXPHIST
- Current Fiscal Year Employee table
CSUHR.CSUH_EDUCATION
- Education information as provided by the employee (includes the same population as CSUH_Current_Employee_Hist)
CSUHR.CSUH_EMPLOYEE_ALL
CSUHR.CSUH_JOB

Research Data (RESEARCH_DATA_DEPT)

CSUSR.CSUV_RPS_COMMENTS
- Research Project Status (RPS) comments pertaining to 53 fund research accounts. May contain multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_CONTACTS
- Research Project Status (RPS) contacts for an award (e.g., PI, Co-PI, fiscal officer, account manager, etc.). May contain multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_COST_SHARE_ACCOUNT
- Research Project Status (RPS) cost share accounts related to 53 fund research accounts. May contain multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_COST_SHARE_AMOUNT
- Research Project Status (RPS) cost share amounts related to 53 fund research accounts. May contain multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_DEMOGRAPHICS
- Research Project Status (RPS) demographic data pertaining to 53 fund research accounts. Where award status = active, fund advance, pending close, or close. One record per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_FUNDING_PRPSL
- Research Project Status (RPS) institutional proposals associated with an award. May contain multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_RELATED_NUMBERS
- Research Project Status (RPS) additional document numbers and accounts related to a 53 fund research account. Multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_REPORTS
- Research Project Status (RPS) reports and due dates related to 53 fund research accounts. Reports with variable due dates (e.g., monthly, quarterly) will not have a due date displayed. May contain multiple records per account. Data source: Kuali Coeus.
CSUSR.CSUV_RPS_TERMS
- Research Project Status (RPS) sponsor terms and conditions related to 53 fund research accounts. May contain multiple records per account. Data source: Kuali Coeus.

Student Data (ARIES_STUDENT_DEPT)

CSUBAN.CSUG_GP_DATA_CODE_CAMPUS
- This is the view that contains all the extra info on a student, such as first-generation student, orientation they attended, and much more.
CSUBAN.CSUG_GP_DEMO
- For each person on Aries a record is created that contains a significant amount of demographic and biographic information. GP is refreshed daily.
CSUBAN.CSUS_AWARDS_CONFERRED
- Awards conferred. Deceased students are excluded from the view.
CSUBAN.CSUS_COURSE_SCHEDULE_CUR
- The list of classes that should be offered for the current term.
CSUBAN.CSUS_CRN_INFO
- Contains detailed data on courses by academic period, part of term, CRN and course offering. Includes meeting times for each section or schedule offering.
CSUBAN.CSUS_SECTION_INFO_AH
- Historical student enrollment, with grades. Section information for Academic History. Deceased students are excluded from the view.
CSUBAN.CSUS_SECTION_INFO_CUR
- Current student enrollment in courses. Section information for current term. Deceased students are excluded from the view.
CSUBAN.CSUS_SECTION_INFO_FAL(SPR, SMR)
- Student enrollment in courses. Section information for fall/spring/summer term. Deceased students are excluded from the view.
CSUBAN.CSUS_STUDENT_FEES
- Student Fee status for enrolled students for the current Fall, Spring and Summer terms. Deceased students are excluded.
CSUBAN.CSUS_STUDENT_FEES_WITH_DATES
- Student Fee status for enrolled students for the current Fall, Spring and Summer terms with Part of Term detail. Deceased students are excluded.
CSUBAN.CSUS_STUDIO_ABROAD
- Active Non-Resident student information
CSUBAN.CSUS_TERM_INFO_AH
- Contains data for all academic history included in the ODS. Deceased students are excluded from the view. Academic History for most recent 7 years.
CSUBAN.CSUS_TERM_INFO_AH_RECENT
- Contains data for all academic history included in the ODS. Deceased students are excluded from the view.
CSUBAN.CSUS_TERM_INFO_CUR
- Contains data for the current term. Deceased students are excluded from the view.
CSUBAN.CSUS_TERM_INFO_FAL (SPR, SMR)
- Contains data for the fall/spring/summer term. Deceased students are excluded from the view.

Sample Queries

Query for registered students

select b.csu_id ,b.first_name ,b.last_name, a.STUDENT_CLASS, a.STUDENT_CLASS_DESC, a.PRIMARY_MAJOR, a.PRIMARY_MAJOR_DESC, b.email, b.telephone, a.CONTINUOUS_REG, a.CREDITS_CE, a.CREDITS_RI, a.CREDITS_NON_CSU, a.CREDITS_SI, a.CREDITS_OTHER, a.CREDITS_TOTAL

from csus_term_info_cur a

join csug_gp_demo b on a.PIDM = b.pidm

where b.confidentiality_ind <> ‘Y’

order by b.legal_name

Query for Graduating Students for the Spring semester. Use CSUS_COMMENCEMENT_FAL or CSUS_COMMENCEMENT_SMR for fall and summer terms.

select d.first_name,d.last_name,d.email, d.addr_1,d.addr_2,d.addr_3,d.city,d.state,d.nation,d.zip

from csuban.csus_commencement_spr c,

csuban.csug_gp_demo d

where c.person_uid = d.pidm and (d.confidentiality_ind <> 'Y')

Hints for Identifying CSU Student Datasets

Many student related datasets in the Data Warehouse start with a CSU prefix. The fourth letter (i.e. immediately following “CSU”) of the dataset name tells you which role that view belongs to.
- T is for Accounts Receivable
- F is for Finance
- R is for Financial Aid
- G is for General Student and Directory
- H is for Human Resources
- V is for Research Data
- S is for Student