Introduction

The Student Journey data model, introduced in the CCC Data Warehouse with the CCC Data 3.0.0 release, consolidates student information from multiple datasets into a unified view for CCC researchers. By integrating data from various sources, it streamlines access, eliminating the need to search through multiple databases and tables. Designed for analytics, this dataset enhances how researchers view, query, and utilize the CCC Data Warehouse.

Contents

Benefits for Researchers

The Student Journey dataset integrates data from CCCApply and CO-MIS, providing researchers with a more comprehensive and accessible view of student information.

With the CCC Data 3.0.0 release, this new database consolidates datasets and simplifies access permissions, eliminating the need to navigate multiple sources. Designed specifically for analytics, it offers optimized tables that improve query performance and usability.

The Student Journey model is built for adaptability, evolving based on researcher needs. Future enhancements will be guided by user feedback, ensuring the dataset continues to support relevant and impactful research.

Note: Existing databases and tables outside the Student Journey dataset remain unchanged and will continue to be supported.

Scope of this User Guide

This guide provides detailed information on the Student Journey dataset, including an overview of the data model, data sources, and instructions on how to use it, complete with example queries.

For direct access to the Data Warehouse and information on available datasets, refer to the Data Warehouse Direct Connect Access User Guide at: [CCC Data Warehouse Public Documentation - Confluence].

Student Journey Goals

The Student Journey dataset is designed to:

• Improve the usability and accessibility of student data for researchers.

• Establish clear correlations between MIS and CCCApply data, enabling more comprehensive analysis.

Data Model Explanation

The Student Journey dataset is design

Available Data Sources

Direct access connection is supported for the following data sources within the CCC Data Warehouse:

Implementation Process

When a request is received from an authorized individual at a college or district office (often the Dean overseeing institutional research), the CCCTC Enabling Services (ES) team will work directly with the college IT group to configure a custom site-to-site VPN based on the system used by the college. Enabling Services will then establish a secure direct access connection to the CCC Data Warehouse from the college (or district). After successful implementation and query validations, the ES Support team will be able to provide post-implementation (live) support through the CCCTechnology.info support channel.

Process Overview

The process to implement a direct access connection to the CCC Data Warehouse (Redshift) is described below, beginning with a summary of the roles and responsibilities for participants.

1. Request Access

2. Establish VPN Configuration

3. Establish Account Credentials

4. Connect to (Redshift) Database Using SQL Workbench

5. Data Access Validation & Testing Queries

Request Access

The following are the general steps to establish a direct access connection (ODBC/JDBC) to the CCC Data Warehouse.

1. An authorized individual at the college or district makes the request directly to the CEM assigned to the college. This request is typically made by the Vice Chancellor or the Dean overseeing institutional research.

2. Upon approval of the request, the CEM initiates the implementation process by passing the required information to Enabling Services (ES).

3. The ES implementation team then creates the college’s custom VPN and sets up the account credentials.

4. Provide the following information to the CEM when requesting direct access:

-Contact name and email address of the primary researcher responsible for the account login credentials

-Contact name and email address of primary IT/network admin who will work with ES to establish site-to-site VPN connection

-System configuration information such as firewall make, model, and version, as well as IP information

Establish VPN Configuration

The ES Implementation Configuration Engineer (ICE) works with your primary IT contact(s) to facilitate the configuration of the VPN and to establish the account credentials.

A secure direct access (ODBC/JDBC) connection between your college or district (MIS scope) and the CCC Data Warehouse Redshift cluster is enabled using a custom site-to-site VPN tunnel and a service account with authorized user credentials. The data that is accessible by the college or district is based on the scope of access authorized to the organization.

Setting up the VPN and the service account can occur simultaneously; however the configuration of the VPN should be handled first, as it often requires more time to implement (due to required approvals and IT resources).

Establish Account Credentials

In order to access the (Redshift) databases, a service account must be established based on the scope of your authorized access (MIS scope). The ES ICE establishes the service account and provides the account login credentials to the primary IT contact at the college/district. (The login credentials consist of the username and password.)

