Data Access Request (PDAR)

Private Data Access Request (PDAR) for ODS or BANNER data

Degree Audit Reports (DARS)

Data Governance

The CSU Data Warehouse (ODS) is a repository of data collected and updated from various Colorado State University systems. These data sources include the following systems: Ellucian Banner - Student Information System Kuali Financial Systems (KFS) General Directory Human Resources CSU Foundation FAMIS – Facilities Sponsored Research – Kuali Research (KC) Requesting Access to the Data Warehouse To request access to the Data Warehouse ODS, submit a request through the ODS Data Access Request application, https://dataaccessreq.is.colostate.edu/ . As part of your request, you'll need to select the data areas you're interested in accessing: Student, Finance, Directory, etc. When submitted, your request will follow a workflow for signatures that include your supervisor and departments Data Steward. You can monitor the status of your request through the same web app. Developer Tools There are numerous SQL 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 Oracle SQL Developer – https://www.oracle.com/database/sqldeveloper/ Visual Studio Code - https://code.visualstudio.com/ SQL Plus (Command Line SQL client) - https://www.oracle.com/database/technologies/instant-client.html Commercial PL/SQL developer – https://www.allroundautomations.com/products/pl-sql-developer/ TOAD for Oracle – https://www.quest.com/products/toad-for-oracle/ Alternative Resources for Data & Reports ARIESweb is a helpful resource for pre-written reports and includes reports that can be run based on your College or Department information. 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 ARIESweb The Data Warehouse (ODS) is maintained by the Data Warehouse Team within the Division of Information Technology (DoIT). For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu . For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu

General This article is focused on helping you find the data you're 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 as a pair of tables, one focused on data objects (tables and views) and the other of field level information. 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 available tables and views, along with a description of the table, a comma delimited list of the fields, and a list of security roles that grant access to the data object. 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 For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu

WebFOCUS License Information Licenses may be purchased in person at the RAMtech Office in the Lory Student Center OR you can download the Special Order form on the RAMTech website, then email to RAMtech@colostate.edu. RAMtech will require that you include your NetID, department number and account number to process the transaction. Please allow the administrator a couple of days following purchase to perform the WebFOCUS account activation. License Costs: Varies by fiscal year. Fiscal year 2025 is $211. Who needs a license? All campus personnel who expect to create and/or run reports within WebFOCUS will need to purchase a license. Each person is required to have their own license. Who does not need a license? If a license holder creates and/or runs reports within WebFOCUS, they can choose to email reports to department heads or other personnel who do not have licenses. People without licenses will be able to view most emailed reports. As people create more advanced reports that offer drill down capabilities (ie. show summary high level and then drill down to details), the drill down report details will not be available to emailed non-license holders. A license should be purchased annually and is valid until the end of the fiscal year WebFOCUS Data Access Requirements Many of the WebFOCUS reports are built using data from the ODS which relies on ODS security. If you are new to WebFOCUS you’ll need to request access to the data area(s) you want to report on. For example, Finance, Student, and HR. You can do this through the Data Access Request form. For HR and Foundation requests, you’ll be prompted to enter the department code(s) for the data you’re requesting access to. i.e. Your access to HR and Foundation data will be limited to only the departments you list. Once your form is submitted you can check its status on the form Progress page. Can I Access WebFOCUS now Fingers crossed, you should be all set. You can find the link to WebFOCUS on the AAR page under Application Systems. If you do not have access to the system or unable to see data in your reports, please check: That you have purchased your license from RamTech That you have completed a WebFOCUS training class That you have submitted an ODS Data Access Request form (aar.colostate.edu) Special Permissions Requests If you need access to a specific ODS dataset that’s not included with one of the default roles above, you’ll need to fill out a PDAR request per the instructions below Private Data Access Request. Select ODSPROD for the ‘Database’ Enter the details and justification in the ‘Justification’ box. Check the ‘Statement of Use and Understanding’ box (after reading) Check the applicable checkboxes for the type of data you’re requesting access to. Click ‘Next’ Sign the request The request will be routed through the workflow for signatures, starting with your supervisor. Once your form is submitted you can check its status on your Pending Forms page. Getting Started Using Web Focus WebFOCUS (software license required) LOG IN HERE. Directory: Subscribe/UnSubscribe to the WebFocusReports Listserve for Communications relating to WebFOCUS activities Contacts for Administrative Units and Colleges WebFocus Team Training and Starter Info If you're brand new to the platform, these Training Videos are a great place to start. When you're ready to go off on you're own, the Data Quickstart article can help guide you to the data you're looking for. Beyond that there is a ton of content here and elsewhere on the web: In House Articles Reporting Themes (style sheets) Share a report Cancelling a WebFOCUS Report How to Save AS Scheduling Reports Repeating Column Values Vendor Training Materials (free) YouTube Channel – Vendor provided, version specific WebFOCUS training videos Past Knowledge Share Webinars Self Service Video Series – Versions 8.2.01 – 8.2.04 New Features for 8.2.06 For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu

