Extraction from Datatel Colleague (UniData)

Extraction

• The Estudias ETL Manager software connects to Colleague (via secure SSH or telnet), executing UniQuery paragraphs using Cedarville's free DOWNLOAD program to put the files on the UNIX system.
• The Estudias ETL Manager download tool uses Secure FTP, SCP, or FTP to the pull the files from the UNIX system down to the Estudias server.  The files can be encrypted while downloading so that the files are never unencrypted on the Estudias server.
• Files are transformed and loaded as if they came from any other source.
• Files are normalized as much as possible, reducing joins on Colleague.
• Download paragraphs are automatically generated for multi-valued fields, putting each multi-valued field into its own file (see below)

Setup

Estudias connects to Colleague (as above) and retrieves the data dictionary for all relevant files.  Estudias then knows which fields are multi-valued, the data types, etc.

Multi-valued Fields

Multi-valued fields are one of the biggest challenges to schools wanting to run reports on information in Datatel Colleague. 

Estudias automatically generates DOWNLOAD paragraphs to "explode" multi-valued fields into a relational format (one file per multi-valued field). 

For example, in downloading information from the STUDENTS file, Estudias would dynamically create multiple paragraphs: one to download the selected single-valued fields and another for each one of the selected multi-valued fields.  Estudias would then pass these paragraphs to DOWNLOAD (as above), get the text files, and process them as if they had come from any other relational source.

Below is an example of how one such "exploded" multi-valued field (STU.CUR.TYPE.DESC) might look once Estudias created the paragraph and downloaded it to its own file:

0123456, In District
0123456, Valedictorian
0123456, ...
7654321, Out of District
8765432, Valedictorian
...

Note that the the student's Colleague ID is replicated for each value in the multi-value field.  This creates a common one-to-many relational mapping.

Correlated Fields

Estudias can also handle multi-valued fields that are correlated (i.e. the multi-valued enrollment status date field is correlated with the multi-valued enrollment status description field) by putting all fields in the same file to preserve the correlation.

In either case, on download, the files are in relational format and can then be processed as usual with the transform-load architecture of the Estudias ETL Manager.

Release 18

If your school is staying with UniData in a "combined" environment, nothing should change.  If your school is moving to SQL-Server or Oracle, extraction should be much simpler as it will be possible to use an ODBC connection to generate the correct files.