The ES ICE also provides the database endpoint (DNS) and schema roles to the college or district’s primary IT contact via secure transmission. (The schema roles are also provided in this document.)

Scope of Access

Authorized access to the CCC Data Warehouse for colleges and districts using direct access connection (ODBC/JDBC) is permitted through secure login credentials to your local network VPN.

Configuration of the authorized user’s account credentials are based on the scope of their access, where the scope is defined by their 3-digit MIS code. In this context, the scope of their access is defined as <misScope>. For example, authorized district users will have a scope of access that is represented by their district MIS code (example: 210). Authorized college users will have a scope represented by their college MIS code (example: 211).

Connect to the Data Warehouse (Redshift Cluster)

This section is designed for authorized colleges who want to gain access to the CCC Data Warehouse Redshift cluster databases. AWS Redshift is a secure, cloud-based data warehouse service used for collecting and storing large-scale data sets and enables users to analyze data using various BI tools.

Connection Requirements

In order to complete the connection process (SQL Workbench install) and access the CCC Data Warehouse (Redshift cluster), the college/district will need to meet the requirements below.

1. Complete VPN Configuration & Log In from Internal Network: Log in and connection to the Redshift cluster must originate from your local network.

2. Account Login Credentials: Obtain your account credentials (username and password) and the database endpoint (DNS), provided by your ES implementation team.

3. Database Connection Strings & Schemas: Ensure the database names, connection strings, and schema roles are correct to access specific databases (see Table B: Application Database Names below).

Remote Connection: If you are working remotely and the district has authorized it, you will need to establish a connection to the district’s internal network using a separate VPN client that the district has provided for this purpose.

...

...

Connect using SQL Workbench

Follow the instructions in this section to install SQL Workbench and configure database access connection. SQL Workbench is a database GUI used for accessing many different databases. The instructions below are similar to those found on the AWS website for SQL Workbench, but are more in-depth regarding the specific installation steps.

The process for connecting to the Data Warehouse using SQL Workbench is as follows:

1. Prerequisite: Install Java (v11 or higher recommended)

2. Download & Install SQL Workbench

3. Download Redshift Drivers & Test Connection

4. Configure Database Connection String

5. Table B: Application Database Names & Schema Formats

...

SQL Workbench requires a Java version 11 (or higher) runtime environment. You can use either a JRE ("Runtime") or a JDK ("Development Kit").

Download & Install SQL Workbench

Download SQL Workbench/J here: https://www.sql-workbench.eu/downloads.html.

Select the generic installation package for all systems with optional libraries. (We recommend saving the package on your local machine with an informative name that is easy to access.)

...

Unzip the downloaded folder and select the sqlworkbench.jar (JAR) file inside to open SQL Workbench.

...

The application is now installed and the Select Connection Profile screen appears.

...

Download Redshift Drivers and Test Connection

Download the Amazon Redshift JDBC driver version 2.0 to enable SQL Workbench to communicate with Redshift. Below is the zip file to download:

JDBC 4.2–compatible driver (without the AWS SDK) and driver dependent libraries for AWS SDK files version 2.0

Once you have downloaded and unzipped the Redshift drivers to a directory you choose on your machine, go back to the SQL Workbench installed and select 'Amazon Redshift' from the Driver dropdown.

At the prompt asking you to configure the driver, select ‘Yes,’ as shown below.

...

The Manage Drivers menu will appear. Click the Folder icon and navigate to the directory that contains the driver package that you recently unzipped, as shown below.

...

Select the Redshift driver, which will be named ‘Redshift*.jar,’ then click ‘Open.’ See the example below.

...

Once the driver file is selected, click ‘OK’ on the Manage Drivers screen.

Configure the Database Connection String

Configure the Connection Profile screen with your account credentials (username and password) and the connection string.

