Tagged: needless changes
This is the thread where we moan about ESFA changing the data structure of the .mdb export BACK to the structure we moaned about four years ago when the FAM codes and other monitoring fields were stored in separate tables for no apparent reason which means we have to rewrite a bunch of queries to do the simplest things…
16/17 Access query to count enrols on a course:
SELECT Valid_LearningDelivery.ProvSpecMon_A, Valid_LearningDelivery.LearnAimRef, Count(Valid_LearningDelivery.LearnRefNumber) AS CountOfLearnRefNumber
GROUP BY Valid_LearningDelivery.ProvSpecMon_A, Valid_LearningDelivery.LearnAimRef
ORDER BY Valid_LearningDelivery.ProvSpecMon_A, Valid_LearningDelivery.LearnAimRef;
17/18 query to do the same:
SELECT Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMon, Valid_LearningDelivery.LearnAimRef, Count(Valid_LearningDelivery.LearnRefNumber) AS CountOfLearnRefNumber
FROM Valid_LearningDelivery INNER JOIN Valid_ProviderSpecDeliveryMonitoring ON (Valid_LearningDelivery.AimSeqNumber = Valid_ProviderSpecDeliveryMonitoring.AimSeqNumber) AND (Valid_LearningDelivery.LearnRefNumber = Valid_ProviderSpecDeliveryMonitoring.LearnRefNumber)
GROUP BY Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMon, Valid_LearningDelivery.LearnAimRef
ORDER BY Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMon, Valid_LearningDelivery.LearnAimRef;
(where ProvSpecMon_A = course code)October 12, 2017 at 3:19 pm #205461
I’m wondering if this is related to the export not working at all in version 005? That maybe rather than properly fixing it they’ve just gone back to the 14/15 configuration export model?October 12, 2017 at 3:22 pm #205463
Steve – I haven’t had a chance to test yet, but when you say the 14/15 configuration export does that mean the address fields have stayed where they used to be in a separate learner contact table rather than moving into the learner table (where they used to be about five years ago)!October 12, 2017 at 3:41 pm #205473
Ha! No, actually, the only change I really expected was with the address fields and they have, as you say, come into the Learner record due to the change in the structure of the XML entity.
That, at least, makes sense (and I don’t tend to use the addresses anyway). It’s why all the other tables have reappeared that is annoying me…October 12, 2017 at 3:47 pm #205475
didn’t think it was likely (or possible) but thought I’d check. I’ve also realised what you mean by looking at the second query – we’ll have to rewrite all our import routines and I thought it was just the learner details…….what a painOctober 12, 2017 at 4:03 pm #205479
don’t forget the employment statuses (less of an issue for you I’d imagine?)…
Definite changes so far:
Provider Specified Learner Fields in own table rather than in Learner table
Provider Specified Learning Delivery Fields in own table rather than in Learning Delivery Table
Learner FAM fields in separate table rather than in Learner Table
Learning Delivery FAM fields in separate table rather than in Learning Delivery Table
Employment Monitoring fields in separate table rather than in Employment table
Learner Contact fields moved into the Learner table
Rulebase_DV_Learner and Rulebase_DV_LearningDelivery are also absent (although it’s not the first time we’ve been without the derived tables at this time of year)October 12, 2017 at 4:30 pm #205492
Thanks for the summary – that’s really useful – it does have some pretty big implications for us (and organisations we work with as well).
I’m going to contact the Agency directly but do you think this is going to be it for the year or is it an interim hiccup before another FIS update changes the export again? It’s a lot of work for us and others we work with, if we then have to change back later in the year.October 13, 2017 at 9:22 am #205661
I’ve had this response from Ian Tress (Data Collections Service Product Owner) at the Agency:
“We are aware the schema provided in the latest FIS mdb export has changed in the current version.
To provide minimal impact for anyone who might have already updated their export routine; we are looking at providing both the flattened structure and the normalised structure from the most recent update.
We will also be adding the missing DV and FD Val tables to the export.
We are endeavouring to deploy this to live between the 6th & 8th of Nov in time for the R04 close.”
In a conversation last week I’d specifically mentioned the Provider Specified fields not being in the learner/learning delivery table and think this means they’ll be going back into the FIS db export as part of the flattened structure export.October 23, 2017 at 11:03 am #208473
Have you heard anything else about this, i.e. is it likely that in a short period of time we will have the ‘old’ database schema back?
I’ve just started reviewing our FIS based reports and realised none of them work due to the changes. I don’t want to spend time converting them if I can wait a short time and they will work without changes!November 1, 2017 at 2:00 pm #211013
I haven’t since posting above. I’d asked if the update (hopefully putting back the provider specified fields into their respective tables – learner and learning delivery) would just be a main component set update and I received a response saying that was correct.
As a result, I’m waiting for a main component set update in the next week and hoping it will sort out the issues we have. Judging from Ian’s response (posted above) it means there will be two options open to us – the ‘new’ in year database export, which prompted Steven’s initial post in this thread and this 6th-8th November fix which I’m hoping will mean most of our old import routines will still work.November 1, 2017 at 2:12 pm #211026
Well it’s not happened with component set 008 which I’ve just updated to, Access Export appears the same.
I’ve not even had the courtesy of a holding response from ESFA, although I suspect including the words “formal complaint” in an email might have this effect…November 2, 2017 at 11:54 am #211256
Steve – have you still got my number?November 2, 2017 at 11:57 am #211261
I’ve now had exactly the same response as you got, not sure why it took three weeks…November 2, 2017 at 4:50 pm #211347
I’ve heard from a colleague that the release of component set 009 has added an option to export to an Access database in a flattened format. She sent a screen shot and it’s below the normal database export check box.
I’m not sure about all the field changes in the original post but do know that it adds the provider specified fields and the FAM codes back into the learning delivery table at the very least.November 10, 2017 at 2:31 pm #213237
You must be logged in to reply to this topic.