Updated: July 14, 2021
...
| 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. |
| Tip |
|---|
Next Step: College or district establishes their connection to the Data Warehouse (Redshift) databases using SQL Workbench. |
...
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.
...
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 |
...
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 |
|---|
Required: Colleges must be “live” with the basic DW Direct Connect/wiki/spaces/ES/pages/2401272488service in order to implement Canvas DW 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 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). |
...
Making Queries
After the database connection is validated, you will be given the following prompt that will allow you to entry SQL Queries.
...