+ Post New Thread
Page 1 of 3 123 LastLast
Results 1 to 15 of 40
MIS Systems Thread, Extracting data automatically from CMIS in Technical; Is there a way of extracting data from CMIS Facility automatically at set periods, for instance run a report and ...
  1. #1

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    30

    Extracting data automatically from CMIS

    Is there a way of extracting data from CMIS Facility automatically at set periods, for instance run a report and extract its contents to a csv file.

    A way to reference the databse and pull out data perhaps for a learning platform?

    Dan

  2. #2

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,942
    Thank Post
    1,199
    Thanked 1,069 Times in 760 Posts
    Rep Power
    330

    Re: Extracting data automatically from CMIS

    I am speaking to a third party software vendor at the moment who in turn is working alongside another software vendor and we have been asked if we would like to trial this software for them.
    The short of it is they have produced a intermediatary piece of software that sits between Sims or Cims plus any other SQL database and periodically queries the database for whatever information you require.
    I am waiting for them to get back to me at the mo will update.

  3. #3

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    30

    Re: Extracting data automatically from CMIS

    Sounds right up my street, exactly what im looking for.

    Would be intrested in hearing about any results or findings, and perhaps also in PM the name of the company lol!

  4. #4

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    30

    Re: Extracting data automatically from CMIS

    a simple vbscript or some kind of script which will connect to a sql database.

    connect to facility database and look up the table structure?

    anyone know anything about creating sql connections? etc...

  5. #5
    Guest

    Re: Extracting data automatically from CMIS

    If you run CMIS on SQL server (which you probably do) you should have some software with SQL Server called Enterprise Manager and Query Analyser.

    with Query Analyser you can write SQL statements that access CMIS data and export it to a csv file.

    You then add these statements to a procedure.

    In enterprise manager you create a scheduled job to run this procedure every night.
    It isn't very hard in theory, but you need a good understanding of SQL and of the structure of the CMIS data tables.

    An other way is to create a "view" of the data (like a query in Access) and then link an excel spreadsheet to the view via ODBC, excel can be set to pull the data from the SQL data when it is opened (or when you click the refresh the data link). A VB script can open the excel file, refresh the data, save it and close it again - this would also need scheduling.

  6. #6

    Join Date
    Feb 2007
    Location
    Lincolnshire
    Posts
    120
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Re: Extracting data automatically from CMIS

    I have always found cmis way of doing things very clunky so decided that as cmis worked on a database i would write a web application to deal with the information i wanted.

    I have written a web application to manage student events we call them referals, it allows me and staff at our school to log on and view the top "most wanted" for each year and look up which teachers they get the most referals from which lessons / times of the day and which days they struggle to behave on in hope to find patterns.

    Anyway long and short of it dependant on what you want to do, i know a little about the structure of the facility database so if u want some help let me know.

  7. #7

    Join Date
    Feb 2007
    Location
    Birmingham
    Posts
    35
    Thank Post
    5
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Re: Extracting data automatically from CMIS

    I've done it using a consultant to publish attendance data into excel documents in order to have a good overview of sessional attendance, so it is definately possible.

  8. #8
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25

    Re: Extracting data automatically from CMIS

    I do this on a regular basis - in fact our whole MLE / VLE / VLP or whatever it should be called is based on being able to do this.

    If you have CMIS installed on your machine then you arlready have the ODBC connection you need to be able to access the data through something like Excel or Access.

    Access is by far the easiest option if you are familiar with it. Assuming you are just link to the tables (or export them if you are nervous about accessing the data live) as you would any other data source.

    That is the easy bit - the next bit is working out what data you want and where it sits in the hundreds of tables (many of them empty) in the database. Then build the queries that extract the data you want in the Access query builder.

    I am nervous of saying too much becase the LEA have already promised to send me to bill of any school whose data gets screwed up by accessing data directly having following my advice - hence me being a little vague.

    But I can at least confirm it is exceptionally possible to get at the data without any scripting or SQL knowledge at all. Taking it to the next step and automating it could require a little knowledge depending on what exactly you want to exctract and how.

  9. #9

    Join Date
    Feb 2007
    Location
    Lincolnshire
    Posts
    120
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Re: Extracting data automatically from CMIS

    I access data live via sql but as i only read from the tables it shouldnt affect the data itself, lol @ you lea threatening you with the bill

    *hides all projects linking to cmis*

  10. #10
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25

    Re: Extracting data automatically from CMIS

    Even more distrubing is that the guy who said this too me now works for Capita.

    It all came from us publishing an app that would unlock tables when they got locked. They were giving out advice for staff to go to the SQL server and manually edit the data on the system which allowed for all sorts of things to go wrong.

    Our app was actually safer because it targeted and changed only what was needed rather than letting users loose in the raw data.

    And we were not even charging for it!

  11. #11

    Join Date
    Jun 2007
    Location
    Leicestershire
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Re: Extracting data automatically from CMIS

    Quote Originally Posted by Kained
    I have always found cmis way of doing things very clunky so decided that as cmis worked on a database i would write a web application to deal with the information i wanted.

    I have written a web application to manage student events we call them referals, it allows me and staff at our school to log on and view the top "most wanted" for each year and look up which teachers they get the most referals from which lessons / times of the day and which days they struggle to behave on in hope to find patterns.

    Anyway long and short of it dependant on what you want to do, i know a little about the structure of the facility database so if u want some help let me know.
    That's exactly what I want to do, but I can't find which table in the database that the referral information is stored in! I looked in one called STUEVENTS but it appears to be empty. Can you please tell me where to look?
    thanks,
    Simon

  12. #12
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    213
    Thank Post
    24
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: Extracting data automatically from CMIS

    Hi Simon

    i have found the tables that are used for the "Events" part of CMIS

    APPAPPFIELDS
    APPAPPEVENT
    APPEVENTDEFN

    I am trying to put together this in MS Access through an ODBC link to the live data.

    I have stumbled across how some of the tables are linked but to be honest its a real pigs ear!!

    Anyone know the significance of the "AppEventId"??

    Mark

  13. #13
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25

    Re: Extracting data automatically from CMIS

    Just had a very quick look at this - and we have hardly used the event log so I do not have a lot of data to go by.

    It seems that the AppEventID is the unique occurance for that student - if you find a nice high number in this field and then filter any student that has that number then they appear to have a lot of entries with different AppEventID's - my data has a lot of gaps in these numbers which may or may not be the norm.

    So the key to the data seems to be matching the AppEventID (defined in the APPEventDefN table) with the EventID between the APPAPPEvent table (where this pair of entires should be unique in any one record) and the APPAPPFields table where the pair of numbers can be found together frequently, each time with a different field ID and Value Data. This FieldID will probably match up with the defined fields in the APPEVFields table.

    Remember to match the students in both tables and the SetID.

    Does any of that make sense? I think I started to loose it on about the second line.

  14. #14
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25

    Re: Extracting data automatically from CMIS

    I have just thrown together a query on this that should pull everything out - not sure about posting the SQL on here in detail - worried about possible security implications and what it might do to the layout of the page!

    PM me if you want a copy of the SQL statement.

    One thing I found to watch for - the fields seem to be very limited on the number of characters, so some of the longer inputs have been spread accross two records - the linenum value in the APPAPPFIELDS table is the order these inputs need to appear in (all other values will be the same - apart from the input obviously).

  15. #15
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    213
    Thank Post
    24
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: Extracting data automatically from CMIS

    @ Limbo

    cheers for taking time to look into this for me

    the attached picture shows the query output that i have created in MS Access. This particular student has 12 events that can be viewd in CMIS but i cannot see a reason as to the jump from 8 to 431??

    If you could PM that SQL that would be great 8)

    Mark
    Attached Images Attached Images

SHARE:
+ Post New Thread
Page 1 of 3 123 LastLast

Similar Threads

  1. Assessment Data(CMIS)
    By Kained in forum How do you do....it?
    Replies: 7
    Last Post: 2nd April 2011, 12:47 PM
  2. Extracting data from an MIS
    By MikeBostock in forum MIS Systems
    Replies: 76
    Last Post: 27th February 2008, 09:18 PM
  3. Merging data from two cmis datasets
    By gskelton in forum MIS Systems
    Replies: 1
    Last Post: 5th July 2007, 10:47 AM
  4. CMIS DATA MANAGER
    By SpecialAgent in forum Educational IT Jobs
    Replies: 0
    Last Post: 9th May 2007, 11:10 AM
  5. CMIS Data Manager
    By SpecialAgent in forum Educational IT Jobs
    Replies: 0
    Last Post: 23rd February 2007, 10:54 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •