ODS Main Page

What is the Operational Data Store (ODS)?

The Operational Data Store is a repository of data from several sources of Colorado State University data. These data sources include the following systems:

  • BanneBanner
  • CSU Foundation
  • Famis – Facilities
  • General Directory
  • Human Resources
  • Kuali – Kuali Financial Systems (KFS)
  • Sponsored Research – Kuali Coeus (KC)

The data from these systems are categorized into ODS roles. These roles are:

  • Accounts Receivable (ARIES_AR_DEPT)
  • CSU Foundation (CSU_FOUNDATION_REPORTING)
  • Financial Data (FINANCIAL_REPORTING)
  • General Directory (WEID_QUERY)
  • Human Resources (HR_DEPT_LEVEL_ACCESS)
  • Research Data (RESEARCH_DATA_DEPT)
  • Student Data (ARIES_STUDENT_DEPT)
  • Facilities Data (FACILITIES REPORTING)

The data in the ODS is maintained by the ODS team in the Information Systems department.

ODS Data Dictionary Documentation

How do I get access to data in the ODS?

To gain access to one or more of the ODS roles:

  1. Decide which Role(s) you need. Some ways to do this include:
    • asking your supervisor or co-workers
    • using the “Roles and Views” search in the “ODS Data Dictionary Documentation” section on this page
    • using the “Most Used Views” section on this page
  2. Go to the AAR portal and select “Data Access Request” in the right side menu
  3. Select “Requester” from the “Login As” menu and submit your request to the Role(s) you need
  4. Your supervisor will get an email that they have a request to act upon using the same Data Access Request tool.
    • If they do not approve or deny your request within 30 days, your request will be automatically denied.
  5. If your request is approved by your supervisor, the data steward appointed for your department will use the same tool to make final approval on your request.
    • If the data steward does not approve or deny your request within 30 days of the supervisor approval date, your request will be automatically denied.
  6. If approved by the data steward, you will be granted access to the data you requested
  7. You will be notified via email as each approval or denial is made through the process.
  8. You can check the status of your request at any time by logging back into the Data Access Request application and viewing the “Previous Requests” section of the page. As approvals or denials are made, the dates will be updated in that section.

Most Used Views

To make it easier to find the data you need, we have compiled a list of the top views used by other CSU employees for each role.

Accounts Receivable (ARIES_AR_DEPT)

View Name Description
CSUT_AR
_DEPOSITS
A/R Deposits
CSUT_AR_HOLD View of Accounts Receivable Holds (FA, FB, FC, FD, FE, FG, FL, FP, FR, FS, FT, FW, TR)
CSUT_AR_TRN A/R Transaction ODS View

Financial Data (FINANCIAL_REPORTING)

View Name Description
CSUF_ACCOUNT
_MONTHLY_SNAP
Account month end snapshot
CSUF_CA
_ACCOUNT
Primary KFS Account Table
CSUF_CA_ICR
_AUTO_ENTR
KFS Indirect Cost Recovery table
CSUF_DETAIL Transaction level detail for each GL entry. It includes account, fund, sub-fund, object code, and other information about the entry
CSUF_FIN
_DOC_HDR
KFS/RICE table contains document workflow and approvers
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
CSUF_GL_ENTRY KFS General Ledger table
CSUF_GL
_PENDING_ENTRY
KFS General Ledger Pending Entries table
CSUF_KRIM
_PRNCPL
KFS/Rice User (Principal) Id table
CSUF_OBJECT
_MONTHLY_SNAP
Object month end snapshot
CSUF_PUR_PO Primary KFS Purchase Order table
CSUF_SH
_UNIV_DATE
University Date Table
CSUF_SUMMARY
_MONTH_ADJ

 

General Directory (WEID_QUERY)

View Name Description
CSUG_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.
CSUG_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.
CSUG_DIRECTORY
_EID
Directory information for all CSU Faculty, Staff, Students, and Associates with an active eID. Only Primary eID account information for each person is included.

 

Human Resources (HR_DEPT_LEVEL_ACCESS)

