+ Post New Thread
Page 3 of 3 FirstFirst 123
Results 31 to 40 of 40
MIS Systems Thread, Extracting data automatically from CMIS in Technical; - Assessment spreadsheets for staff linked directly to the data, including being able to update them from home via the ...
  1. #31
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    212
    Thank Post
    23
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: Extracting data automatically from CMIS

    - Assessment spreadsheets for staff linked directly to the data, including being able to update them from home via the web (http without a VPN)
    Extracting the assessment data from the CMIS tables is the main issue i had and posted my issue on page 1 of this thread

    Could you post on here the tables you use and the relational links between them?

    Mark

  2. #32
    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

    Quote Originally Posted by mark80
    Extracting the assessment data from the CMIS tables is the main issue i had and posted my issue on page 1 of this thread
    Sorry mark80 - I thought you were talking about the events log rather than the assessment data.

    To understand the data you need to understand some of the terminology CMIS uses (forgive me if you already know this bit - but others may not)

    Criteria - this is a single collection of data with its rules, for example a KS3 level that is going to be either 2, 3, 4, 5, 6 etc. You only have to define this once and it can be used repeatedley and the same rules always applied to give continuity. Another example could be effort, forecast grade, expected grade etc. This allows life for like grades taken at different times to be based on the same logic and therefor comparable.

    Assessment - this is a collection of different criteria. For example a simple assesssment could be an effort and KS3 attainment level. This might be collected every term, once a year etc. Collecting the two criteria together into one assessment makes it easier to do the next step which is:-

    Examination - this is the actual event you are asking staff to enter information against. So the simple assessment example above might be collected every term - so you set up a different examination for each of those terms that consists of that assessment. Maybe Year 7 Autumn Term, Year 7 Spring Term etc. All containing the simple KS3 assessment above.

    OK - bare with me, I am getting to the important bit now:-

    All the results are stored in NSTURESULTS which reference the StudentID, the ExamID and the AssessID as well as the actual results which are in the CriteriaData field (not recognisable as a result just yet - you need to read on because it is not that simple!)

    Also in this table is the ModuleID - this is how you determine which subject the particular results were for (so a student may have several lines in this table with the same assessment and exam information - once for maths, science, technology etc.)

    Hopefully you will notice that the SetID is also in the table, but remmber that this refers to the dataset the results were recorded in, so do not automatically just strip out this years dataset, or you will only get this years results.

    The next table is the EXAMINATIONS table, useful if the examid is not enough to identify the exam you are after - sometimes itis only the name of the assessment (which you will find in this table) that gives this away, rather than the examid found in the examinations table. This table also tells you the relevant AssessID for this exam, but you should already know that from the nsturesults table. So this table may prove to be unecessary for you.

    The ASSESSMENTS table will give you the nme of the assessment relating to the assessid field in the results table - again only necessary if you cannot identify what you need from the assessId field.

    Next is the ASSESSCRITERIA table will give you the information you need to be able to extract the data from the results table - this is the bit that is not so simple.

    If you look up the relevant assessid in this table you will see there are multiple lines for many of the assessments - the difference being the criteriaID (and related Critlabel). What you are really interested in from this table is the mapvalue field - a numeric field.

    Take this numeric field and look back at the criteriadata field in your NSTURESULTS table (and it will need to be one with the right assessid for this mapvalue) - you will hopefully see the mapvalue appear in the criteriadata field followed perhaps by a control character and then another value. This second value is the result for that critiera. So an assessment will generate a string in the criteriadata field that contains several of these map values followed by the result.

    All you have to do is search that string for the mapvalue and then the next piece of data after that is the result you are looking for.

    Hope that all makes sense - I am going to a lie down now!


    And again more details, if needed, about the individual criteria can be found in the CRITERIA table.

  3. #33
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    212
    Thank Post
    23
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: Extracting data automatically from CMIS

    Very comprehensible peice of info there and much appreciated

  4. #34
    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

    no probs - let me know how you get on

  5. #35
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    212
    Thank Post
    23
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: Extracting data automatically from CMIS

    Compiled a query in Access that has given me the MapValue and the CriteriaData so visually i can see what each part of the CriteriaData string is but i want to be able to turn this:

    [] represents the character in the CriteriaData field that splits the criteria

    Code:
    CritLabel	MapValue         CriteriaData
    Comment         22             20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
    Concern         126           20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
    Effort          127           20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
    More Able       140           20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
    Summer NCL       20            20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
    Working          23            20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
    Into either a table in Access with Summer NCL, Comment, More Able etc as field headings and the content filled with actual vales (not codes) from the criterdata field

    Hope it makes sense :?:

  6. #36
    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

    There are two ways to do this depending on how much data you are wanting to look at at any one time.

    You can have the data converted and displayed live using a crosstab query - but depending on how much data you want to look at at any one time this may be slooooow.

    Alternatively you can write a number of queries that build the data into a table which you can then call on - this will be quicker, but will need updating every now and then.

    A couple of the spreadsheets I produce have 200 columns or so - for this I use a combination of tables and live queries. Things like SAT results hardly every change so that would probably be a good candidate for a permenant table wherease attendance changes twice a day so that is better live.

    Anyway - copy and paste this into the SQL window of the Access query designer and it should give you what you want (just hope it does not screw the page up!)

    Code:
    TRANSFORM First(Left(Mid([criteriadata],InStr(1,[criteriadata],[mapvalue])+1+Len([mapvalue]+1),100),InStr(1,Mid([criteriadata],InStr(1,[criteriadata],[mapvalue])+1+Len([mapvalue]+1),100),Chr$(10)))) AS [Value]
    SELECT NSTURESULTS.StudentId, NSTURESULTS.ExamId
    FROM ASSESSCRITERIA INNER JOIN NSTURESULTS ON ASSESSCRITERIA.AssessId = NSTURESULTS.AssessId
    WHERE (((NSTURESULTS.ExamId)="<<YOUR EXAMID GOES HERE>>"))
    GROUP BY NSTURESULTS.StudentId, NSTURESULTS.ExamId
    PIVOT [ModuleID] & " - " & [CritLabel];
    Then go back to the design view and you should see the place to put your desired examid.

    It will do this for every student that has ever had that examid - if you want to filter it to a certain year group then include the STUDENTS table and filter for the relevant setid and courseyear.

  7. #37
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    212
    Thank Post
    23
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: Extracting data automatically from CMIS

    Wow thats a great way of parsing the string into its components not 100% what i want yet but a great help in getting me statred

    i owe you a drink

  8. #38
    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

    Let me know if I can help get you to 100%!!

  9. #39

    Join Date
    Jul 2009
    Location
    Cambridgeshire
    Posts
    8
    Thank Post
    0
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    Has anyone done anything with pulling students who miss more than a set number of days in the academic year?
    We have a PA register (Persistent Absentee) which is currently done in Excel and exported from CMIS by hand each week. The spreadsheet then has graphs on other worksheets based on the data in the first worksheet. Basically wanting to pull out those students who are persistently absent from school.

    I know with office 2007 you can run an SQL import, but that requires getting the sql right first! I have the code to pull out the student details I need, but I can't work out how many 'sessions' the students should be in school for and then how many they actually are there for.

    Spreadsheet columns are:
    StudName, YearGroup, Total Number of Sessions, Number of sessions missed, Gender, SEN, FSM, Ethnicity, CLA, YC
    (Sessions - I.e: AM attendance slot would = 1 session. In one day if a student is present for both AM & PM tutor registration then they would be 2 sessions)

    Has anyone got any ideas?
    Cheers.

  10. #40
    BarryWAaMC's Avatar
    Join Date
    Oct 2009
    Posts
    22
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by danIT View Post
    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
    Dan,

    If you don't mind playing with PHP, then Moodle VLE has two add-on modules which connect to CMIS, both of them are for different areas:
    Facility to Moodle - Connects students for authentication to Moodle and imports teaching groups.
    MIS - For looking up student ePortal information.

    I'm sure the coding of these two modules would help with finding the solution that you would like.

    Hope thats of help.

    Barry.
    Winton Arts and Media College

SHARE:
+ Post New Thread
Page 3 of 3 FirstFirst 123

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
  •