Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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. |
...
Contents
Table of Contents | ||
---|---|---|
|
...
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 | DW | DW Direct | DW | |
CCCApply Standard Application |
|
|
|
|
CCC Data 2.0
CCCApply Noncredit Application |
|
|
|
|
CCC Data 2.0
CCCApply International Application |
|
|
|
California College Promise Grant Application |
|
|
|
Decrypted LGBTQ Data (and Report) |
|
|
|
Multiple Measures Placement Service (MMPS) |
|
|
|
|
MyPath |
|
|
|
| |
COCI: Chancellor's Office Curriculum Inventory |
|
|
|
C-ID: Course ID Numbering System |
|
|
|
|
Canvas Data 2 |
(Opt-in) |
|
|
|
|
|
CCC Data 2.1
CO-MIS |
|
|
|
|
CCC Data 2.1
NOVA |
|
|
|
Launchboard |
|
|
|
| |
CTEOS |
|
|
|
|
Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
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 |
|
|
Enabling Services (ES) College Relationship Manager ( |
CEM) |
|
ES Implementation Configuration Engineer (ICE) |
|
College / District IT/Network Admin |
|
ES Implementation Configuration Engineer (ICE) |
|
ES Support Services |
|
Note |
---|
Colleges and districts using their own analytics/BI applications are responsible for their own support in the use of these tools. |
...
Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
Start with Your CRMCEM: The initial request should be made to your College Relationship Manager. If you do not know who your CRM CEM is, send an email to crms@ccctechcenter cems@ccctechcenter.org. |
...
Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
Next Step: ES ICE engineer works with your primary IT contact(s) to facilitate the configuration of the VPN and establish the account credentials. |
...
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
Next Step: ES ICE engineer establishes the service account and provides the account login credentials to primary IT contact. |
...
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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:
Prerequisite: Install Java (11 or higher recommended)
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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/ |
canvas2
|
Username: the PostgreSQL username you are using to login.
Password: the password for the username above.
...
Table B: Application Database Names
Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
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 |
canvas2 |
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:
|
...
Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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?
...