View Name Description
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
CSUH_CURRENT
_EMPLOYEE_PRIMARY
All active primary employee assignments including non-protected person-related information, current record only
CSUH_CURRENT
_EMPLOYEE_PRIV_EV
Private or protected employee ethnicity/race and veteran status data. Further information provided at http://www.hrs.colostate.edu/current-employees/race-ethnicity-veteran-values.html
CSUH_CURRENT
_EMPLOYEE_PRIV_GB
Private or protected employee gender, date of birth, and date of death data
CSUH_CUR_FY
_EXPHIST
Current Fiscal Year Employee table
CSUH_EDUCATION Education information as provided by the employee (includes the same population as CSUH_Current_Employee_Hist)
CSUH_EMPLOYEE
_ALL
CSUH_JOB

Research Data (RESEARCH_DATA_DEPT)

View Name Description
CSUV_RPS
_COMMENTS
Research Project Status (RPS) comments pertaining to 53 fund research accounts. May contain multiple records per account. Data source: Kuali Coeus.
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.
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.
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.
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.
CSUV_RPS
_FUNDING_PRPSL
Research Project Status (RPS) institutional proposals associated with an award. May contain multiple records per account. Data source: Kuali Coeus.
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.
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.
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)

View Name Description
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.
CSUG_GP_DEMO For each person on Aries a record is created that contains a significant amount fo demographic and biographic information. GP is refreshed daily.
CSUS_AWARDS
_CONFERRED
Awards conferred. Deceased students are excluded from the view.
CSUS_COURSE
_SCHEDULE_CUR
The list of classes that should be offered for the current term.
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.
CSUS_SECTION
_INFO_AH
Historical student enrollment, with grades. Section information for Academic History. Deceased students are excluded from the view.
CSUS_SECTION
_INFO_CUR
Current student enrollment in courses. Section information for current term. Deceased students are excluded from the view.
CSUS_SECTION
_INFO_FAL/SPR/SMR
Student enrollment in courses. Section information for fall/spring/summer term. Deceased students are excluded from the view.
CSUS_STUDENT
_FEES
Student Fee status for enrolled students for the current Fall, Spring and Summer terms. Deceased students are excluded.
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.
CSUS_STUDIO
_ABROAD
Active Non-Resident student information
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.
CSUS_TERM
_INFO_AH_RECENT
Contains data for all academic history included in the ODS. Deceased students are excluded from the view.
CSUS_TERM
_INFO_CUR
Contains data for the current term. Deceased students are excluded from the view.
CSUS_TERM
_INFO_FAL/SPR/SMR
Contains data for the fall/spring/summer term. Deceased students are excluded from the view.

Sample Queries

Please read FERPA Policies prior to accessing Student Data

Graduating Students - Spring (Note: For Summer change spr to smr, and for Fall change spr to fal)

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’)

Get Information 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

Get a student's schedule for the current term.

SELECT a.ID, a.NAME, a.ACADEMIC_PERIOD,

a.SUBJECT, a.COURSE_NUMBER,

a.COURSE_SECTION_NUMBER, a.COURSE_REFERENCE_NUMBER

FROM CSUBAN.CSUS_SECTION_INFO_CUR a

order by a.SUBJECT ,a.COURSE_NUMBER ,a.COURSE_SECTION_NUMBER

Get all employees within Student Affairs (by mail code, ignoring students and temporary staff)

SELECT LAST_NAME, FIRST_NAME, MIDDLE_NAME,

CSU_ID,

CASE WHEN PROPER_NAME_ADDRESS IS NULL THEN EMAIL_ADDRESS ELSE PROPER_NAME_ADDRESS END EMAIL,

EMPLOYEE_TITLE,

EMPLOYEE_DEPARTMENT,

EMPLOYEE_DEPARTMENT_NUMBER,

EMPLOYEE_TYPE

FROM CSUBAN.CSUG_DIRECTORY_ALL

WHERE ((EMPLOYEE_DEPARTMENT_NUMBER BETWEEN 8001 AND 8105) OR

(EMPLOYEE_DEPARTMENT_NUMBER = 1032))

AND (ACCOUNT_TYPE = ‘P’) AND (LIVE_LAG_START_DATE IS NULL)

AND (DEAD_LAG_START_DATE IS NULL)

AND (ACTIVE_FLAG_EIDDATA = ‘Y’)

AND (EMPLOYEE_TYPE NOT LIKE ‘%Student Hourly%’)

AND (EMPLOYEE_TYPE NOT LIKE ‘%Retiree%’)

AND (EMPLOYEE_TYPE NOT LIKE ‘%Temporary%’)

AND (EMAIL_ADDRESS IS NOT NULL)

AND (ACTIVE_FLAG_EMAIL <> ‘N’)

