Versions Compared

Old Version 77

changes.mady.by.user Liza Tont

Saved on

compared with

New Version Current

changes.mady.by.user Patricia Donohue

Saved on

Key

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

Introduction

...

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)

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#F4F5F7

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

...

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.

...

This section is designed for authorized colleges who want to gain access to the CC 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.

...

  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.

...

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

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

...

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.

...

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

...

The important essential fields to configure are as follows:

  • Name: The name for this the 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. 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, something like a name such as `DWH-COCI` might be appropriate. Signifying the environment in this name is highly recommended.)

  • URL: Where to reach the database. Edit the auto-filled string here in this field with your 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

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#F4F5F7

IMPORTANT: Replace {{database}} with the specific Database Name shown in Table B below

.
Where “database” is the name of the

for the database to which you are trying to connect.

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

  • Password: the The password for the username above. 

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

...

Click `Test` 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 would represent their 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

TroubleshootingIMPORTANT: If 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. Make sure Ensure you are connected to the college’s VPN.

Panel
panelIconIdatlassian-check_mark
panelIcon:check_mark:
bgColor#F4F5F7

Next Step: Validate your connection and data access.

...

Data Access Validation & Testing

...

If a local data warehouse is available, configure a CCC Data Warehouse table as an “external table” 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.

Panel
panelIconIdatlassian-infonote
panelIcon:infonote:
bgColor#F4F5F7

Note: CCCTC College Staff Support is available to assist with issues connecting to the CCC Data Warehouse.  

ES works will work with college 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.

  • College will confirm The college confirms local connection and successful login, as well as perform and also performs optional data validation activities.

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

...

...

  • 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

...

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#F4F5F7

Important: Colleges must be “live” live with the basic DW Direct Connect service in order to implement Canvas 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. 

...

After the database connection is validated, you will be given the following prompt that will allow you to entry 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 herethere, as well as some additional basic additional information about the DB.

...

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

...

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

Code Block
languagesql
select count(*) from dw_apply_read_523.contact;

...

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.LT: The following link does not work; connect to the correct page and copy link.

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

Panel
panelIconIdatlassian-note
panelIcon:note:
bgColor#F4F5F7

To Get Help and Share HelpInfo: After successful implementation, if you need help using the CCC Data Warehouse, access the CCCTechnology.info support channel. In that forum, you can get help from the ES Support team. In addition, you can discuss use of the CCC Data Warehouse with other colleges and districts.

...