In InfoAssist, the report designer tool for WebFOCUS, you have the ability to select a ‘Theme’ when creating or editing a report. Several CSU Themes have been created for use with Finance and Registrar’s reports. Themes are sometimes referred to as templates or WebFOCUS Style sheets. To apply a Theme to a report go to the Home menu and select the Theme icon. You’ll be presented with a short list of available Templates in the public repository. You’ll also be able to browse through the reporting domains and select a style sheet that’s in a Domain folder. Simply select the style sheet you’d like to use and click Ok. When you run the report you’ll see the effect of the style sheet/theme. While you can’t make changes to the style sheet, settings within the report will override settings in the style sheet. Style sheets have been created for Portrait and Landscape when necessary to place images properly on the page. Finance Report Style Sheet Details The Finance style sheet contains settings for the following: Image of CSU Ram logo is set in the top left corner of the report Page Heading: Lines 1 and 2 are font size 12 and Bold. Lines 3, 4, 5, and 6 are font size 10 Page Footer Formatting along the left margin for two rows with two columns for the label and the data that are intended to contain the following information. Line 1 “Run Date:” Line 2 “As Of:” Formatting along the right margin for two row with two columns that are intended to contain the following information. Line 1 “Page: X of Y” Line 2 “Source: WebFOCUS” One of the keys to the resulting header/footer format is that the report has field separator characters “” between the label and the text in the page header and page footers. The Finance template also requires the page header to be indented for the CSU Logo. In order to achieve the indent you’ll need to have another field separator character in front of the text. Here’s an example of a 5 line Page HEADING would look like in the text editor, including the double quotes. ” Computer Hardware/Software (6225) Trigger Report” ” RECHARG Accounts – Current FY” ” “ ” Chart: “ ” Fiscal Year: &&CUR_FY “ Here’s an example of the standard page footing for finance reports. “Run Date: &DATE &DATEHHIA Page: &CUR_FP Source: WebFOCUS” These changes can only be done by with “Edit with Text Editor” option in WebFocus. For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu

The menu of WebFOCUS videos below link to a Microsoft Teams team for WebFOCUS. You'll be prompted to login using your ColoState.edu credentials. Note: When logging in to Microsoft 365 (M365), your username is your NetID@colostate.edu. If you are unable to access the videos after logging in, please open a Freshservice ticket with the Data Warehouse team ( https://csusystem.freshservice.com/support/home ). Introduction - Logging in Report Formatting Report Outputs, Sharing, and Scheduling Navigation part 1 Navigation part 2 Run and Edit a Report Create a Report Virtual Fields Filters Test and Run a Report Headers and Footers Subtotals and Line Breaks For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu

Open a new browser window to WebFOCUS In the upper right corner click on your logon name -> then Tools -> then Stop Requests You should receive a message like: Click OK, then your original browser window that was running the report should have a message like the one below: For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu

Data If you’re new to WebFOCUS, the first thing you’ll need (after a license) is data. The primary data source available in WebFOCUS is CSU’s ODS. The ODS hosts data from the Finance(Kuali), HR(Oracle EBS), Student(Banner), Foundation, Facilities, and Research Enterprise Systems. WebFOCUS is also connected to several college/departmental data sources hosted on Microsoft’s SQL Server. As you create new reports in WebFOCUS the first thing you’ll be prompted for is data. Below is a list of commonly used data sources in each of the Enterprise Systems and where to find them from the “New Report” prompt within WebFOCUS. You can get more information about these and other ODS data sources in the ODS Data Dictionary. A number of the data sources you’ll see have a “_bv” suffix. This stands for Business View and generally means that the Information Systems department has worked with a subject matter expert to provide something ‘extra’ in the Business View (like combining multiple sets of data into it). FINANCE Finance data from the ODS is available in the /finance_bv and /finance folders. Access to Finance data requires the ODS role “Finance Data”, which can be requested here. The underlying finance data is stored either in the ODS or newer Finance Data Mart. The Finance Data Mart contains transaction level General Ledger data. It contains TEM data and a limited amount of ARVID data. Common tables for Kuali Financial data GL_TRN_MASTER_BV Contains transaction level details the Kuali Financial System. Contains a specific subset of fields from the Finance Data Mart GL_OBJ_SUMMARY_BV Contains finance data summarized and cumulative at the Fiscal Period level with pre-calculated fields for ‘Smart Dollars’. The cumulative is also across Fiscal Periods. For example, Fiscal Period 03 will include data from Fiscal Periods 01, 02, BB, and CB. PCARD_DATA_MART Combines data from the Finance Data Mart and PCard data for reporting on PCard transactions. HUMAN RESOURCES Human Resource data from the ODS is available in the /human_resources folder. Access to HR data requires the ODS role “Human Resources” along with Private Data Access for your department(s). Data access requests can be made here. Common tables for HR data CSUH_CURRENT_EMPLOYEE_PRIMARY All active primary employee assignments including non-protected person-related information, current record only. CSUH_DEPT Basic information for all University departments CSUH_EDUCATION Education information as provided by the employee (includes the same population as CSUH_Current_Employee_Hist). STUDENT Student data from the ODS is available in the /student_bv and /student folders. Access to Student data requires the ODS role “Student Data”, which can be requested here. Common Tables for Banner Student Data CSUS_APPLICANT Applicant information for all applications, including all student levels, for all active and future terms. Does not include records for previous (or “inactive”) terms. TERM_SECTION_AH_RECENT_BV A WebFOCUS Business View that joins CSUS_TERM_INFO_AH_RECENT and CSUS_SECTION_INFO_AH CSUS_AWARDS_CONFERRED Awards conferred. Deceased students are excluded from the view For questions or support requests, please contact the Data Warehouse team at, doit_data_warehouse@colostate.edu