With the drive selected, a sample endpoint will autofill in the URL field. This endpoint needs to be modified, and several other fields need to be completed. See the example below.

...

The essential fields to configure are as follows:

• Name: The name for the connector that you will reference later to access this database again. This is for your use only, so make it clearly descriptive for the database you will be accessing. Identifying the environment in the name is highly recommended. (For example, if you are going to query the COCI DB, a name such as `DWH-COCI` might be appropriate.)

• URL: Where to reach the database. Edit the auto-filled string in this field with the custom endpoint (example shown below).

...

Environment

...

Endpoint

...

Production

...

jdbc:redshift://dwh-prod.ccctechcenter.org:5439/{{database}}

Ex: jdbc:redshift://dwh-prod.ccctechcenter.org:5439/canvas2

• Username: The PostgreSQL username you are using to login.

• Password: The password for the username above. 

Place a checkmark in the Autocommit box, as shown in the example below.

...

Click Test in the lower right to verify you have connected successfully. Then click OK.

...

Table B: Application Database Names and Schema Formats

...

Application (Data Source)

...

Database Name

...

Query Schema Format

...

CCCApply Application

...

application

...

dw_apply_read_<misScope>

...

CC College Promise Grant

...

bogfw_application

...

dw_apply_read_<misScope>

...

CCC International Application

...

intl_application

...

dw_apply_read_<misScope>

...

Multiple Measures Placement Service (MMPS)

...

mmps

...

dw_apply_read_<misScope>

...

Chancellor's Office Curriculum Inventory (COCI)

...

coci

...

dw_coci_read_<misScope>

...

Course Identification Numbering System (C-ID)

...

cid

...

dw_cid_read_<misScope>

...

Canvas Data 2

...

canvas2

...

dw_canvas2_read_<misScope>

Data Access Validation & Testing

Once connected to the CCC Data Warehouse with your own tool, colleges are encouraged to conduct a series of data access tests and activities, which may include:

• Test Query Data Sources

Through local resources, the participant is able to connect to, and run at least one query against each of the available datasets (CCCApply application, CCCApply international application, CCCApply Promise Grant, and Multiple Measures Placement Service).

• Connect Data to Local Data Warehouse

If a local data warehouse is available, configure a CCC Data Warehouse table as an external table in the local database source. This supports data within the CCC Data Warehouse to be connected to your local data warehouse without the need to copy data to the local machine or district server.

ES will work with colleges to validate initial data queries.

• Using provided user documentation (such as this user guide), the college configures their BI tool to connect to the Data Warehouse.

• The college confirms local connection and successful login, and also performs optional data validation activities.

• Online documentation is available to support data access validation activities for researchers.

• Work with your ES ICE (engineer) to confirm access validation and query tests. Discuss any unfinished implementation steps before hand-off to ES Support.

Canvas Data 2 with DW Direct Connect Service

Colleges may request to access their Canvas Data 2 (CD2) data through the CCC Data Warehouse via direct access connection (ODBC/JDBC). The ability to access CD2 data will require the college Canvas Administrator to generate and pass their Canvas Data 2 API credentials to the CCCTC Enabling Services (ES) Implementation Configuration Engineer (ICE) as part of the configuration process. Once received and implemented, the Canvas v2 data warehouse integration will be configured to pass your CD2 data to the CCC Data Warehouse. Following that initial pass, the data will be updated per a configured schedule.

To get started, send an email requesting Canvas Data 2 Data Warehouse Direct Connect access for your college to your College Experience Manager (CEM) at CCCTC Enabling Services.

Access In-Progress Application Data in the CCC Data Warehouse

The CCCApply “in_progress_applications” table is available in the Data Warehouse via Direct Connect Access to make it easier for researchers to perform quick queries without having to include the joins manually for every query. 

See related content in Accessing In-Progress Application Data in the CCC Data Warehouse, featuring tables and query information.

Making Queries

After the database connection is validated, you will be given the following prompt that will allow you to enter SQL Queries.

...

