Moodle/SIMS integration

[localzuk]

Has anyone managed to integrate SIMS.net with Moodle in such a way that users are imported, and the courses they are on?

 

I know the CLEO/LUNS team have managed it and are waiting on the powers that be to do some rubber stamping before they can release the code, but I would like to be able to integrate the 2 asap as an example for my boss.

 

So, if you have done it, any chance you can provide a howto?

[apeo]

I'd be interested to know if anyone has done this.

[CyberNerd]

yes, SIMS then use the moodle MSSQL database module. I didn't do it so I can't provide much of a howto There is a view set up in the database called dbo.mdl_enrolments and moodle queries this view using its own MSSQL database module. After a user logs in (via AD auth) their courses are only populated after the nightly cron is run to import the courses.

 

I edited this because what I originally said was inaccurate. I've posted a screeny of the MSSQL view further down the thread.

[webman]

Doesn't connecting to the MSSQL SIMS DB violate your SIMS licence?

[localzuk]

Doesn't connecting to the MSSQL SIMS DB violate your SIMS licence?

 

Those were my thoughts too...

[CyberNerd]

not sure. We did this with CMIS and when we migrated we were given assurances by LA and capita that it was OK to do it now (said my boss). It's only talking to a 'view' of the database so this might be a get out clause ?

 

apparently teh LA have a dev license - since we are part of LA we use that ??

[Geoff]

Our RBC has internally developed a tool to do this. I'm unsure of its public release status.

[webman]

I thought CMIS wasn't by Capita? I am lead to believe that with CMIS you can connect to SQL directly anyway without any come-back.

 

I woud like to refer to a post by PhilNeal in a previous thread:

 

Our stance on this is that tapping directly into SIMS tables is that it invalidates our warranty. Basically if you hack into tables and you mess things up we will charge for putting it right.

 

We provide the SDK which many people are happy with and we also support the use of our business objects. Your support unit would need to be involved if you take the BO route as they would need to support you.

[dhicks]

> Has anyone managed to integrate SIMS.net with Moodle in such a way

> that users are imported, and the courses they are on?

 

I'm working on a solution at the moment based on parsing the XML data produced by the CommandReporter tool. I've written a Perl wrapper around this tool that presents a set of functions callable via HTTP (simple REST protocol, no SOAP or whatever). I've currently got a client application running on a another server (our LDAP server) that calls the SIMS server to get a list of current users, then synchs that list with the LDAP server. I figured that Moodle could then use the LDAP server to authenticate against.

 

There's no security implemented yet, I figured I'd add some public/private key encryption functions rather than mess around with HTTPS. I was meant to be getting this finished today, but other stuff came up.

 

--

David Hicks

[CyberNerd]

I thought CMIS wasn't by Capita? I am lead to believe that with CMIS you can connect to SQL directly anyway without any come-back.

 

I woud like to refer to a post by PhilNeal in a previous thread:

 

Our stance on this is that tapping directly into SIMS tables is that it invalidates our warranty. Basically if you hack into tables and you mess things up we will charge for putting it right.

 

We provide the SDK which many people are happy with and we also support the use of our business objects. Your support unit would need to be involved if you take the BO route as they would need to support you.

 

no CMIS isn't capita. We migrated from SIMS to CMIS then back to SIMS (won't do into reasons - it gets me cross). The LA were very keen to see us migrate back and said the database connect thing was ok since they have a dev license. we don't write anything back to the MSSQL and we only lquery a view of it (I really am a SQL noob so please point out if what I say doesnt make sense ). If anyone wants me to get any more info to do a project let me know.

[webman]

I see... I mis-read your post with it having CMIS and Capita in the same sentence

 

I'm still not 100% sure if querying views are OK as far as Capita is concerned. I suspect not, but am willing to stand corrected on that.

 

I remember a while ago several of us (including dhicks) were discussing the very thing dhicks is currently working on; that is developing ways of getting data from the (albeit poor) method of data extraction at our disposal - the "SDK".

[sahmeepee]

I'm surprised you'd need to add fields to SIMS - surely the data is coming OUT of SIMS so you could create the fields in a table elsewhere?

 

Is it possible those fields are just the fields exposed by the view they've created and so only a view has been added to the SIMS db?

 

When I google I get this thread back and eventually disappear into my own belly button

[CyberNerd]

I think it was capita themselves that migrated our CMIS -> SIMS and they migrated the user defined fields themselves. I may be wrong because I've wisely kept away from CMIS and SIMS my entire career!

[CyberNerd]

