Getting data from apps to NDW

Current (beginning of Nov 2020) state of play:

Had a meeting with TIS and NDW people on 14 Oct. This is what came out of the meeting:

• Moon on a stick is a good starting point, and message queuing as an approach is ultimately understood to be 'a very good way of talking to each other', but resources available at the NDW end are very stretched for the foreseeable future.

• NDW team would like to understand better the use cases by service (TIS, TISSS, Reval, TIS-ESR and NI), to determine the kinds of data that we most usefully should store in NDW vs what we should be looking to query (if not just store) in-situ.

• One challenge is the nature of how Mongo stores unstructured data and the relational structure of the NDW.

• Feeling was that the compromise option outlined in the invite email is probably the best thing to look at first up - TIS team effectively implementing a messaging queue at our end (for the use cases identified, rather than 'all the data'), and then perhaps having a layer that converts that into a form that is more easily consumable into the NDW (though resource at the TIS end is also quite stretched at the moment too).

This is where we are now (I think - please shout if any of this is wrong!):

• Reval: reporting was part of the MVP. After the re-procurement it was de-prioritised as we focused on delivery but it is still an expectation of the MVP for after launch.
  Ify has produced a list of data and the kind of reports desired that James is looking at right now - that we can start planning for post-Reval launch.

• TISSS: pulls data from TIS (so no extra requirement there), and when it is launched (no longer scheduled for this month) it will offer Trainees the ability to complete their Form Rs which then gets stored in JSON format. When a Trainee has done so, we may be in a position where that information is different to what we hold on TIS, which is turn may be different from ESR. In terms of reporting, therefore, the data we're interested in would be any data existing on TIS that could be different to data a Trainee fills in on their Form R. Longer term, this would be a powerful report to help tidy up data across TIS / ESR (as we are not going to enable Trainees to update their master data from TISSS).
  James is going to detail the fields in question.

• TIS-ESR: before he left, Paul created a new table in TIS which TIS-ESR feeds into for exposing Placement export data. The contents of this table need to go to NDW.
  As the data now exists in TIS, we are anticipating the vehicle to get it from there to NDW will be the existing TIS-NDW ETL.
  Question: how unstructured is the data in that table, and is that an issue for transferring to the NDW (Pepe? Andy?)

• NI: we are building NI as a separate instance of TIS. Therefore the anticipation is that we would clone the TIS-NDW ETLs for NI. NI user base would be a handful of users, and the data set would be small
  We will need to think about the timing of the ETL to make sure it doesn't lock things up for the TIS-NDW ETL.

• TIS: We have already set up a parallel ETL between TIS-NDW(PaaS) for both Prod-Prod and Stage-UAT. As a result, there should be no further work (other than the above TISSS and TIS-ESR extension of data) from the TIS side of things.

So, in terms of immediate need:

• Nothing more is needed from the TIS end to support the switch over to NDW PaaS on Friday.

• Reval reporting - the team will look at a way of providing reports weather it is adding data to Metabase and tableau sitting on top, a transformation approach using spring batch or a message solution with an interface to interact with NDW since they can not support a messaging solution on their end for now and it's not known when they can.

• TISSS reporting is not needed this month. We'll need to confirm the approach for handling the data - creating 'sparce tables'(?) in NDW / using SpringBatch / some other transformation - and the vehicle for transporting it, but it won't be needed probably until January at the earliest.

• Some of the data needed for reporting from the TIS-ESR perspective is already in TIS - so we just need to work out how to modify the TIS-NDW ETL to get that data into NDW (again once we've confirmed the approach and vehicle for transport, as above. We are likely to extend the data from TIS-ESR going into NDW, but it is likely to follow a similar pattern to the Placement export data.