[acs] CMIS Data Integration

[nathj]

Hi,

 

I'm new here and very new to CMIS, so allow me to introduce myself. I am a developer by trade who has is currently involved in helping my employer to decide upon a VLE. Currently the options are FROG, in-house or a combination of the two.

 

One part that I am currently considering is the ability to read from our MIS - CMIS. further to this I want to be able to write back to it - it's only SQL database after all.

 

My question is this; does anyone have or know of any documentation on the data structure or have any advice on writing information to the database?

 

All help will be greatly appreciated.

 

Many thanks

nathj

[CyberNerd]

We did this CMIS/moodle integration before the powers that be made us migrate to SIMS (political decision rather than technical)

 

I'm afraid I don't have any documentation, other than to say it is possible as its just an SQL database.

Edited December 3, 2008 by CyberNerd
repeating myself

[nathj]

That's encouraging and pretty much as I expected. I'm sure it's not going to cause me too much bother - once I figure out the data structure. To be honest as MySQL, PHP developer I'm not at all worried about the code I just want some tips on the data structure as I hear it's not normalised and doesn't make any logical sense but as yet I've not seen it.

 

Also, the tools that we are considering for a VLE seem to be able to read from the CMIS system but not write to them - which could result in data duplication and even poorer data integrity.

 

Thanks for the encouraging words though

nathj

[CyberNerd]

no worries,

 

My top tips would be to get CMIS and you Directory server (AD) in sync, (so that either the LDAP stores a unique identifier or so CMIS stores the username). This way most of the (read-only) integration with moodle is already done (it will automatically pull out course information etc if you create a SQL view to do it) then you just need to write some moodle blocks/modules to put (grades) back into the database.

[kylewilliamson]

Our recommendation would be to not write back to CMIS..

[nathj]

Why is that your recommendation?

 

If I have a system that enables students/parents to view performance records, grades whatever then it makes sense if the teacher can use the same system to update that information. One system for al the main tasks.

 

If it's done properly I see no reason why this would be an issue - which is why I asked my question at the start of this post. I'm new to this and looking for input, if you have reasons for this - experience etc then please let me know.

 

Many thanks

nathj

[SpuffMonkey]

I imagine that Serco will wash their hands of any support issues if you start writing to the database without their agreement. I've never found a proper data dictionary for the system, so you are only having an educated guess at what the fields are used for - not a problem for extracting data - but a big prob putting it back in.

[nathj]

Thanks for the clarification. Support issues should certainly be a consideration. Is the data structure as poor as I've read about with no DD, no normalisation and really poor data integrity?

[SpuffMonkey]

Thanks for the clarification. Support issues should certainly be a consideration. Is the data structure as poor as I've read about with no DD, no normalisation and really poor data integrity?

 

It was the last time I looked.

[gareth.davies]

I spotted this post because it has the word Frog in it, but it's a question we're often asked, so I thought I'd send a reply.

 

As a rule you should never update a third party database directly. The risk of breaking "referential integrity" is too great. You might notice that you've broken it straight away, you might notice 6 months later. Either way, you would have to be very "lucky" rather than very "skilled" not to break it. While I've never personally seen the CMIS schema, it's worth mentioning that there are all sorts of reasons why databases are not 100% normalised, typically related to performance. If you are not aware of the exact details of any system's intentionally denormalised data, caching tables, (design flaws) and so on then the data will inevitably become inconsistent.

 

It's for this reason that commercial database suppliers typically provide an API - a set of business objects, or web services that handle read and write to the database while ensuring that the referential integrity is maintained. This approach also ensures that as the database is changed by the supplier in subsequent releases that your code doesn't break further down the line.

 

We have been working with CMIS for a few years now. Initially we accessed their database directly through ODBC drivers. Serco have recently started a partner programme and are building a library of web services. I believe that they have only got as far as reading data out, but I'm sure it's only a matter of time before they provide web services to write data back as well.

 

As tempting as it may be to just "get stuck in there", I would recommend patience. I would be extremely surprised if Serco were happy to provide support to your school if they felt that the school had been responsible for "breaking the data."

 

There may well be other ways of skinning the cat though, it depends on exactly what you're trying to achieve.

 

Hope this has been helpful.

 

Gareth

[mark80]

Getting to grips with CMIS data structure is like trying to get a grip on the Popes balls!!

 

Our school is opting for Moodle/CMIS integration due to the increasing support network

[SpuffMonkey]

Getting to grips with CMIS data structure is like trying to get a grip on the Popes balls!!

 

Given what (supposedly) happens at the Popes inaugeration - not sure whether you mean its easy or difficult!

[Tamarside]

I imagine that Serco will wash their hands of any support issues if you start writing to the database without their agreement. I've never found a proper data dictionary for the system, so you are only having an educated guess at what the fields are used for - not a problem for extracting data - but a big prob putting it back in.

 

The moment you start writing data back to the DB you would have broken the Serco license agreement, allowing them to completely cut off all support.

The ONLY time that won't happen is if you are registered as a partner of Serco's and that is not a cheap option.

 

