SS&E Accelerate (formerly known as Connect) is an integration utility that allows an institution to define how data should be extracted from local systems (especially an SIS) to then be transmitted and ingested into SS&E.
The utility itself runs on a server on the institution's local network where it has access to the source data. It is configured from the SS&E administrative screens. Here we will discuss this configuration.
Accelerator Format
SS&E Accelerate configurations are known as accelerators. They are XML documents that describe how each data feed should extract from the source system in a format (model) that SS&E is able to consume.
An accelerator includes the following components:
Connector
The root level element that encapsulates the entire accelerator. The Connector element may optionally include a parent attribute that the accelerator should inherit from. See "The Parent-Child Relationship" below for more information on this.
Example
parent="XXXXX"
isDefault="true"/
Connections
In order for Accelerate to read from the database, authenticated connections must be established. One or more connections can be established in the Connections block and used by data feed queries. Exactly one of these connections may be marked as the default connection, in which case it will be used by any data feed queries that do not explicitly include a connection attribute.
Connection
- Attributes
- isDefault - set to "true" if this connection should be used by default.
- Children
- Name - the name of the connection that can be used by data feed queries to refer to this connection.
- Database - the type of database that this connection is used for. This determines the appropriate driver for connecting to the database. Valid options are sqlserver, csv, postgres and oracle.
- Credentials
- Username - The username that should be used to connect to the source database
- Password - The password that should be used to connect to the source database
- Connection String - The jdbc connection string (without credentials) that should be used to connect to the source database
Example
isDefault="true"
SIS
oracle
jdbc:oracle:thin:@url:1521:db
FinAid
sqlserver
jdbc:sqlserver:@url:port:db
GlobalDefinitions
Queries may often use common variables in their definitions. These may include lists of values (eg. high school codes), SQL snippets or any other string value. Such constants can be defined in the GlobalDefinitions block and referenced by any data feed query. To create a constant, create a StaticDefinition element inside of the GlobalDefinitions block. Leverage these variables consistently to ensure that definitions can be controlled from a single point.
StaticDefinition
- Attributes
- bindingOrder - In order for variables to be properly set inside of a string, they must first be defined. If your static definition references other variables, those variables must first be parsed. If your static definition does not reference other variables, bindingOrder should be set to 0. If your static definition does reference other variables, bindingOrder should be set to the bindingOrder of the referenced variable + 1. If your static definition references multiple other variables, the bindingOrder should be set to the highest binding order of the referenced definitions + 1.
- Children
- Name - The reference-able name of the variable. This is the name that other nodes will use to interpolate or otherwise reference the variable.
- Type - The type of variable. Currently only string is supported.
- Value - The value of the variable. This is the value that will be interpolated when the variable is referenced.
bindingOrder="0"
institutionName
String
ParentDemoColleague
bindingOrder="0"
personFafsaInstitutionIdentifier
String
5449
StreamDefinitions
Stream definitions are where the actual extracts are defined for each data feed. A Stream child should exist for each of the implemented data feeds and the name of the stream will be used to determine which data feed it is serving. For example, a Stream element with name = "Person" will be executed in order to generate the extract for the Person data feed. That extract will be transmitted to SS&E and loaded into the relevant Person tables of SS&E.
Define a Stream for each of the data feeds that you want to support.
Stream
- Attributes
- name - The name of the datafeed that you want to support. This will be used to determine which datafeed the Stream is for as well as allow for references to queries within this stream from other streams (See reference queries).
- Children
- StaticDefinitions - Static definitions defined inside of a stream are variables just like the ones defined in GlobalDefinitions, however they are scoped to the stream for which they are defined. They are unavailable to other streams. If you need to share a variable among multiple streams, define it in GlobalDefinitions instead. The format for Stream scoped StaticDefinitions is the same as those for GlobalDefinitions.
- SetupQueries - Setup queries are used to define any SQL that should execute on the source database in preparation for the "main" query(ies) to run. These queries usually create temp tables but may be used to perform any SQL operation. The output of these queries is discarded, so they are typically used to alter the state of the database or the database session (eg. creation of temp tables). There are two types of SetupQueries, "TempTable" used to create temporary tables and "ReferenceQueries" which execute SetupQueries that are defined in other streams.
- Attributes
- type - TempTable or Reference
- connection - The Connection that should be used to execute the query
- name - The name of the SetupQuery. In a Reference type query, this is the name of the referenced queries. The query will be retrieved by name from the referenced stream (see the stream attribute). For TempTable type queries, this name is used by other streams to reference the setup query.
- stream - Reference queries only. The stream that contains the referenced SetupQuery.
- bindingOrder - The order in which this query should bind. Except for advanced cases, this should be set to 0.
- executionOrder - The order in which this query should execute in relation to all of the other SetupQueries. If the order does not matter this can be set to 0. If the query depends on other SetupQueries, set it to the dependent queries executionOrder + 1.
- Children
- Value - The SQL string that should execute. This may include variable references. Only applicable for TempTable type SetupQueries.
- Attributes
- Queries - The queries that exist in the Queries block are the actual data queries that should execute. All of the Query children will execute and the resulting rows will be unioned together to produce the extract file.
- Attributes
- connection - The Connection that should be used to execute the query
- name - A friendly name of the query
- Children
- Value - The SQL that will execute. This query should return a rowset whose column names, types and values match the SS&E connector model exactly.
- Attributes
- AllIdsQueries - When data is deleted out of the source data, this deletion may need to be synchronized with SS&E. In order to achieve this, an "all ids" process is available for certain streams (see Data Feed Actions for the streams that support deletion). In order to leverage the deletion synchronization, an AllIds query should be introduced that returns the ids of all of the records that should be retained in SS&E . The All Ids query is executed, the list of IDs is sent to SS&E and any records that exist in SS&E but do not exist in the list of IDs will be either deleted or marked as deleted (soft deleted). This block should consist of Query blocks that follow the same format as the Queries block.
Examples
name="CourseSection"
bindingOrder="0"
onlineDeliveryMethods
String
'IN'
bindingOrder="0"
distanceDeliveryMethods
String
'IN'
bindingOrder="0"
activeStatuses
String
'A'
bindingOrder="0"
cancelledStatuses
String
'C'
type="TempTable"
connection="SIS"
name="courseSectionIds"
bindingOrder="0"
executionOrder="0"
CREATE TEMPORARY TABLE courseSectionIds as
select
COLUMNS[0] courseSectionId
from dfs.root.`course_section.csv`
type="Reference"
connection="SIS"
stream="TranscriptCourse"
name="transferCourseSections"
bindingOrder="0"
executionOrder="0"/
type="Reference"
connection="SIS"
stream="TranscriptCourse"
name="otherCourseSections"
bindingOrder="0"
executionOrder="0"/
connection="SIS" name="mainQuery"
select
COLUMNS[0] as id,
COLUMNS[1] as section,
cast(COLUMNS[2] as date) as startDate,
cast(COLUMNS[3] as date) as endDate,
COLUMNS[4] as courseId,
COLUMNS[5] as academicCalendarId,
COLUMNS[6] as instructorPersonId,
0 as selfPaced,
COLUMNS[7] as locationId,
case
when COLUMNS[8] in ({{{onlineDeliveryMethods}}})
then 1
else 0
end as online,
case
when COLUMNS[8] in ({{{distanceDeliveryMethods}}})
then 1
else 0
end as distance,
case
when COLUMNS[9] in ({{{activeStatuses}}})
then 'ACTIVE'
when COLUMNS[9] in ({{{cancelledStatuses}}})
then 'CANCELLED'
else 'ACTIVE'
end as status
from dfs.root.`course_section.csv`
union
select
id,
section,
startDate,
endDate,
courseId,
academicCalendarId,
instructorPersonId,
selfPaced,
locationId,
online,
distance,
status
from transferCourseSections
union
select
id,
section,
startDate,
endDate,
courseId,
academicCalendarId,
instructorPersonId,
selfPaced,
locationId,
online,
distance,
status
from otherCourseSections
connection="SIS" name="allIds"
select COLUMNS[0] as id from dfs.root.`course_section_ids.csv`
union
select id from transferCourseSections
union
select id from otherCourseSections
Using Variables
Variables may be interpolated into queries or other variables using the curly brace syntax. Use three curly braces to reference a variable and the content of that variable will be automatically inserted into the text. For example, given a static definition named "name" with a value of "world", the following would result in a value of Hello World.
bindingOrder="0"
name
String
'World'
bindingOrder="0"
HelloVariable
String
Hello {{{name}}}
Variables can be used in any Value element.
The Parent-Child relationship
An accelerator can define a parent accelerator as an attribute of the Connector element. This allows for the accelerator to inherit all global definitions and streams from the parent. It can then add new elements or override these inherited elements by name in order to extend or modify the behavior of the parent accelerator. For example, the Person stream may define a variable called "studentRoleName". All other behaviors of the Person stream may be applicable (eg. the query), but the studentRoleName needs to be replaced. The child accelerator can define the Person stream and override the desired variable. All of the other variables and queries defined in the parent for the Person stream would still exist and be used, but the studentRoleName would be evaluated from the child accelerator anywhere that it is interpolated.
<-- the="The" child="child" accelerator="accelerator" --="--">
...
</-->name="Person"
bindingOrder="0"
studentRoleName
String
Stdnt