{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Introduction","type":"text"}]},{"type":"paragraph","content":[{"text":"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. For more information on authorization, see ","type":"text"},{"text":"User Authorization and Authentication","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1488355626/Data+Warehouse+Report+Server+User+Guide#User-Authorization-%26-Authentication"}}]},{"text":".","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":" Secure access to CCC Data is provided to:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":" institutional researchers","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"college and district administrators","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"and other decision makers at the California Community Colleges, district offices, and the Chancellor’s Office","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"This critical data may be used to support instructional and institutional decision-making aligned with the Chancellor's Office","type":"text"},{"text":" Vision 2030","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.cccco.edu/About-Us/Vision-2030"}}]},{"text":". ","type":"text"}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":3},"content":[{"text":"Contents","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"maxLevel":{"value":"2"}},"macroMetadata":{"macroId":{"value":"6142fb56-1d1d-4bcd-96b3-52ee166ed7b8"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"994fec32-08b1-4395-80e4-432b8ec5e692"}},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"About the CCC Data Warehouse","type":"text"}]},{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"Data Services Program","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub"}}]},{"text":" initiative from the 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.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Direct Access Connection","type":"text"}]},{"type":"paragraph"},{"type":"layoutSection","content":[{"type":"layoutColumn","attrs":{"width":50.0},"content":[{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"CCC Data Warehouse (Jaspersoft) Report Server","type":"text","marks":[{"type":"em"},{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1488355626"}}]},{"text":". Local applications at CCC colleges/districts may include an analytics or business intelligence application such as Tableau or Power BI.","type":"text"}]},{"type":"paragraph","content":[{"text":"Once connected to the CCC Data Warehouse, your college or district will be able to:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect to available data sources through a site-to-site VPN using service account login credentials (provided through ","type":"text"},{"text":"Enabling Services","type":"text","marks":[{"type":"link","attrs":{"href":"mailto:cems@ccctechcenter"}}]},{"text":")","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Run SQL queries against available data sources ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect a local analytics/BI application through ODBC or JDBC access","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect CCC Data to a local data warehouse by setting up these data as external tables within your local data warehouse","type":"text"}]}]}]}]},{"type":"layoutColumn","attrs":{"width":50.0},"content":[{"type":"mediaSingle","attrs":{"layout":"center","width":539,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":347,"alt":"Decorative image showing how a direct connection is different than other connections.","id":"7ac94fd5-61a6-4eb8-b393-a1c022f74428","collection":"contentId-1628864764","type":"file","height":262}},{"type":"caption","content":[{"text":"Data Warehouse Connection Options","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Available Data Sources","type":"text"}]},{"type":"paragraph","content":[{"text":"Direct access connection is supported for the following data sources within the CCC Data Warehouse:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"California College Promise Grant Application","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Canvas Data 2 ","type":"text"},{"text":"(Opt-in required)","type":"text","marks":[{"type":"em"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"CCCApply: Application (Credit and Non-Credit App)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"CCCApply: International Application","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Chancellor's Office Curriculum Inventory (COCI)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Course Identification Numbering System (C-ID)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"CTEOS (CCCCO Only)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Launchboard (CCCCO Only)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"MIS ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Multiple Measures Placement Service (MMPS)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"MyPath Data","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"NOVA (CCCCO Only)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Student Journey Data (See the ","type":"text"},{"text":"Student Journey User Guide","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/4142563329"}}]},{"text":")","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Table A: Data Availability Chart","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"excerpt-include","parameters":{"macroParams":{"":{"value":"CCC Data Services Program Public Documentation"},"nopanel":{"value":"true"}},"macroMetadata":{"macroId":{"value":"ca13eedc-4006-4a4a-86b0-69a9bffa4ccf"},"schemaVersion":{"value":"1"},"title":"Insert excerpt"}},"localId":"cb5eb295-903c-427a-a3ab-b47ddb9db9f8"}},{"text":"Data dictionaries for the above data sources are available on the ","type":"text"},{"text":"CCC Data Warehouse documentation site","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1477378202/CCC%2BData%2BSources%2BData%2BDictionaries"}}]},{"text":". ","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"Implementation Process","type":"text"}]},{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"CCCTechnology.info","type":"text","marks":[{"type":"link","attrs":{"href":"http://CCCTechnology.info"}}]},{"text":" support channel.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Process Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Request Access","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Requesting-Access"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Establish VPN Configuration","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Establishing-the-VPN-Configuration"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Establish Account Credentials","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Establishing-the-Account-Credentials"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect to (Redshift) Database Using SQL Workbench","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Connecting-to-the-Data-Warehouse-(Redshift-Cluster)"}}]},{"text":" ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Data Access Validation & Testing Queries","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Data-Access-Validation-%26-Testing"}}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Participants, Roles, and Responsibilities","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"d97da4a2-066e-4d62-b8fe-f761f98ed74a"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"Participant ","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"paragraph","content":[{"text":"Is responsible for…","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"College/District Researcher","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Sends request for direct access connection to the assigned ES College Experience Manager (CEM)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Identifies the primary IT and researcher contacts","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Vets the user(s) and confirms authorization of requested scope","type":"text"}]}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"Enabling Services (ES) College Relationship Manager (CEM)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Handles the incoming request for direct access connection and initiates ES processes","type":"text"}]}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"ES Implementation Configuration Engineer (ICE)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Communicates directly with college primary IT/network contact and provides access to online documentation","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Oversees the implementation project, tracks tasks, and communicates status updates","type":"text"}]}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"College/District IT/Network Admin","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Ensures ICE engineer has required system configuration information to support the project (see details below)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Works directly with ICE engineer to implement site-to-site VPN, connection to the database, and account login","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Provides support to local researchers working remotely so they can connect to the VPN","type":"text"}]}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"ES Implementation Configuration Engineer (ICE)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Facilitates configuration of custom VPN and tunnel based on college system information","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Creates service account and provides secure login credentials to authorized user(s)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Supports IT contact with connection and initial query testing","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Provides implementation status updates and, upon completion of implementation, turns over “live” account to ES Support as needed","type":"text"}]}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[588.0]},"content":[{"type":"paragraph","content":[{"text":"ES Support Services","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1212.0]},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Facilitates resolution of issues for post-implementation (live) support needs from college primary contacts ","type":"text"}]}]}]}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"IMPORTANT:","type":"text","marks":[{"type":"strong"}]},{"text":" Colleges and districts that use their own analytics/BI applications are responsible for supporting those tools.","type":"text"}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Request Access","type":"text"}]},{"type":"paragraph","content":[{"text":"To establish a direct access connection (ODBC/JDBC) to the CCC Data Warehouse:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"An authorized individual at the college or district makes the request directly to the CEM assigned to the college. This request is typically made by the Vice Chancellor or the Dean overseeing institutional research.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Upon approval of the request, the CEM initiates the implementation process by passing the required information to Enabling Services (ES).","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The ES implementation team then creates the college’s custom VPN and sets up the account credentials.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Any current DWRS user who was not included in the CEM’s request (from step 2) may request direct access and begin using their self-supported BI tool to query available databases.","type":"text"},{"type":"hardBreak"},{"text":" Send the request to your district’s CEM email address and include this information:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Contact name and email address of the primary researcher responsible for the account login credentials","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Contact name and email address of primary IT/network admin who will work with ES to establish site-to-site VPN connection","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System configuration information such as firewall make, model, and version, as well as IP information","type":"text"}]}]}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note: ","type":"text","marks":[{"type":"strong"}]},{"text":"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 ","type":"text"},{"text":"cems@ccctechcenter.org","type":"text","marks":[{"type":"link","attrs":{"href":"mailto:cems@ccctechcenter.org"}}]},{"text":".","type":"text"}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Establish VPN Configuration","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"A secure direct access (ODBC/JDBC) connection between your college or district (MIS scope) and the CCC Data Warehouse Redshift cluster is enabled using a custom site-to-site VPN tunnel and a service account with authorized user credentials. The data that is accessible by the college or district is based on the scope of access authorized to the organization.","type":"text"}]},{"type":"paragraph","content":[{"text":"Setting up the VPN and the service account can occur simultaneously; however the configuration of the VPN should be handled first, as it often requires more time to implement (due to required approvals and IT resources).","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]},{"text":" Ensure that the ES ICE has all necessary system configuration information, including the firewall make, model, and version, as well as IP information.","type":"text"}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Establish Account Credentials","type":"text"}]},{"type":"paragraph","content":[{"text":"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).","type":"text"}]},{"type":"paragraph","content":[{"text":"The ES ICE also provides the database endpoint (DNS) and schema roles to the college or district’s primary IT contact via secure transmission. The schema roles are also provided in this document.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]},{"text":" 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.","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Scope of Access","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Configuration of the authorized user’s account credentials are based on the scope of their access, where the scope is defined by their three-digit MIS code. In this context, the scope of their access is defined as ","type":"text"},{"text":"","type":"text","marks":[{"type":"em"}]},{"text":". For example, authorized district users will have a scope of access that is represented by their district MIS code (example: 210). Authorized college users will have a scope represented by their college MIS code (example: 211). ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"The will appear in the user’s credentials (username) and also in the database schema formats used in SQL queries during the ","type":"text"},{"text":"Connect using SQL Workbench","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Connect-Using-SQL-Workbench"}}]},{"text":" validations.","type":"text"}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Connect to the Data Warehouse (Redshift Cluster)","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]},{"text":" Colleges achieve direct connection access to the CCC Data Warehouse using SQL Workbench.","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Connection Requirements","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Complete VPN Configuration & Log In from Internal Network: Log in and connection to the Redshift cluster must originate from your local network.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Account Login Credentials: Obtain your account credentials (username and password) and the database endpoint (DNS), provided by your ES implementation team.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Database Connection Strings & Schemas: Ensure the database names, connection strings, and schema roles are correct to access specific databases (see ","type":"text"},{"text":"Table B: Application Database Names","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Table-B%3A--Application-Database-Names"}}]},{"text":" below).","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"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 provided by the district. ","type":"text"}]},{"type":"rule"},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"IMPORTANT: ","type":"text","marks":[{"type":"strong"}]},{"text":"Below","type":"text"},{"text":" ","type":"text","marks":[{"type":"strong"}]},{"text":"are instructions for implementing ","type":"text"},{"text":"SQL Workbench","type":"text","marks":[{"type":"em"}]},{"text":" to connect to the ","type":"text"},{"text":"CCC Data Warehouse. SQL Workbench","type":"text","marks":[{"type":"em"}]},{"text":" is supported by the CCC Technology Center, and is recommended to assist with initial data validation ","type":"text"},{"text":"but is not a requirement","type":"text","marks":[{"type":"em"}]},{"text":". ","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Connect using SQL Workbench","type":"text"}]},{"type":"paragraph","content":[{"text":"Follow the instructions in this section to install ","type":"text"},{"text":"SQL Workbench ","type":"text","marks":[{"type":"em"}]},{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"To connect to the Data Warehouse using SQL Workbench:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Install Java","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Prerequisite%3A-Install-Java"}}]},{"text":" (v11 or higher recommended)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Download & Install SQL Workbench","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Download-%26-Install-SQL-Workbench"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Download Redshift Drivers & Test Connection","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#%5BhardBreak%5DDownload-Redshift-Drivers-and-Test-Connection"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure Database Connection String","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1628864764/CCC+Data+Warehouse+-+Direct+Access+User+Guide#Configure-the-Database-Connection-String"}}]}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"For more information, visit the AWS website for ","type":"text"},{"text":"Connecting to Your Cluster Using SQL Workbench","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-to-cluster.html"}}]},{"text":".","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install Java","type":"text"},{"text":" ","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"SQL Workbench requires a ","type":"text"},{"text":"Java version 11 (or higher)","type":"text","marks":[{"type":"strong"}]},{"text":" runtime environment. You can use either a JRE (\"Runtime\") or a JDK (\"Development Kit\").","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"For more information, see ","type":"text"},{"text":"Section 4.1: Installing and Starting SQL Workbench","type":"text","marks":[{"type":"em"}]},{"text":" in the ","type":"text"},{"text":"SQL Workbench Manual","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.sql-workbench.eu/manual/install.html"}}]},{"text":".","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Download & Install SQL Workbench","type":"text"}]},{"type":"paragraph","content":[{"text":"Download ","type":"text"},{"text":"SQL Workbench/J ","type":"text","marks":[{"type":"strong"}]},{"text":"here: ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://www.sql-workbench.eu/downloads.html"}},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"Select the generic installation package for all systems with optional libraries. We recommend saving the package on your local machine with an informative name that is easy to access.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]},{"text":" The generic package contains the jar file, the manual (HTML and PDF), shell scripts for Linux/Unix-based systems (including MacOS) to start the application as well as a Windows® ","type":"text"},{"text":"launcher","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://www.sql-workbench.eu/manual/install.html#launcher-using"}}]},{"text":" and sample ","type":"text"},{"text":"XSLT","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://www.sql-workbench.eu/xslt.html"}}]},{"text":" scripts.","type":"text"}]}]},{"type":"mediaSingle","attrs":{"layout":"align-start"},"content":[{"type":"media","attrs":{"width":678,"id":"fbd7abe3-1a9d-475b-ad2f-3c080a8c51c6","collection":"contentId-1628864764","type":"file","height":226}},{"type":"caption","content":[{"text":"Download Link for ","type":"text"},{"text":"Generic Package with Optional Libraries","type":"text","marks":[{"type":"em"}]}]}]},{"type":"paragraph","content":[{"text":"Unzip the downloaded folder and select the ","type":"text"},{"text":"sqlworkbench.jar","type":"text","marks":[{"type":"strong"}]},{"text":" (JAR) file inside to open SQL Workbench. ","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"align-start"},"content":[{"type":"media","attrs":{"width":619,"id":"6b899395-4d83-4faa-bf9e-74f70f985246","collection":"contentId-1628864764","type":"file","height":231}},{"type":"caption","content":[{"text":"SQL Workbench Jar File being Selected","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"IMPORTANT","type":"text","marks":[{"type":"strong"}]},{"text":": Follow these instructions to generate an SQL Workbench executable file:","type":"text"},{"type":"hardBreak"},{"type":"inlineCard","attrs":{"url":"https://www.sql-workbench.eu/macos-binary.html"}},{"text":".","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"More details about installing and configuring the application can be found ","type":"text"},{"text":"in the SQL Workbench manual","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://www.sql-workbench.eu/manual/install.html"}}]},{"text":".","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"IMPORTANT:","type":"text","marks":[{"type":"strong"}]},{"text":" It is necessary to configure the necessary JDBC driver(s) for your database before you can connect to a database. Please refer to the chapter ","type":"text"},{"text":"JDBC Drivers","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.sql-workbench.eu/manual/jdbc-setup.html"}}]},{"text":" in the SQL Workbench manual for details on how to make the JDBC driver available to SQL Workbench/J.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"The application is now installed and the ","type":"text"},{"text":"Select Connection Profile ","type":"text","marks":[{"type":"em"}]},{"text":"screen appears.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":40.0},"content":[{"type":"media","attrs":{"width":704,"id":"b9e004c1-16d8-462f-9921-14599afb3367","collection":"contentId-1628864764","type":"file","height":449}},{"type":"caption","content":[{"text":"Select Connection Profile Screen","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Download Redshift Drivers and Test Connection","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Download the Amazon Redshift JDBC driver version 2.0 to enable SQL Workbench to communicate with Redshift:","type":"text"}]}]}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"JDBC 4.2–compatible driver (without the AWS SDK) and driver dependent libraries for AWS SDK files version 2.0","type":"text","marks":[{"type":"link","attrs":{"href":"https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/2.0.0.4/redshift-jdbc42-2.0.0.4.zip"}}]}]},{"type":"orderedList","attrs":{"order":2},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Unzip the Redshift drivers to a directory you choose on your machine","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Open SQL Workbench. click the ","type":"text"},{"text":"Manage Drivers","type":"text","marks":[{"type":"strong"}]},{"text":" button.","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":182,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":182,"alt":"Manage Drivers button on the GUI","id":"7f2cc307-6d43-476f-870d-9f2b244f9545","collection":"contentId-1628864764","type":"file","height":62}}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":" Click the New Entry button, then select the New Driver entry.","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":344,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":344,"alt":"newdriver.png","id":"e15c2f27-9a89-41e3-ba21-c68dbe999bac","collection":"contentId-1628864764","type":"file","height":326}}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Rename the new driver “Amazon Redshift”.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Click the folder icon and navigate to where you unzipped the Redshift drivers. Select the redshift driver and click ","type":"text"},{"text":"Open","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":543,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":543,"alt":"selectredshift.png","id":"93853d52-2cc8-4e84-8817-550123ae9689","collection":"contentId-1628864764","type":"file","height":445}}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Click ","type":"text"},{"text":"OK","type":"text","marks":[{"type":"strong"}]},{"text":". In the Driver dropdown, select Amazon Redshift.","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":587,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":587,"alt":"selectdriver.png","id":"96ad07b0-9c06-43c5-9ca3-42f02e4e6cc6","collection":"contentId-1628864764","type":"file","height":273}}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Configure the Database Connection String","type":"text"}]},{"type":"paragraph","content":[{"text":"Configure the","type":"text"},{"text":" Connection Profile","type":"text","marks":[{"type":"em"}]},{"text":" screen with your account credentials (username and password) and the connection string.","type":"text"}]},{"type":"paragraph","content":[{"text":"With the drive selected, a sample endpoint will autofill in the URL field. This endpoint needs to be modified, and several other fields need to be completed. See the example below.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":25.0},"content":[{"type":"media","attrs":{"width":872,"id":"1e7e306f-ec7b-4f06-983c-a4e2cc803a19","collection":"contentId-1628864764","type":"file","height":510}},{"type":"caption","content":[{"text":"Example of New ","type":"text"},{"text":"Select Connection Profile","type":"text","marks":[{"type":"em"}]},{"text":" Screen","type":"text"}]}]},{"type":"paragraph","content":[{"text":"The essential fields to configure are as follows:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name","type":"text","marks":[{"type":"strong"}]},{"text":": The name for the connector that you will reference later to access this database again. This is for your use only, so make it 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, a name such as `DWH-COCI` might be appropriate.)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"URL","type":"text","marks":[{"type":"strong"}]},{"text":": Where to reach the database. Edit the auto-filled string in this field with the custom endpoint (example shown below).","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"aa3ccdc2-a81f-4cbc-90fd-ad6d2f6fa584"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"background":"#f4f5f7","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"Environment","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"background":"#f4f5f7","rowspan":1,"colwidth":[140.0]},"content":[{"type":"paragraph","content":[{"text":"Endpoint","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"Production","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[140.0]},"content":[{"type":"paragraph","content":[{"text":"For the URL to the Production environment, please contact your College Experience Manager or email ","type":"text"},{"text":"cems@ccctechcenter.org","type":"text","marks":[{"type":"link","attrs":{"href":"mailto:cems@ccctechcenter.org"}}]},{"text":".","type":"text"}]}]}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Username","type":"text","marks":[{"type":"strong"}]},{"text":": The PostgreSQL username you are using to login.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Password","type":"text","marks":[{"type":"strong"}]},{"text":": The password for the username above. ","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Place a checkmark in the ","type":"text"},{"text":"Autocommit","type":"text","marks":[{"type":"em"}]},{"text":" box, as shown in the example below.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":40.0},"content":[{"type":"media","attrs":{"width":493,"id":"d64a1d70-dee8-46f7-8270-2fcbeba386b4","collection":"contentId-1628864764","type":"file","height":269}}]},{"type":"paragraph","content":[{"text":"Click ","type":"text"},{"text":"Test ","type":"text","marks":[{"type":"strong"}]},{"text":"in the lower right to verify you have connected successfully. Then click ","type":"text"},{"text":"OK","type":"text","marks":[{"type":"em"}]},{"text":".","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":40.0},"content":[{"type":"media","attrs":{"width":491,"id":"80b7d2ff-d6f1-4251-b7e6-cf8ea8838d3c","collection":"contentId-1628864764","type":"file","height":289}},{"type":"caption","content":[{"text":"Example of Configuring the Driver Connection","type":"text"}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Table B: Application Database Names and Schema Formats","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"The value of ","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]},{"text":" can be derived from the prefix of your Data Warehouse Direct user name which is in the format of ","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]},{"text":"_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]},{"text":" i.e. ","type":"text"},{"text":"000_jdoe","type":"text","marks":[{"type":"strong"}]},{"text":", where 000 represents the MIS scope value. An example of the resulting schema for accessing tables within the Application database = dw_apply_read_000.","type":"text"}]}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"52757b6d-5baa-40e2-946b-e9753e047a10"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Application (Data Source)","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"Database Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"Query Schema Format","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Canvas (Canvas Data 2)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"canvas2","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_canvas2_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"CCCApply Application","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"application","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_apply_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"CC College Promise Grant","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"bogfw_application","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_apply_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"CCC International Application","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"intl_application","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_apply_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Course Identification Numbering System (C-ID)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"cid","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_cid_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Chancellor's Office Curriculum Inventory (COCI)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"coci","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_coci_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Chancellor’s Office - Management Information Systems (MIS)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"mis","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_mis_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]},{"text":" ","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Multiple Measures Placement Service (MMPS)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"mmps","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"dw_apply_read_","type":"text"},{"text":"","type":"text","marks":[{"type":"textColor","attrs":{"color":"#bf2600"}},{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[788.0]},"content":[{"type":"paragraph","content":[{"text":"Student Journey Data","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[269.0]},"content":[{"type":"paragraph","content":[{"text":"student_journey","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[735.0]},"content":[{"type":"paragraph","content":[{"text":"public","type":"text"}]}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note: ","type":"text","marks":[{"type":"strong"}]},{"text":"Data dictionaries for the above data sources are available on the ","type":"text"},{"text":"CCC Data Warehouse documentation site","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1477378202/CCC%2BData%2BSources%2BData%2BDictionaries"}}]},{"text":". ","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"IMPORTANT:","type":"text","marks":[{"type":"strong"}]},{"text":" For troubleshooting if the connection test fails, the two most common fixes are as follows:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Verify you have entered the URL, username, and password correctly.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Ensure you are connected to the college’s VPN.","type":"text"}]}]}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Data Access Validation & Testing","type":"text"}]},{"type":"paragraph","content":[{"text":"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:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Test Query Data Sources","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"Through local resources, the participant is able to connect to, and run at least one query against each of the available datasets (CCCApply application, CCCApply international application, CCCApply Promise Grant, and Multiple Measures Placement Service).","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect Data to Local Data Warehouse ","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"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.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]},{"text":" CCCTC ","type":"text"},{"text":"College Staff Support","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/CSUP/pages/1539768496"}}]},{"text":" is available to assist with issues connecting to the CCC Data Warehouse. ","type":"text"}]}]},{"type":"paragraph","content":[{"text":"ES will work with colleges to validate initial data queries using methods described below.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Using provided user documentation (such as this user guide), the college configures their BI tool to connect to the Data Warehouse. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The college confirms local connection and successful login, and also performs optional data validation activities.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Online documentation is available to support data access validation activities for researchers.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Work with your ES ICE (engineer) to confirm access validation and query tests. Discuss any unfinished implementation steps before hand-off to ES Support.","type":"text"}]}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Canvas Data 2 with DW Direct Connect Service","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"To get started, send an email requesting Canvas Data 2 Data Warehouse Direct Connect access for your college to your College Experience Manager (CEM) at CCCTC Enabling Services. ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Important: ","type":"text","marks":[{"type":"strong"}]},{"text":"Colleges must be 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 ","type":"text"},{"text":"Enabling Services","type":"text","marks":[{"type":"em"},{"type":"link","attrs":{"href":"mailto:cems@ccctechcener.org"}}]},{"text":" to get started. ","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"For more information about the ","type":"text"},{"text":"Canvas Data 2 Direct Connect service","type":"text","marks":[{"type":"em"}]},{"text":", including the college preparatory requirements before and during the implementation process, refer to the following link: ","type":"text"},{"text":"Accessing Canvas Data 2 in the Data Warehouse","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/ES/pages/3172564993"}}]},{"text":".","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"For information about the latest Canvas Data 2 updates to the CCC Data Warehouse, refer to the following link: ","type":"text"},{"text":"Canvas Data 2 Changes to Data Warehouse","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/x/AwBpx"}}]},{"text":".","type":"text"}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Access In-Progress Application Data in the CCC Data Warehouse","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"See related content in ","type":"text"},{"text":"Accessing In-Progress Application Data in the CCC Data Warehouse","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/3502964795"}}]},{"text":", featuring tables and query information.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"Making Queries","type":"text"}]},{"type":"paragraph","content":[{"text":"After the database connection is validated, a prompt will appear to allow you to enter SQL Queries.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":65.0},"content":[{"type":"media","attrs":{"width":659,"id":"8be0b3d5-6663-4c5a-8c70-356092e23a1e","collection":"contentId-1628864764","type":"file","height":172}}]},{"type":"paragraph","content":[{"text":"A tab labeled ","type":"text"},{"text":"Database Explorer","type":"text","marks":[{"type":"em"}]},{"text":" displays more information about the database that your user has access to. Table names can be found there, as well as some additional basic information about the DB.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start"},"content":[{"type":"media","attrs":{"width":601,"alt":"Screenshot showing the Database Explorer tab.","id":"f1e5d59d-23ff-4d72-864b-63db527e65f7","collection":"contentId-1628864764","type":"file","height":430}},{"type":"caption","content":[{"text":"Database Explorer Tab Provides More DB Information","type":"text"}]}]},{"type":"paragraph","content":[{"text":"You can also run SQL against objects your DB user has access to.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":30.0},"content":[{"type":"media","attrs":{"width":347,"alt":"Screenshot showing an example SQL script in the Statement tab.","id":"44628ffa-5455-4503-8647-c9f580bb5a86","collection":"contentId-1628864764","type":"file","height":232}},{"type":"caption","content":[{"text":"SQL Allowed by Your User Can be Run in the Statement Tab","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Example Query to run on Data Warehouse Data","type":"text"}]},{"type":"paragraph","content":[{"text":"To get a row count of a table, run the following query:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"select count(*) from dw_apply_read_523.contact;","type":"text"}]},{"type":"paragraph","content":[{"text":"You will need to replace the ","type":"text"},{"text":"dw_apply_read_523","type":"text","marks":[{"type":"code"}]},{"text":" with the appropriate schema your user has access to. You can also use the database explorer in SQL workbench to navigate the data.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"CCC Data Warehouse Direct Access FAQs","type":"text"}]},{"type":"paragraph","content":[{"text":"Q: Can I have more than one VPN connection for my district?","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"A. ","type":"text","marks":[{"type":"em"}]},{"text":"Not at this time. If this is needed, please discuss with your CEM.","type":"text"}]},{"type":"paragraph","content":[{"text":"Q: ","type":"text","marks":[{"type":"strong"}]},{"text":"My IT department does not want to create a site-to-site VPN, can I just have an individual VPN connection? ","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"A. No, the CCC Tech Center is not staffed to handle individual user VPN requests.","type":"text"}]},{"type":"paragraph","content":[{"text":"Q: Can I have more than one individual at the college (or district office) connect to the VPN at a time?","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"More Information: ","type":"text","marks":[{"type":"strong"}]},{"text":"See ","type":"text"},{"text":"CCC Data Warehouse - Frequently Asked Questions","type":"text","marks":[{"type":"link","attrs":{"href":"https://cccnext.jira.com/wiki/spaces/DWPub/pages/1597210705"}}]},{"text":" for more frequently asked questions.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-note","panelIcon":":note:","panelColor":"#F4F5F7","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"To get help and share information, access the ","type":"text"},{"text":"CCCTechnology.info","type":"text","marks":[{"type":"link","attrs":{"href":"http://CCCTechnology.info"}}]},{"text":" support channel. In that forum, you can get help from the ES Support team. and discuss use of the CCC Data Warehouse with other colleges and districts.","type":"text"}]}]},{"type":"paragraph"}],"version":1}

Browser not supported