+ Post New Thread
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 39
MIS Systems Thread, SIMS <--> SQL DB relationship schema in Technical; Originally Posted by localzuk I don't think you've been given the entire documentation package then? Yeah, we have got this ...
  1. #16
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    960
    Thank Post
    58
    Thanked 163 Times in 115 Posts
    Rep Power
    67
    Quote Originally Posted by localzuk View Post
    I don't think you've been given the entire documentation package then?
    Yeah, we have got this direct from capita.

    Quote Originally Posted by localzuk View Post
    And I don't think rpwillis will be allowed to, as the NDA doesn't allow discussion of such things...
    Capita's documentation is covered by the NDA not the program that is created, if it was then the company would not be able to release it.

    even a pointer in the right direction would be helpful using an example on a website which implements similar system..

  2. #17
    rpwillis's Avatar
    Join Date
    Aug 2007
    Location
    SalamanderSoft Ltd
    Posts
    178
    Thank Post
    43
    Thanked 61 Times in 49 Posts
    Rep Power
    26
    @gdn

    Sorry, I misunderstood you then. That's for the comments on what I've been working on with Twynham. I'll have to get round to blogging about it soon.

    @penfold_99

    localzuk is correct in that our NDA prohibits that sort of discussion. The document "Technical Guide - Interfacing With SIMS.net.doc" which comes with the API indexes gives an example though. I can download them through Support Net e.g. Resource: 14928 - 13th Jan 2009, but I don't know if they are available to schools.

    Richard

  3. #18
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    960
    Thank Post
    58
    Thanked 163 Times in 115 Posts
    Rep Power
    67
    Quote Originally Posted by rpwillis View Post
    @gdn
    localzuk is correct in that our NDA prohibits that sort of discussion. The document "Technical Guide - Interfacing With SIMS.net.doc" which comes with the API indexes gives an example though. I can download them through Support Net e.g. Resource: 14928 - 13th Jan 2009, but I don't know if they are available to schools.

    Richard
    Thanks Richard,

    In capita support what menu route are you taking to get the documentation?

  4. #19
    rpwillis's Avatar
    Join Date
    Aug 2007
    Location
    SalamanderSoft Ltd
    Posts
    178
    Thank Post
    43
    Thanked 61 Times in 49 Posts
    Rep Power
    26
    Hi Penfold,

    It's just on my home page in SupportNet under files. It will be the default set up of SupportNet for partners.

    Richard

  5. #20
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    960
    Thank Post
    58
    Thanked 163 Times in 115 Posts
    Rep Power
    67
    Quote Originally Posted by rpwillis View Post
    Hi Penfold,

    It's just on my home page in SupportNet under files. It will be the default set up of SupportNet for partners.

    Richard
    Hi Richard,

    Thanks for that, unfortunately i don't have access to it but have put in a request with our lea for it.

  6. #21
    budgester's Avatar
    Join Date
    Jan 2006
    Location
    Enfield, Middlesex
    Posts
    486
    Thank Post
    4
    Thanked 37 Times in 30 Posts
    Rep Power
    24
    Quote Originally Posted by penfold_99 View Post
    @Gdn,

    i am probably a useful guy to know, i have done exactly what you are doing, i have built modules for Moodle that pull information from SIMS.net via sql views.

    The key is to get your head round the relationships in the db, it's hard as it not a normalised database.

    Capita prefer to people to use there business objects but this is not possible if your not on a windows platform.
    We do a similar thing with Serco CMIS, and directly read from the database, using various technologies, e.g. C#, PHP, ODBC, to windows and apache on linux.

    [RANT]
    But having worked on the database what really strikes me is the complete mess that they call the database design.

    Lets face it, normalisation, indexes and foreign keys are pretty much database 101.

    As far as I can see no one at either of these companies has a clue about database design.

    And the other thing that annoys me is the way that they want to restrict my access to my data.

    [/RANT]

  7. #22
    Martin_W's Avatar
    Join Date
    Aug 2010
    Posts
    7
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hey Gdn,

    I saw your post from about 1,5 year ago. I was wondering if you had made progress with the SQL queries you needed. I am trying to extract data directly from the SIMS database as well. If you have any tips/help, that would be much appreciated!

    If you have any problems with your data, maybe I can also help you out. I have a lot of experience in extracting data from school information systems other than SIMS (directly from the source databases). I can use the tools of the company I work for to help making the data extraction easier.

    Please let me know, if you can help.

    Martin

  8. #23

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159
    @Martin
    What you trying to extract?

  9. #24
    Martin_W's Avatar
    Join Date
    Aug 2010
    Posts
    7
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I want to extract data about grades and absences of pupils from directly from the database for management information purposes.

    @jinnantonnix
    Are there already accessible views in the SIMS SQL database that are easy to use, is that what you mean?

  10. #25
    Martin_W's Avatar
    Join Date
    Aug 2010
    Posts
    7
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    @jinnantonnix
    Thanks for you eleborate explanation!

    I've read about the Business Objects universes that 3rd parties can purchase. I've worked with Business Objects before, but I was wondering:
    Are they actually that useful in this case. I mean if I can join together the views that I need, is the BO universe really of that much extra value?

  11. #26
    DBMan's Avatar
    Join Date
    May 2009
    Posts
    12
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by jinnantonnix View Post
    Use SQL Management Studio to browse the SIMS database. Under each SIMS database, you'll find an SQL branch called Views.

    As an example, there's a view called sims.rpt.vix_report_student. If you right click this view, then select Top 1000 rows, you can see how this view neatly joins the disparate tables and presents a nice coherent view of pupil records.

    If you right-click a view and choose Design, you can see how the views are built from the underlying tables.

    But for goodness sake, don't modify a view or you'll do a lot of damage. Only use a SELECT with views; an UPDATE can change the underlying tables directly - not good.

    The good thing about views is that even if the underlying table structures change (and they often do), the names of the views remain static and any queries you write are likely to still work after upgrades.

    You can use a view as part of a query, of course.
    If you wanted to see the pupil details for a particular UPN, for instance, you could use this query:
    Code:
    select * from sims.rpt_vix_report_student sr
    where sr.upn = '12345454'
    With this one simple query, we can get a whole heap of data about a pupil. The view draws in fields from multiple tables. Try it to see what you get.

    We can effectively treat a view as a table in its own right.
    Sound advice. SIMS runs on top of plain old SQL Server, which in this case is provided by the end user (school) rather than the application provider (Capita).

    As it is 'just' SQL Server, you can use the standard tools to interact with the database. Management Studio can be used to poke around and find what's in there and to run queries.

    As has been made clear on several posts, running SELECT queries can do no harm whatsoever...apart from maybe slowing down the system if you write a bit of bad SQL.

    Why not take a copy of the database and run queries against the copy? It's your data and your DBMS software, remember!

    Although the schema may be a mess in some folks eyes, using the built-in views that SIMS already uses will surely help - a view is simply a query that can be used as a data source just like a physical table. Either query the views using a SELECT query or take a copy and query it there.

    DON'T MAKE ANY CHANGES TO THE SIMS DATABASE!!!!

  12. #27

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159
    Quote Originally Posted by DBMan View Post
    As has been made clear on several posts, running SELECT queries can do no harm whatsoever...apart from maybe slowing down the system if you write a bit of bad SQL.
    SELECT is only a step away from DROP, thus you shouldn't publicly post information about where stuff is. You also are bypassing the auditing and security. A standard SIMS user account isn't able to login directly with SQL, so you would have to create a new SQL account. Personally, if the standard SIMS tools aren't good enough, you've got the business objects. Which keeps everyone happy.

  13. #28

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,866
    Thank Post
    518
    Thanked 2,486 Times in 1,928 Posts
    Blog Entries
    24
    Rep Power
    838
    Quote Originally Posted by matt40k View Post
    SELECT is only a step away from DROP, thus you shouldn't publicly post information about where stuff is. You also are bypassing the auditing and security. A standard SIMS user account isn't able to login directly with SQL, so you would have to create a new SQL account. Personally, if the standard SIMS tools aren't good enough, you've got the business objects. Which keeps everyone happy.
    Security through obscurity is not security. Someone posting the structure of the SIMS .net database, and simple commands on how to get data from it is not weakening security in the slightest. If a user who could use the above commands could run them, then they'd also be able to browse the database anyway...

  14. #29

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,009
    Thank Post
    680
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    Is it actually the case that Capita do not disallow you from directly accessing the database, merely that they wont support you if you do?
    It seems very odd, i know that it's our data and we provide the software, but i'd have thought that it would be part of the licence / terms of use that we aren't allowed to access the underlying data. I suppose they can't do that.

    I have accessed copies of our database for many years, but don't do much with it, it's mostly for testing purposes.

    Quote Originally Posted by Martin_W View Post
    I've read about the Business Objects universes that 3rd parties can purchase. I've worked with Business Objects before, but I was wondering:
    Are they actually that useful in this case. I mean if I can join together the views that I need, is the BO universe really of that much extra value?
    The Business Objects are useful because they allow you to write back to the db, and i don't think anyone would dare do that without support. Also, if you want to provide third party apps using SIMS data, it's the only way to do it and not have to fix things if they break. If you only want to extract data for internal use then you can get away with not using them.

    The views make a useful method for accessing the data, that is a little more resilient to the frequent changes that occur with upgrades.

  15. #30

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,072
    Thank Post
    377
    Thanked 380 Times in 309 Posts
    Blog Entries
    8
    Rep Power
    178
    Been away for 8 months and I see the topics of choice never change!

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

Similar Threads

  1. Trust Relationship for web traffic
    By ahunter in forum Wireless Networks
    Replies: 4
    Last Post: 11th November 2008, 05:34 PM
  2. Old SIMS SQL Services
    By enjay in forum MIS Systems
    Replies: 5
    Last Post: 27th February 2008, 01:55 PM
  3. SIMS and SQL Express
    By interele in forum MIS Systems
    Replies: 3
    Last Post: 2nd February 2008, 05:43 PM
  4. SIMS and SQL, which version
    By andrewsmart in forum MIS Systems
    Replies: 4
    Last Post: 14th June 2007, 08:13 PM
  5. SIMS + SQL 2005?
    By Norphy in forum MIS Systems
    Replies: 22
    Last Post: 5th April 2007, 10:20 AM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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