Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

(Note: DRAFT in PROGRESS. Currently in the process of merging new content with this existing content from the DW Direct Access User Guide. That draft work is being done outside of Confluence, but I’ve added the basis doc here. The existing Existing technical content here is being revised, deleted from, and added to, using new technical info from Jack Thompson. I have a separate draft of Jack’s new data model user guide content. Jack also needs to create and send me additional technical info, per our working meeting.)

Introduction

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

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

Secure access to CCC Data is provided to institutional researchers, college and district administrators, and other decision makers 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 2030.

...

Contents

...

About the CCC Data Warehouse

The CCC Data Warehouse is developed by the CCC Technology Center (CCCTC) in coordination with, and at the direction of, the CCC Chancellor's Office. A part of the Data Services Program initiative from the California Community Colleges Chancellor's Office, the CCC Data Warehouse provides the necessary infrastructure to the California Community College System to aggregate data from across disparate systems to an enterprise data warehouse.

Direct Access Connection

For authorized researchers at CCC institutions who want to connect local applications to data sources within CCC Data, the CCCTC 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 at CCC colleges /or districts may include an analytics or business intelligence application such as Tableau or Power BI.

...

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#F4F5F7

IMPORTANT: The CCC Data Warehouse should not 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.

Available Data Sources

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

  • 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

...

DW Direct
Connection

...

DW
Report Server

...

DW Direct
Connection

...

DW
Report Server

...

CCCApply Standard Application

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

CCCApply Noncredit Application

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

CCCApply International Application

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

California College Promise Grant Application

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

Decrypted LGBTQ Data (and Report)

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

Multiple Measures Placement Service (MMPS)

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

MyPath

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

COCI: Chancellor's Office Curriculum Inventory

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

C-ID: Course ID Numbering System

...

(tick)

...

(tick)

...

(tick)

...

(tick)

...

 

...

Canvas Data 2 (Opt-in required)

...

(tick)

...

 

...

(tick)

...

 

...

(tick)

...

CO-MIS

...

(tick)

...

 

...

 

...

 

...

 

...

NOVA

...

(tick)

...

 

...

 

...

 

...

 

...

Launchboard

...

(tick)

...

 

...

 

...

 

...

 

...

CTEOS

...

(tick)

...

 

...

 

...

 

...

 

...

panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

...

...

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

Participants, Roles, and Responsibilities

Participant

Is responsible for…

College/District Researcher

  • Sends request for direct access connection to the assigned ES College Experience Manager (CEM)

  • Identifies the primary IT and researcher contacts

  • Vets the user(s) and confirms authorization of requested scope

Enabling Services (ES) College Relationship Manager (CEM)

  • Handles the incoming request for direct access connection and initiates 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 the 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 so they can connect to the 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 of implementation, turns over “live” account to ES Support as needed

ES Support Services

  • Facilitates resolution of issues for post-implementation (live) support needs from college primary contacts

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#F4F5F7

IMPORTANT: Colleges and districts that use their own analytics/BI applications are responsible for their own support in using those tools.

...

Request Access

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

...

Panel
panelIconIdatlassian-note
panelIcon:note:
bgColor#F4F5F7

Note: The initial direct connection request should be made to your College Experience Manager. If you do not have the name of the CEM for your college, send an email to cems@ccctechcenter.org.

...

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.

...

Panel
panelIconIdatlassian-note
panelIcon:note:
bgColor#F4F5F7

Note: Ensure that the ES ICE has all necessary system configuration information, including the firewall make, model, and version, as well as IP information.

...

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.)

...

Panel
panelIconIdatlassian-note
panelIcon:note:
bgColor#F4F5F7

Note: 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/district.

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.

...

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

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

...

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.

Panel
panelIconIdatlassian-note
panelIcon:note:
bgColor#F4F5F7

Note: Colleges achieve direct connection access to the CCC Data Warehouse using SQL Workbench.

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.

...

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#F4F5F7

IMPORTANT: Beloware instructions for implementing SQL Workbench to connect to the CCC Data Warehouse. SQL Workbench is supported by the CCC Technology Center, and is recommended to assist with initial data validation but is not a requirement.

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.

...

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

For more information, visit the AWS website for Connecting to Your Cluster Using SQL Workbench.

Prerequisite: Install Java

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

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

For more information, see Section 4.1: Installing and Starting SQL Workbench in the SQL Workbench Manual.

Download & Install SQL Workbench

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

...

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:

...

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.

...

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

...

Table B: Application Database Names and Schema Formats

Panel
panelIconIdatlassian-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 represents the 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

dw_canvas2_read_<misScope>

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

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

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#F4F5F7

IMPORTANT: For troubleshooting if the connection test fails, the two most common fixes are as follows:

  1. Verify you have entered the URL, username, and password correctly.

  2. Ensure you are connected to the college’s VPN.

...

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:

...

  • 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.

...

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

For information about the latest Canvas Data 2 updates to the CCC Data Warehouse, refer to the following link: Canvas Data 2 Changes to Data Warehouse.

...

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.

...

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:

...

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?

...