A tab labeled Database Explorer displays more information about the database that your user has access to. Table names can be found there, as well as some additional basic information about the DB.

...

You can also run SQL against objects your DB user has access to.

...

Example Query to run on Data Warehouse Data

To get a row count of a table, run the following query:

select count(*) from dw_apply_read_523.contact;

You will need to replace the dw_apply_read_523 with the appropriate schema your user has access to. You can also use the database explorer in SQL workbench to navigate the data.

CCC Data Warehouse Direct Access FAQs

Q: Can I have more than one VPN connection for my district?

A. Not at this time. If this is needed, please discuss with your CEM.

Q: My IT department does not want to create a site-to-site VPN, can I just have an individual VPN connection? 

A. No, the CCC Tech Center is not staffed to handle individual user VPN requests.

Q: Can I have more than one individual at the college (or district office) connect to the VPN at a time?

A: While the login credentials are intended to serve as a Service Account for the connection of local applications to the CCC Data Warehouse, individual accounts may be requested for authorized users where this is needed.

More Information: See CCC Data Warehouse - Frequently Asked Questions for more frequently asked questions.

...

...

Introduction

The Student Journey data model, introduced in the CCC Data Warehouse with the CCC Data 3.0.0 release, consolidates student information from multiple datasets into a unified view for CCC researchers. By integrating data from various sources, it streamlines access, eliminating the need to search through multiple databases and tables. Designed for analytics, this dataset enhances how researchers view, query, and utilize the CCC Data Warehouse.

...

Contents

...

Benefits for Researchers

The Student Journey dataset integrates data from CCCApply and CO-MIS, providing researchers with a more comprehensive and accessible view of student information.

With the CCC Data 3.0.0 release, this new database consolidates datasets and simplifies access permissions, eliminating the need to navigate multiple sources. Designed specifically data analysis, it allows for the creation of optimized tables that improve query performance and usability.

The Student Journey model is built for adaptability, evolving based on researcher needs. Future enhancements will be guided by user feedback, ensuring the dataset continues to support relevant and impactful research.

Note: Existing databases and tables outside the Student Journey dataset remain unchanged and will continue to be supported.

Scope of this User Guide

This guide provides detailed information on the Student Journey dataset, including an overview of the data model, data sources, and instructions on how to use it, complete with example queries.

For direct access to the Data Warehouse and information on available datasets, refer to the Data Warehouse Direct Connect Access User Guide.

Student Journey Goals

The Student Journey dataset is designed to:

• Improve the usability and accessibility of student data for researchers.

• Establish clear correlations between CO-MIS and CCCApply data, enabling more comprehensive analysis.

Data Model Explanation

The Student Journey data model uses a canonical modeling approach to unify data from multiple sources into a single, structured framework. Instead of maintaining separate, sparsely populated tables for different data sources, this model consolidates attributes into core tables with linked satellite tables, improving data consistency and usability.

Core Tables

These tables serve as the foundation of the model:

• Application

• College

• District

• Needs_and_Interests

Satellite Tables

Each core table is associated with satellite tables that store additional details:

• Education

• Contact_Information

• Personal_Information

• Personal_Sociodemographic

Consult the Student Journey Database Data Dictionary to identify the source, table, description, and example values for each data field.

Description of the Schema

Researchers can query the Student Journey dataset in the CCC Data Warehouse via direct access. The following tables are available:

Core Tables

• ccc__district_vw

• ccc__college_vw

• ccc__needs_and_interests_vw

Application and Student Data Tables

• apply__mis_associative_entity_vw

• apply__application_vw

• apply__college_attended_vw

• apply__contact_information_vw

• apply__education_vw

• apply__needs_and_interests_vw

• apply__personal_information_vw

• apply__personal_sociodemographic_vw

• student_journey__dictionary_mvw

These tables consolidate data from multiple sources and follow a canonicalized structure to ensure consistency and ease of use.

Using the Student Journey Data Model

This section provides guidance on interpreting and querying the tables within the Student Journey canonical model.

