+ Post New Thread
Page 1 of 3 123 LastLast
Results 1 to 15 of 39
MIS Systems Thread, SIMS <--> SQL DB relationship schema in Technical; ...
  1. #1
    Gdn
    Gdn is offline
    Gdn's Avatar
    Join Date
    Mar 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    SIMS <--> SQL DB relationship schema

    Hey.

    I know this topic has been covered ad nauseum, but I'm building a web gateway that extracts information from SIMS's SQL Server database; but I don't want to go through SIMS to get it. That means no SIMS SDK or anything of that kind. I don't see the need for those extra levels of abstraction. I just want to connect directly to the database, run some queries, then do whatever I want with the information.

    I've already had some success, and the project is going pretty well. Our LEA has given us its full support.

    Now, we only want to pull data from the database - there will be no writing back to it. Capita are aware of our intentions, and their response was:

    "We donít support the schema as that implies direct connection to the database. We would rather the school use IMS or the Command reporter."

    So, they would rather we do things their way - but why wouldn't they? From what my LEA has told me, much of the talk on here about Capita rescinding their SIMS support if we circumvent their software is just so much scaremongering.

    Anyway, regardless of the Capita issues, we've been given the go-ahead by the LEA to look into this.

    It's going to happen.

    So, I know a lot of folks have had some success in this area already (penfold_99, I believe, with Moodle integration; and the chaps at SalamanderSoft who did a lot of work for Twyneham, to name a couple); and I was wondering if anyone has managed to do any extensive mapping of the SIMS/SQL relationships, perhaps using the SQL Server Profiler or something similar to log the requests that SIMS is sending to the database.

    We're really looking for people to collaborate with on this; and we would be massively appreciative to anyone who has already made substantial headway into mapping the relationships who might be willing to help. I know you're out there :)

    Thanks in advance,

    -Gdn

  2. #2

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,130
    Thank Post
    522
    Thanked 2,540 Times in 1,975 Posts
    Blog Entries
    24
    Rep Power
    875
    I know you probably won't see this as being too helpful, but can I just point out that one of the main reasons the schema isn't supported is that it is subject to change, every time an update is released...

  3. #3

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,442
    Thank Post
    369
    Thanked 650 Times in 531 Posts
    Rep Power
    160
    Anyone who access the db direct will invalid there SIMS Support... it's like getting your pay paid annually then burning it to heat your house. Ok, it works, but till not the best way of doing it.

    Nothing wrong with the command report tool.

  4. #4
    Gdn
    Gdn is offline
    Gdn's Avatar
    Join Date
    Mar 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    @localzuk

    Yeah, we're aware of that. But I just do waht I'm told :)

    My workaround idea for that is to generate my SQL queries on the fly, using the data extracted from the SQL Profiler logs.

    The information we want to pull isn't that extensive, so if I could use SIMS to run a specific function every day, then use SQL Profiler to log the queries SIMS passes to SQL Server, I can run a script to extract the correct schema data from the logs and build my own SQL queries at runtime.

    It's still early-doors yet, and the whole project might fall on its arse; but part of my job is to try and come up with these kinds of workarounds and try to see how feasible they are.

    So, although the schema might change during SIMS updates, at most (touch wood) the downtime of our own application would be, what - A day or two - while we do the remapping. For the kind of information we want to pull from the database, that sort of downtime isn't really a problem. Just an inconvenience for the users, really. None of the stuff we want is *needs* to be accurate at the time of the request. When you ring up your bank, you only ever get information correct as of the previous close-of-day.

    Thanks though - because you might be right. until I look at it in much more detail, this might actually turn out to be a pretty big issue. I'll just have to wait and see though.

  5. #5
    Gdn
    Gdn is offline
    Gdn's Avatar
    Join Date
    Mar 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    @matt40k

    Technicalities, my friend. And that's for the legal gremlins to worry about - not me :)

  6. #6

    Join Date
    Mar 2008
    Location
    Surrey
    Posts
    2,210
    Thank Post
    114
    Thanked 331 Times in 273 Posts
    Blog Entries
    4
    Rep Power
    115
    Quote Originally Posted by Gdn View Post
    When you ring up your bank, you only ever get information correct as of the previous close-of-day.
    Get a better bank. I get up-to-date information from mine whenever I call, including any pending transactions and so on. I expect to get that information as well, and would be more than a little angry if I transferred money I didn't have, or paid for a bill I couldn't afford, because their information isn't up to date.

  7. #7
    rpwillis's Avatar
    Join Date
    Aug 2007
    Location
    SalamanderSoft Ltd
    Posts
    182
    Thank Post
    50
    Thanked 63 Times in 51 Posts
    Rep Power
    26
    Hi Gdn,

    It's Richard from SalamanderSoft. Thanks for thinking of us, but I'm afraid that I can't help you here. All our access to Sims is through the business objects and API. In fact I think we'd be in breach of our partnership contract otherwise.

    Regards,
    Richard
    SalamanderSoft
    http://blog.salamandersoft.co.uk

  8. #8
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    983
    Thank Post
    58
    Thanked 165 Times in 117 Posts
    Rep Power
    68
    @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.

    if you let me know what info your trying to get out, i my have already created the views.

    As for support, we are supported by our lea and capita, we have an agreement in place that if we break it, we pay to fix it.

    Capita prefer to people to use there business objects but this is not possible if your not on a windows platform.

  9. #9
    Gdn
    Gdn is offline
    Gdn's Avatar
    Join Date
    Mar 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    @jamesb

    Right, so I gave a bad analogy. Thanks for pointing that out.

    Look, I know there might be problems. I expect there will be problems. But it's my job to explore what those problems might be and to present them to the Powers. People have done this already, with varying degrees of success. I'm just exploring options. Can we maybe get a little more positivity here?

  10. #10

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,442
    Thank Post
    369
    Thanked 650 Times in 531 Posts
    Rep Power
    160
    I really don't think a school would go for a product that would breach the Capita license \ support and cost you thounsands of pounds. Capita provide a API - I believe in C# for anyone who needs anything above the standard reports, which to be perfectly honest, gives you pretty everything you want. If you contact your LEA, they can even rename the school name, person name who wrote it with whatever you want.

  11. #11
    Gdn
    Gdn is offline
    Gdn's Avatar
    Join Date
    Mar 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    @rpwillis

    Hey Richard. I was actually aware that you'd done what you have by using their API. But I was thinking of getting in touch about it anyway, as it's another avenue I'll have to explore in order to come up with a decent report. And what you've done for Twyneham looks awesome.


    ----------


    @penfold_99

    Cheers dude. Someone dropped your name to me a week-or-so back saying that you might be the guy to speak to. And I knew there would be some way around all this Capita support/contract/legal rights nay-saying crap. My LEA have told me as much, and no one, not even the people at Capita, have been able to give me a difinitive answer on the subject, including pertinent quotes from the support contract or Terms of Use. It all seems very hazy at best.

    the If We Break It We Fix It approach seems perfectly reasonable to me.

    Can I get in touch later today to pass you some stuff in greater detail?

  12. #12

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,442
    Thank Post
    369
    Thanked 650 Times in 531 Posts
    Rep Power
    160

  13. #13
    Gdn
    Gdn is offline
    Gdn's Avatar
    Join Date
    Mar 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    @matt40k

    yeah, you missed the bit where I get paid to give my bosses an in-depth analysis of the alternatives, complete with working examples purely because they want to look at that information - because that's what they're paid for - and not just swallow the Capita-Is-The-Best story without having all the alternative options in-front of them - no matter how true the Capita-Is-The-Best story may or may not be.

  14. #14
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    983
    Thank Post
    58
    Thanked 165 Times in 117 Posts
    Rep Power
    68
    Quote Originally Posted by matt40k View Post
    I really don't think a school would go for a product that would breach the Capita license \ support and cost you thounsands of pounds. Capita provide a API - I believe in C# for anyone who needs anything above the standard reports, which to be perfectly honest, gives you pretty everything you want. If you contact your LEA, they can even rename the school name, person name who wrote it with whatever you want.
    @matt40uk

    The business objects are only as good as the documentation, i would use the business objects but i don't have any implementation/usage documentation. I have all the information about whatt dll do what but not how to get the information out of them.

    @rpwillis

    This is a bit cheeky, but any change you could post a hello world program on how to pull information from sims through the business object(dlls) not the command report tool?

  15. #15

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,130
    Thank Post
    522
    Thanked 2,540 Times in 1,975 Posts
    Blog Entries
    24
    Rep Power
    875
    Quote Originally Posted by penfold_99 View Post
    @matt40uk

    The business objects are only as good as the documentation, i would use the business objects but i don't have any implementation/usage documentation. I have all the information about whatt dll do what but not how to get the information out of them.

    @rpwillis

    This is a bit cheeky, but any change you could post a hello world program on how to pull information from sims through the business object(dlls) not the command report tool?
    I don't think you've been given the entire documentation package then?

    And I don't think rpwillis will be allowed to, as the NDA doesn't allow discussion of such things...

SHARE:
+ Post New Thread
Page 1 of 3 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
  •