Versions Compared

Old Version 55

changes.mady.by.user Patricia Donohue

Saved on

compared with

New Version 56

changes.mady.by.user Patricia Donohue

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Panel
panelIconIdatlassian-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.

...

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

(tick)

(tick)

(tick)

(tick)

CCC Data 2.0

CCCApply Noncredit Application

(tick)

(tick)

(tick)

(tick)

CCC Data 2.0

CCCApply International Application

(tick)

(tick)

(tick)

(tick)

CCC Data 2.0

California College Promise Grant Application

(tick)

(tick)

(tick)

(tick)

CCC Data 2.0

Decrypted LGBTQ Data (and Report)

(tick)

(tick)

(minus)

(tick)

CCC Data 2.0

Multiple Measures Placement Service (MMPS)

(tick)

(tick)

(tick)

(tick)

CCC Data 2.0

COCI: Chancellor's Office Curriculum Inventory

(tick)

(tick)

(tick)

(tick)

CCC Data 2.1

C-ID: Course ID Numbering System

(tick)

(tick)

(tick)

(tick)

CCC Data 2.1

CANVAS (Opt-in)

(tick)

(minus)

(tick)

(minus)

(tick)

CCC Data 2.1

CO-MIS

(tick)

(minus)

(minus)

(minus)

CCC Data 2.1

NOVA

(tick)

(minus)

(minus)

(minus)

CCC Data 2.1

Launchboard

(tick)

(minus)

(minus)

(minus)

CCC Data 2.1

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

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

...

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.

  1. Requesting Access

  2. Establishing the VPN Configuration

  3. Establishing the Account Credentials

  4. Connecting to the (Redshift) Database Using SQL Workbench

  5. Data Access Validation & Testing Queries

Participation

Participant

Is responsible for…

College / District Researcher

  • sends request for direct access connection to their ES CRM.

  • identifies their primary IT and Researcher contacts.

  • Vetting the user(s) and confirming authorization of requested scope.

Enabling Services (ES) College Relationship Manager (CRM)

  • 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

Note

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

...

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.

  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 the district has provided for this purpose.

...

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:

  1. Prerequisite: Install Java (11 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

Info

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

...

  • 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

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

Info

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

canvas

canvas_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:

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

  2. Make sure you are connected to the Colleges’ VPN.

Tip

Next Step: Validate your connection and data access.

...