This guide provides an implementation configuration overview for

Tip

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

This guide provides an implementation configuration overview for establishing a direct access connection (ODBC/JDBC) to the CCC Data Warehouse for authorized CCC colleges and districts.

Panel

panelIconId	atlassian-info
panelIcon	:info:
bgColor	#F4F5F7

Secure access to CCC Data is provided to institutional researchers, college and district administrators, and other decision - makers across at the California Community Colleges, district offices, and the Chancellor’s Office, where these critical data may be used to support instructional and institutional decision-making aligned with the Chancellor's Office Vision for Success2030.

...

For authorized Researchers at CCC institutions who want to connect local applications to data sources within CCC Data, the CCC Tech Center (CCCTC) now supports direct access connection to the CCC Data Warehouse as an alternate or additional option to the CCC Data Warehouse (Jaspersoft) Report Server. Local applications may include an analytics or business intelligence application such as Tableau or Power BI.

...

Note
IMPORTANT: The CCC Data Warehouse is not to be used as a staging ground for data distribution to your local data warehouse. This type of usage will not be permitted as it places an undue load and cost on the CCC Data infrastructure.

Panel

panelIconId	atlassian-info
panelIcon	:info:
bgColor	#F4F5F7

Reminder: Direct access is an alternate or additional option to the CCC Data Warehouse Report Server for authorized CCC institutional researchers.

...

CCCApply: Application (Credit and Non-Credit App)
CCCApply: International Application
California College Promise Grant Application
Multiple Measures Placement Service (MMPS)
MyPath Data
Canvas Data (Opt-in required)
Chancellors Office Curriculum Inventory (COCI)
Course Identification Numbering System (C-ID)
NOVA (CCCCO Only)
Launchboard (CCCCO Only)
MIS (CCCCO Only)
CTEOS (CCCCO Only)

Table A: Data Availability Chart

Source Data Set	CCCCO Access		College Access		Requires Opt-In for inclusion

Introduced in Release Version

DW Direct Connection	DW Report Server	DW Direct Connection	DW Report Server
CCCApply Standard Application

CCC Data 2.0


CCCApply Noncredit Application

CCC Data 2.0


CCCApply International Application

CCC Data 2.0


California College Promise Grant Application

CCC Data 2.0


Decrypted LGBTQ Data (and Report)

CCC Data 2.0


Multiple Measures Placement Service (MMPS)

CCC Data 2.0


MyPath
COCI: Chancellor's Office Curriculum Inventory

CCC Data 2.1


C-ID: Course ID Numbering System

CCC

Canvas Data 2

.1CANVAS MIS

(Opt-in)
CO-MIS

CCC Data 2.1

NOVA


NOVA

CCC Data 2.1

Launchboard

CCC Data 2.1

Launchboard


CTEOS

CCC Data 2.1

Info

Panel

panelIconId	atlassian-info
panelIcon	:info:
bgColor	#F4F5F7

Data dictionaries for the above data sources are available on the CCC Data Warehouse documentation site.

...

Upon request from an authorized individual at a college or district office (often the Dean overseeing Institutional Research), one or more members of the CCCTC Enabling Services team will work directly with your college IT group to configure a custom site-to-site VPN based on your system, and establish secure direct access connection to the CCC Data Warehouse from your college (or district). Following the successful implementation and query validations, the ES Support team will be able to provide post-implementation (Live) support through the CCCTechnology.info support channel.

...

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

Participation

Participant	Is responsible for…
College / District Researcher	sends request for direct access connection to their ES

CRM

College Experience Manager (CEM).
identifies their primary IT and Researcher contacts.
Vetting the user(s) and confirming authorization of requested scope.

Enabling Services (ES) College Relationship Manager (

CRM

CEM)	handles the incoming request for direct access and initiates the ES processes.
ES Implementation Configuration Engineer (ICE)	communicates directly with college primary IT/Network contact and provides access to online documentation. oversees the implementation project, tracks tasks, and communicates status updates.
College / District IT/Network Admin	ensures ICE engineer has required system configuration information to support project (see details below). works directly with ICE engineer to implement site-to-site VPN, connection to the database, and account login. provides support to local Researchers working remotely to connect to VPN.
ES Implementation Configuration Engineer (ICE)	facilitates configuration of custom VPN and tunnel based on college system information. creates service account and provides secure login credentials to authorized user(s). supports IT contact with connection and initial query testing. provides implementation status updates and upon completion, hands off “live” account to ES Support as needed.
ES Support Services	facilitates issue resolution for post-implementation (live) support needs from college primary contacts

tip

Note
Colleges and districts using their own analytics/BI applications are responsible for their own support in the use of these tools.
support in the use of these tools.

...

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

First Step: An authorized individual sends a request to your ES College Relationship Experience Manager (CRMCEM) to implement a direct access connection (ODBC/JDBC) to the CCC Data Warehouse.

...

The first step in establishing a direct access connection is for an authorized individual at the college or district (typically the Vice Chancellor or Dean overseeing Institutional Research) to make a request directly to the CRM CEM assigned to your college. Upon approval, your CRM CEM will initiate the implementation process by passing the required information to the ES implementation team to get started on your custom VPN and account credentials.

Please identify and provide the following information to your CRMCEM:

Primary Researcher Contact: Name and email address of the Researcher who will be responsible for the account login credentials.
Primary IT/Network Admin Contact: Name and email address of the IT/Network Admin contact who will work with ES to establish the site-to-site VPN connection.
System Configuration Information: Firewall make, model, and version, and IP information.