Is it possible those fields are just the fields exposed by the view they've created and so only a view has been added to the SIMS db?

I don't think so, I can check if you tell me what to do (again, I'm no MSSQL expert)

I added a screenshot of moodle db setup

[sahmeepee]

I'm a bit wary of giving you instructions as I generally don't go near my own SIMS db without saying a few prayers.

 

Basically you are looking to open the database in SQL Server management studio, expand the sims database, expand views, find the view you mentioned and then right click to edit it. That shows you how the view is built, but unpicking which fields are built in and which are added would be fiddly given that SIMS has well over 1000 tables in the database.

 

I'm happy to remain curious considering the safety of your MIS is at stake...

[CyberNerd]

I don't think the Sims manager will mind, I'm sure he has a backup somewhere.

 

 

USE [sims]

GO
/****** Object:  View [dbo].[mdl_enrolments]    Script Date: 12/05/2007 17:16:34 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[mdl_enrolments]
AS
SELECT DISTINCT class_name AS course, staff_initials AS person, 'editingteacher' AS role
FROM         sims.curr_via_classperiods AS p
UNION
SELECT DISTINCT p.class_name AS course, m.unique_pupil_no AS person, 'student' AS role
FROM         sims.curr_via_classperiods AS p INNER JOIN
                     sims.stud_via_group_members AS m ON m.base_group_id = p.base_group_id

[sahmeepee]

OK, so no extra fields required.. that query will run against any SIMS db and return the teacher initials for every class code and the pupil's UPN for every pupil in every class.

 

It's very similar to the data I used when I tried to set up MS Classserver a few years back. The difficulty I had was a lack of link between the SIMS id (which can be the UPN or the admission number) and the AD id (which would probably be the username (samid) or the SID).

 

I found that although the UPN would be better to use in theory, in practice we often didn't get UPNs for our sixth formers until several months after they started. I was also wary of temporary UPNs being issued and then changed later which apparently can happen.

 

It's not a simple problem to solve!

 

EDIT: Thanks for sharing by the way

[CyberNerd]

I spoke to our Sims manager this evening as I'm obviously getting quite confused as to how we have this set up. He confirmed that the user defined fields that we have are nothing to do with moodle integration and it is just the view that was set up. It's all starting to fall into place (i think), the user defined fields that our sims has were migrated over because we used to automatically create active directory accounts from CMIS - It was the autocreation script that needed the extra field. my mistake.

Anyone fancy trying this out and writing a howto ?

[CyberNerd]

I found that although the UPN would be better to use in theory, in practice we often didn't get UPNs for our sixth formers until several months after they started. I was also wary of temporary UPNs being issued and then changed later which apparently can happen.

 

We have the UPN set in the employeeID field in active directory. In the LDAP authentication of moodle under data mapping, we pull out that information "ID number = employeedID". This is why AD/MIS integration is the way forward.

[m2d2]

I remember a while ago several of us (including dhicks) were discussing the very thing dhicks is currently working on; that is developing ways of getting data from the (albeit poor) method of data extraction at our disposal - the "SDK".

 

What's wrong with the IMS. Updating VLEs is what it was created for.

[sahmeepee]

We have the UPN set in the employeeID field in active directory. In the LDAP authentication of moodle under data mapping, we pull out that information "ID number = employeedID". This is why AD/MIS integration is the way forward.

 

I haven't really delved into moodle very seriously yet, but from what I can see these issues are fairly universal with VLEs as they stand today. I'm glad to hear that people are having success with it in Moodle, despite the slightly crushing news today that after consultation with the NMs in the borough, our LA have decided to ignore our requests and base their VLE selection process on BECTArds "List Of Ten". The winner to be announced on Friday to all Heads :/

[webman]

Moodle will never win in the eyes of those QUANGOs as it's free.

[CyberNerd]

our LA have decided to ignore our requests and base their VLE selection process on BECTArds "List Of Ten". The winner to be announced on Friday to all Heads :/.

 

Seems to be the same everywhere, The LA's get funding but only if they pay companies that are on bectas favorites list. Education will ultimately suffer because such a huge amount of money is being wasted on these companies, given the robustness of the free solutions. If Becta could have just offered a financial incentive to develop moodle/mis plugins - or even a howto (like they recently paid RM to develop an opensource whiteboard format then we'd all get a better solution and the government would save money. It's hard to believe there is no conspiracy.

[Geoff]

CLEO (Lancs and Cumbria) have standardised on Moodle. Cumbria even has some full time developers working on Moodle coding/addons.