Strictly speaking if you only read data from the DB you're also breaking the license, but this is less severe as you don't run the risk of writing back to the DB and breaking it.

 

I've been working with the CMIS DB for a number of years now and like others here I've never come across a data map. Often I think even Serco doesn't have one, judging by how they sometimes botch their upgrades.

 

Why do you need to write back to the CMIS DB anyway? You can have custom fields in AD that you can write to at will. Provided you have a link between AD accounts and CMIS (the admission number springs to mind!) you can have data straddle both systems. This should take care of practically everything you need from a SysAdmin's point of view.

 

For any other data that needs writing back specifically to the CMIS DB, have your staff members use ePortal or CMIS itself.

[SchoolITLackey]

CMIS data structures are pretty tricky to understand without a data dictionary.

And I echo what everyone else is saying, once you start writing (or deleting / editing!) data directly within the CMIS database, you will negate the support agreement and CMIS will be quite within their rights to withdraw support.

[AdrianB]

Hi all,

 

To confirm the presumptions regarding bespoke reading/writing from the Facility database, the only officially supported methods are via API, SIF or some of our partners who may use proprietary methods - but we encouraging everyone to move over to the above.

 

As Gareth from Frog (also one of our partners) has already stated, we are working with our partners to continue to evolve the API and write back methods have started to appear - these being around attendance and some limited student details. No doubt we'll add more over time.

 

In terms of a data structure/map, we don't make that public for a number of obvious reasons (commercial/IP, etc). However, if anyone would like to find out more or talk to me about using the API or integration in general just drop me a PM.

 

Thanks

Adrian

[SpuffMonkey]

In terms of a data structure/map, we don't make that public for a number of obvious reasons (commercial/IP, etc). However, if anyone would like to find out more or talk to me about using the API or integration in general just drop me a PM.

 

Thanks

Adrian

 

That's not a very convincing argument - setting up security and producing reports within CMIS are made much more complicated by not having proper documentation on what data is held where and why. The structure isn't a secret - we can just have a look at it - and security through obfuscation is no security at all - just an irritation to your paying customers...imho...

[AdrianB]

That's not a very convincing argument - setting up security and producing reports within CMIS are made much more complicated by not having proper documentation on what data is held where and why. The structure isn't a secret - we can just have a look at it - and security through obfuscation is no security at all - just an irritation to your paying customers...imho...

 

Indeed you can and people do - and your quite right it isn't made any more secure by simply not having the matching documentation - but that's not the reason why it isn't made public. The bottom line is we do not support direct database access for all the reasons discussed above and making such documentation available would mean enforcing that policy very difficult.

 

However, this is why we have looked to more intelligent and robust methods such as APIs and SIF so people do not have to figure out database structures or worry about data integrity when it comes to integration.

[gaz350]

which is also not public!

[NotSoFast]

We're currently trying to plan the migration of our assessment (from Excel - individual departments) and reporting (from Access) to Facility, partly to facilitate Online Reporting. We want to do away with annual reports completely and record milestone assessments with comments which build gradually over time, in line with guidance from on high.

 

We're currently a bit frustrated that what we're being asked to do by the government is not (it seems) possible with our MIS, which is designed to meet the requirements of regular reporting of grades/levels and annual reporting of comments.

 

Our data manager has been trying to create various templates in Eportal to make this work, but the end result has two problems: all subjects must record an assessment at the same time, which is inappropriate given different amounts of curriculum time; secondly, the interface you end up with once comments are added is hard to navigate for staff and parents. Our teachers are used to a custom Access database which they love thanks to the straightforward interface.

 

We are rapidly coming to the conclusion that we might have to write something ourselves to do this the way we'd like it, but we'd need to a) query the Facility database for student, teacher and teaching group and b) find a way of displaying the results within Eportal.

 

Anyone got any advice? If we could customise the GUI a bit and stick with Eportal I'd be happy (but I don't think this is possible); if not can anyone point me in the direction of a suitable contact to advise on data extraction? Final question: does Eportal support user-created bitlets (a bit like web parts?)?

[bcdobbs]

Has any body had any luck getting interim grades directly out via SQL? Have no need to write anything back in so hopefully fairly straight forward.

 

Any help would be very much appreciated.

 

Kind regards

 

Ben

[pcstru]

Has any body had any luck getting interim grades directly out via SQL? Have no need to write anything back in so hopefully fairly straight forward.

 

Discussed a while back in This thread.

[bcdobbs]

Thank you!

[loksmb]

Hi All,

 

From my experience with CMIS, reading from the database might be fine but writing back is quite risky inspite of I having access to a data dictionary. The reason being that the data dictionary is not detailed enough to tell you all the tables that get affected for particular actions and there can be more than one tables involved in showing up an entity or a relationship in the frontend.

 

Also, the second reason is that Serco anyways warn that upgrades to the software can change tables and their usgaes so it can get tricky from that point of view as well, assuming you get everything right in the first place.

 

And lastly, there is this issue with the license as everyone has pointed out.

 

Cheers

Lokesh