Thursday, November 29, 2012

SITS StuTalk

SITS is a student record system. It contains details about students, such as the course they are on, grades, etc. We were using version 8.5.0 at the time of writing.

StuTalk is a web service interface to SITS. You can do a handful of things with this – the two main ones, for me, being to import and export data via XML Exchange Templates (XET).

The XET screen in SITS lets you create and edit XET templates that map the SITS data model to and from an XML file. You can use File > Import and File > Export from the main menu in SITS Vision (the desktop client) to manually import and export SITS data in XML format through the XET templates.

These even work for things like TUPs (SITS triggers) so I can export those from development, put them in source control and then import them in to live during a release.

It can also handle nested data and the XET screen knows about many of the relationships between entities so you can just add the entities and let it sort out the details most of the time.

A big plus about XETs is that they will trigger the SITS customisations (that is, your business logic) that you might have configured, such as TUPs. This obviously isn’t the case if you attempted to import the data directly into the underlying SITS database tables.

On the other hand this is slow. Attempting to export ~9k rows of data with a handful of entities took hours. After discussion with the vendor we learned that direct table access is still the recommended approach for pulling large amounts of data out in one go.

However, a more event-driven approach is possible which removes the need for a big bulk export. We use TUPs to raise events (such as a student enrolling to a course) and place these events onto a “service bus” (MSMQ message queues). Some custom code then processes these events, in some cases making a XET export request via StuTalk to fetch additional data out and then pass the resulting dataset on to another system, such as our VLE (Moodle) or maybe to more custom code to have an email account created or whatever.

A second “gotcha” is that XETs cannot import child entities if the parent is using auto-generated primary key parts. The example that bit us was with IPRs and IPRQs. We use these to import things like prospectus requests made over the phone or through our website. The TRAN process in SITS lets us match this new enquiry up with others so we don’t double-count our enquirers (they may have contacted us before). The IPR represents the enquiry and IPRQs are a way of defining additional custom data.

The XET template is defined such that it auto-generates a sequence number for the IPR. The child IPRQs need this sequence number as a foreign key so they are related to their parent IPR, but the XET import does not do this for us. The SITS consultants and helpdesk people first tried to tell us that this “just worked”, but this turned out to be because their training systems had IPR and IPRQ sequence numbers that happened to be in sync, so generated matching numbers by accident. Doesn’t work for us as our sequences are not in sync as we have real data not a static training set. We were then told we could achieve it by using some “registers” which are special data slots we can use during the import, but when we tried it we couldn’t get it to work. The SITS Helpdesk people then admitted the product couldn’t do this and closed the call.

To work around this deficiency of SITS we have a two-stage import. The first stage imports the IPR (and we include a key value from the source data in a user defined field of the IPR). A TUP fires and writes an event out to a table. This event includes the auto-generated sequence number that was given to the IPR by SITS during the import as well as the other key parts and the source key we imported (see above). Using the source key we can get back to the source data and pick up our additional data to import into IPRQs via StuTalk, using the auto-generated sequence number from the IPR to relate the IPRQs correctly.

1 comment:

Anonymous said...

Nice post...
Was thinking about using StuTalk to replace the mundane screens with Html pages that were more user friendly (similar to what eVision does, but in .Net). So the idea was to use StuTalk to get data from SITS and then present it in a way that is more intuitive for the end user. Part of the process was to make updates... was hoping Tribal had an answer, but I guess we're stuck with multiple-pass updates!
Qamar