Panel

panelIconId	atlassian-note
panelIcon	:note:
bgColor	#F4F5F7

Start with Your

...

CEM: The initial request should be made to your College Relationship Manager. If you do not know who your

...

tip

CEM is, send an email to

...

cems@ccctechcenter.org.

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: ES ICE engineer works with your primary IT contact(s) to facilitate the configuration of the VPN and establish the account credentials.

...

Establishing the VPN Configuration

...

tip

Note
Reminder: Ensure your ES ICE engineer has all necessary system configuration information, including your firewall make, model, and version, and your IP information.

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: ES ICE engineer establishes the service account and provides the account login credentials to primary IT contact.

...

Establishing the Account Credentials

In order to access the (Redshift) databases, a service account will be established based on the scope of your authorized access (MIS scope). Your ES ICE engineer will provide the account login credentials (username and password), as well as the database endpoint (DNS) and schemas roles (also provided in this document), to your primary IT contact through secure transmission.

Panel

panelIconId	atlassian-note
panelIcon	:note:
bgColor	#F4F5F7

Note that the login credentials are a Service Account, while they are issued to an individual they are intended to be used to connect applications (such as Tableau) to the CCC Data Warehouse. Protecting these credentials is the responsibility of the college.

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.

...

Info
The <misScope> will appear in the user’s credentials (username) and also in the database schema formats used in SQL queries during the Connecting Using SQL Workbench validations.

...

.

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: College or district establishes their connection to the Data Warehouse (Redshift) databases using SQL Workbench.

...

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

Complete VPN Configuration & Log In from Internal Network: Log in and connection to the Redshift cluster must originate from your local network.
Account Login Credentials: Obtain your account credentials (username and password) and the database endpoint (DNS), provided by your ES implementation team.
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 the district has provided for this purpose.

...

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: Follow the instructions 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 more in depth regarding the actual installation steps.

Process Overview:

Info
Reference: Visit the AWS website for Connecting to Your Cluster Using SQL Workbench

...

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

...

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: At this point, you need to download Redshift drivers and test the connection.

...

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

Tippanel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

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

...

Name: The name for this connector that you will reference later when you want to access this database again. This is for your use only, so make it descriptive enough to where you understand clearly which DB you are accessing. For example, if you are going to query the COCI DB, something like `DWH-COCI` might be appropriate. Signifying the environment in this name is highly recommended.
URL: Where to reach the database. Edit the autofilled string here with your custom endpoint (example shown below).

Environment

Endpoint

Production

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

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

canvas

canvas2

Note
Replace {{database}} with the specific Database Name shown in Table B below. Where “database” is the name of the database to which you are trying to connect.

Username: the PostgreSQL username you are using to login.
Password: the password for the username above.

...

Table B: Application Database Names

Panel

panelIconId	atlassian-info
panelIcon	:info:
bgColor	#F4F5F7

The value of <misScope> can be derived from the prefix of your Data Warehouse Direct user name which is in the format of <misScope>_<firstInitial><lastName> i.e. 000_jdoe where 000 would represent their MIS scope value. An example of the resulting schema for accessing tables within the Application database = dw_apply_read_000.

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

canvas

canvas2

canvas

canvas2_rs_<misScope>

Info
Note: Data dictionaries for the above data sources are available on the CCC Data Warehouse documentation site.

Note

Troubleshooting: If the test fails, the two most common fixes are:

Verify you have entered the URL, your username, and password correctly.
Make sure you are connected to the Colleges’ VPN.

...

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: Validate your connection and data access.

...

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.

Info
CCCTC Enabling Services College Staff Support is available to assist with issues connecting to the CCC Data Warehouse.

...

Using provided user documentation (this user guide), college configures BI tool to connect to the Data Warehouse.
College will confirm local connection and successful login, as well as perform optional data validation activities.
Online documentation is available to support data access validation activities for Researchers.

...

activities for Researchers.

Panel

panelIconId	atlassian-check_mark
panelIcon	:check_mark:
bgColor	#F4F5F7

Next Step: 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 Canvas CD2 data will require the college Canvas Administrator to generate and pass their Canvas Data 2 API credentials to the CCCTC Enabling Services Implementation Engineer as part of the configuration process. Once received and implemented, the data pipeline will be configured to pass your Canvas CD2 data to the CCC Data Warehouse. Following that initial pass, the data will be updated nightlyper a configured schedule.

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

Note
Required: Colleges must be “live” with the basic DW Direct Connect service in order to implement Canvas DW Data 2 Direct Connect access. If your college has not yet configured site-to-site VPN access with the CCC Data Warehouse, please contact Enabling Services to get started.

Info
Learn more about the Canvas DW Data 2 Direct Connect service, including the college preparatory requirements before and during the implementation process: Obtaining Your Canvas Data API Access Credentials (for the CCC Data Warehouse) /wiki/spaces/ES/pages/3172564993.

...

Making Queries

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

...

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

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

...

Versions Compared

Old Version 46

New Version 72

Key

Contents

Table A: Data Availability Chart

Participation

Establishing the VPN Configuration

Establishing the Account Credentials

Scope of Access

Table B: Application Database Names

Canvas Data 2 with DW Direct Connect Service

Making Queries

Page Comparison

Versions Compared

Old Version 46

New Version 72

Key

Contents

Table A: Data Availability Chart

Participation

Establishing the VPN Configuration

Establishing the Account Credentials

Scope of Access

Table B: Application Database Names

Canvas Data 2 with DW Direct Connect Service

Making Queries