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:

• Banner
• 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 within the Division of Information Technology (DoIT).

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” under “Application Systems” in the right side menu
   • You may need to click on the words “Application Systems” to expand the list to see the Data Access Request application
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.

To connect to the ODS with Microsoft Access or Excel on Windows, you’ll need to create an ODBC connection to the ODS database.  Instructions for that are available in the document linked below.

How to make an ODBC connection

The ODS Data Dictionary is available in the ODS 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 in the ODS, 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 is oriented more to the field data. It includes the schema and table/view name, a description of the 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;
• Join of Data Objects and Fields
  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;

If you’re looking for the legacy ODS Data Dictionary, it was based on a technology that was no longer supportable and retired in March, 2022.

ODS – Commonly Used Views

ODS – Frequently Asked Questions

ODS – Sample Queries

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 numerous query tools in existence, open source/free tools and commercially licensed, fee-based products. Below is a list of SQL tools that we have some familiarity with.

Free / Open Source

• SQL Developer – Oracle

Commercial

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

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.

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.

Login To Aries Web – Login

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.

For questions relating to the ODS, please contact the Data Warehouse Team at is_dl_team_-_ods_and_bi@mail.colostate.edu.