AND (ADMIN_ACCT_FLAG <> ‘Y’)

ORDER BY LAST_NAME, FIRST_NAME

Get Student Information by eName (enrollment eligible) - NOTE: Replace the :ENAME variable with student eName

SELECT CSU_ID, FIRST_NAME, LAST_NAME,

STUDENT_CLASSIFICATION, STUDENT_MAJOR,

CASE WHEN PROPER_NAME_ADDRESS IS NULL THEN EMAIL_ADDRESS ELSE PROPER_NAME_ADDRESS END EMAIL,

CASE WHEN PHONE IS NULL THEN EMPLOYEE_PHONE ELSE PHONE END PHONE,

STUDENT_ENROLLED_INDICATION

FROM CSUG_DIRECTORY_PRIVACY

WHERE ENAME = :ENAME

Example Student Query

Below are links to help you with a sample query using an ODS reporting view CSUS_CRN_INFO (Course Reference Number).

If you are already using Excel or Access to query ODS, then you will not need to setup an ODBC connection.
If not, then you will need to follow the instructions for Installing ODBC so that you can use Microsoft Excel or Access to query the ODS data warehouse.

MS Query is a tool that comes with Excel and the Banner_Course_Query_Instructions will guide you through this tool.

The link for Demo Query will download CRN_DEMO.zip.  Unzip then double click CRN_DEMO.dqy (will launch MS Query tool).

Excel spreadsheet Banner_Course_Reference is an example spreadsheet that contains the exported results (data) from the query.

Referenced Links:
Instructions for Installing ODBC
Banner_Course_Query_Instructions
Demo Query: CSUS_CRN_INFO (Course Reference Number)
Banner_Course_Reference (exported results)

Frequently Asked Questions

What is FERPA?

  • Usage of student data is restricted under FERPA guidelines. These guidelines require a need to know for educational purposes and information about students cannot be shared without a signed statement from the student.
  • Please read FERPA Policies prior to accessing Student Data

How should I get started?

  • Usage of student data is restricted under FERPA guidelines. These guidelines require a need to know for educational purposes and information about students cannot be shared without a signed statement from the student.
  • Familiarize yourself with the Operational Data Store (ODS) and the Data Dictionary in order to determine the types of data you will need access to.
  • The most commonly-used views have been organized into roles. You can request access to these through AAR, then clicking on the “Data Access Request” link. If you need access to additional views, consult with your Data Steward in order to determine which views should be requested and then please complete the form in order to be granted access to data that you need.
  • Once access has been granted, you next need to install software/configure your computer in order to connect to ODS. There are many tools that you can use to access ODS. Please see “What software will I need to connect to/query ODS” below for further information.

How do I get access to data on campus?

Please click here for information on how to be granted access to restricted data at CSU. Also don’t forgot to read the section on “How should I get started?”

What software will I need to connect to/query ODS?

How do I tell if a student is enrolled for the current semester?

QUERY: Select * from CSUS_TERM_INFO_CUR

What are the policies on storing data?

What is ARVID and how does this differ from Finance subject areas?

ARVID was created for Accounting Reporting views. It contains data from Accounts (COA), Assets, GL, Object Codes, Purchasing, Vendors and KFS Document Routing. BFS created the purchasing views to be able to connect Requisitions, POs, Receiving, and Payment Requests which are all related KFS documents. This information can also be found in the Finance Subject Areas views, but the report writer will need to connect the information (whereas much of this is done for you in ARVID).

How do I access data in the ODS from my PC?

  • Refer to the “How to make an ODBC connection” link under the “ODS Data Dictionary Documentation” section or click this link
  • Refer to the “How do I get access to data in the ODS?” section above to be granted access to the data.

I am running a query and I get an error that the "table or view does not exist". How do I get past this?

  • Confirm you have correctly typed the name of the table or view.
  • Confirm you have access to that table or view. You can do this by checking which views are available in the roles you have been granted. If the view is in a role you do not currently have, you can request access to the role that it goes with.
  • Refer to the section ‘ODS Data Dictionary Documentation’ to see the various reporting roles and views.

How can I request access to a table or view?

Refer to the “How do I get access to data in the ODS?” section above.

How can I stay up-to-date on what is happening with the ODS?

What is PIDM and PERSON_UID?