Example Query 1: Educational Attainment

To analyze educational attainment, researchers can use the apply__application_vw and apply__education_vwtables, as this attribute is linked to each student’s college application. The following examples demonstrate how to retrieve this information.

_{Figure 1: Application Table}

The application table includes fields common to all application data sources. The source field specifies the origin of each record, as shown in the table below.

_{Figure 2: Application Source}

The application_education table, linked to the source field, provides additional details on educational background and goals, as shown below.

_{Figure 3: Application Education Table}

By querying the apply__education_vw view, researchers can access attributes related to a student's educational background and future goals. This table includes both valid values and unknown values (represented as 0), but does not contain null or blank values.

To determine how many submitted applications include an edu_goal, researchers can perform a left join between the application and application_education tables using the query below.

_{Figure 4: Example Query}

SELECT count(*) as num_applications
  FROM apply__application_vw ca
LEFT JOIN apply__education_vw cae
    ON (ca.app_id = cae.app_id)
WHERE ca.source = 'stnd_application' 
   AND cae.field = 'hs_edu_level' ;

_{Figure 5: Query Result}

To generate a report on educational attainment by student, use the following query:

_{Figure 6: Example Query}

SELECT ccc_id,max(value)  as   hs_edu_level
  FROM apply__application_vw ca
LEFT JOIN apply__education_vw cae
    ON (ca.app_id = cae.app_id)
WHERE ca.source = 'stnd_application' 
   AND cae.field = 'hs_edu_level' 
   and ca.tstmp_create > '2025-01-01' group by ccc_id;

To generate a report on student sociodemographic information, use the following query.

_{Figure 7: Example Query}

SELECT ccc_id,max(value)  as   hs_edu_level
  FROM apply__application_vw ca
LEFT JOIN apply__education_vw cae
    ON (ca.app_id = cae.app_id)
WHERE ca.source = 'stnd_application' 
   AND cae.field = 'hs_edu_level' 
   and ca.tstmp_create > '2025-01-01' group by ccc_id;

This requires data from the apply__application_vw and apply__personal_information_vw tables.

_{Figure 8: apply_personal_Information_vw view}

_{Figure 9: apply_personal_sociodemographic_vw View}

By querying these tables, researchers can access attributes related to a student's sociodemographic status. For example, to determine how many students indicated they were married when completing their application, use the following query.

_{Figure 10: Marriage Information Query}

SELECT pi.app_id,sd.field,sd.value ,pi.value last_name FROM apply__personal_sociodemographic_vw sd inner join
apply__personal_information_vw pi on pi.app_id=sd.app_id where sd.field='married_or_rdp'
and pi.field='lastname';

This query returns the following results:

_{Figure 11: Query Results}

More Connection Info: DW Direct Access User Guide

For details on connecting to the Data Warehouse and accessing current datasets, refer to the Data Warehouse Direct Connect Access User Guide. 

Student Journey Application Database Name and Schema Format

When connecting to the Data Warehouse using the instructions in the Data Warehouse Direct Connect Access User Guide, use the following details for the Student Journey dataset:

• Database Name: Student_Journey

• Schema: public

All tables are stored in the public schema, eliminating the need to navigate to a specific schema based on your MIS code. Data access is automatically managed and protected.

Data Access Validation & Testing for Student Journey

To validate your connection to the Student Journey dataset, follow the instructions in the Data Warehouse Direct Connect Access User Guide and run the following SQL query:

select * from student_journey.public.ccc_district_vw
limit 10;

This query should return 10 rows, confirming successful connectivity.

Colleges are encouraged to perform additional data access tests, such as running:

select * from student_journey.public.apply__mis_associative_entity limit 10;

General Data Warehouse Help

If you need assistance with the CCC Data Warehouse, visit the http://CCCTechnology.info  support channel. There, you can receive help from the Enabling Services Support (ESS) team and collaborate with other colleges and districts on using the CCC Data Warehouse.