The Banner system uses the field PIDM as the record key field. The ODS uses both PIDM and PERSON_UID as the record key field, and they are interchangeable. When running a query, specifying the PIDM/PERSON_UID value will often improve speed/performance. If you are using a query that uses 2 or more views, they should be joined together with the PIDM/PERSON_UID. INT_REF_ID_ARIES is another synonym for PIDM and PERSON_UID.

Why are there so many views in the Data Dictionary that I don't seem to have access to?

Access to ODS data is not automatic. You need to request it. Please refer to section on How do I get Access to Data in ODS. This will explain the steps involved with the data access approval. The dictionary will provide documentation on all reporting views, including ones that you may not be able to query.

Why is the Data Dictionary missing so many descriptions of column/field names and even view descriptions?

Information Systems publishes the Data Dictionary to assist campus users who need to write reports. However, we are not the functional area experts who are most familiar with the Student, HR, and Financial data. We will work closely with these functional experts to define the missing information.

What is a view or reporting view in ODS?

Many report writers are familiar with database tables that store the university information. The ODS was developed to provide access to this information in the form of views. The views will join one or more tables together using some rules (like exclude a person with deceased_ind = ‘Y’). For the most part, you can consider a view to be a table (although it may provide data from one or more tables).

How Do I find out Who My Data Steward is?

How do I know if a student has requested their information to be confidential?

In the csug_gp_demo view, there is a field titled confidentiality_ind. If that column is ‘Y’, the student has requested that all of their information, including what is considered by CSU to be directory information, to be confidential. This means you cannot release or publish any information about that student and you cannot contact that student without the student presenting a photo id in person to you. This is similar to seeing the red confidential flag on the top of the ARIESweb screen when you look up the student. Please respect these students choice not to have their information released. Release of information for a student with a confidentiality_ind of ‘Y’ is a violation of FERPA regulations.

Hints for using the ODS

  • The fourth letter (i.e. immediately following “CSU”) of the View 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

Developer Tools

Developer Tools to Access ODS Data Warehouse

Although Information Systems supports the central ODS platform, many campus departments support their own internal IT platforms that utilize data from the ODS.

There are two predominant types of access to the ODS via reporting, query and ETL tools:

  1. Microsoft ODBC client connection
  2. Oracle sqlnet client connection (uses tnsnames)

Although there are numerous query tools in existence, they generally fall into two categories of commercial products that require licensing fees or open source/free tools. We will include several of the tools that we are aware of at our institution.

Commercial

  • Visual Studio (.net) that also has an Express (free) version (requires ODBC connection)
  • PL/SQL developer – AllRound Automation
  • TOAD (Tool for Oracle Application Developers) – Dell software
  • SQL Plus Command Line client – Oracle Client software

Free / Open Source

  • Oracle – Oracle SQL Developer Software

Student Data Reports Already Available

Please read FERPA Policies prior to accessing Student Data

Aries Web contains many Student Data reports that may meet your needs:
Login To Aries Web – Login

ARIESweb access requires approval from your supervisor and the Division of Enrollment and Access. If you do not already have full ARIESweb access (the link you wish to access is grayed out or does not appear), you can request it after you login by using the ‘start here with the online approval process’ link at the top of the screen.

Helpful pre-written reports can be found in ARIESweb using the Department-Specific Data link under the Staff Tools section. This link includes reports that can be run based on your College or Department information using quick drop down lists.

Another tool available is called RamSelect and it is in the General Tools section. RamSelect uses the ODS tables and views, but does not require you to know a Query language as it will do the table/view joins for you. Available tables and views are limited, but RamSelect does provide access to some of the more frequently requested reports and the flexibility to select certain data you want on the report.

User Group Information

Sept 21, 2015 Meeting (Financial Presentation – audio file, 1 hr 12 mins long)

Sept 21, 2015 Meeting (Financial Presentation – .pdf Document)

Nov 11,  2015 Meeting (Student Presentation – audio file, 58 mins long)

Nov 11, 2015 Meeting (Student Presentation – .pdf Document)

Mar 22, 2016 Meeting – (Video of Research Data Presentation): Because YouTube is publicly accessible, the content has been made private. Please email John Walker at j.walker@colostate.edu and he can add your email to the private channel to give you access to the videos. You will receive an email invite to view the video(s). Thank you.

Mar 22, 2016 Meeting (Research Presentation – audio file 1 hour and 17 mins long)

Mar 22, 2016 Meeting – (Research Views PDF Document)