Chris needs answers before the training-provider briefings on 13 and 15 October (pre-read goes out a week ahead, so answers by Friday 2 October). WYCA asked Thrive not to promise a live Power BI login until access is confirmed; the slides currently say providers get "provider-level findings throughout the pilot, through dashboard access or regular extracts". This page builds up the reply, Chris's question first, then our answer.
Question 1
Provider access
What can we realistically offer providers? Specifically:
- Can external provider staff get secure access to their own view inside WYCA's Fabric/Power BI environment?
- Does that depend on WYCA's capacity size (F64 or above) or on each viewer having a Power BI Pro licence? Have you had a steer from Jamie or Kieran on which applies?
- If direct access isn't practical, what's the simplest fallback? A monthly PDF or export per provider? Could that be automated, and who would send it?
Our answer
Access
- Yes, technically feasible.
- Provider staff get secure access via Entra B2B guest accounts (they sign in with their own work account).
- Row-level security so each provider sees only its own learners.
- We'd need a list of users per provider, which does double duty: the guest invites and the row-level security mapping.
- Caveat: this needs WYCA to allow external guest access. Their Entra external-collaboration and Power BI tenant settings must permit it, so it is ultimately WYCA's decision to enable.
Licensing
- Free viewing depends on where the reports are hosted:
- On an F64 or larger capacity, provider viewers view free.
- On a smaller or shared capacity, each viewer needs a Power BI Pro licence.
- Action for me: check with Jamie whether the reporting workspace will sit on an F64 or larger capacity. [Requested from Jamie 25 Sept, awaiting reply.]
- Providers may already have Pro licences. If not, the cost is circa £10 per user per month.
Fallback — PDF (only needed if providers have no licensed access)
- With F64 or a Pro licence: no PDF needed. Providers get the live, interactive dashboard, filtered to their own learners by RLS.
- Without F64 or a Pro licence — the main answer is that WYCA handle it manually. A licensed WYCA user (needs one Power BI Pro licence) exports each provider's view to PDF and emails it out. This is the route WYCA suggested they would handle, and it fits the internal process they already run.
- Automation is possible, but sits outside our scope. It could be automated using the Power BI export API (which needs a Fabric or Premium capacity, not shared/Pro, plus a one-off build), but the tender specifically said providers would need licences, so building the automation to work around that really sits outside our scope.
Bottom line: without licences, the recommended route is WYCA distributing the PDFs manually, as they suggested. An automated export is technically possible but sits outside our scope.
Question 2
Refresh and timing
- How often will the data refresh? The slides currently say daily, but that's unconfirmed.
- What's your realistic go-live date for a working dashboard with real data? I've been saying December. Is that still right, given the WYCA account and workspace set-up (A16)?
- Could we show a first-pass version on dummy data at the provider sessions? Even a screenshot would help. When can the dummy data you need from Eddy and Fraser realistically be in place (A8)?
Our answer
Refresh
- Daily (a nightly refresh) is what I'd build to, and safe to confirm on the slides.
Data and environment
- There is only one environment, so dummy or real data isn't something I control. The dashboard shows whatever has been loaded into Bronze.
Blocking issue
- Jamie is loading the data into Bronze, test or real. Until it lands, I can't start.
Timing
- December is realistic for the full working dashboard.
- A first pass for the 13 & 15 October sessions needs access and data in Bronze by Monday 5 October.
- The fortnight from Monday 5 October is a window where I have a lot of availability, so with access and data in place I can build most of it then.
Question 3
What providers will see
The mock-up shows invitations, baseline and destination completions, matched pairs, response rate by course and level, responses by contact method, and before/after change in work, confidence and wellbeing. Could you sanity-check that it's achievable? In particular:
- Response by contact method (email/text link, WhatsApp/SMS, QR code, phone). WYCA were keen on this (A29). What field would you need from Snap to report it?
- Row-level security, so each provider sees only its own learners.
- Whether we apply a minimum group size, so small courses can't identify individuals. The tender set a minimum of 30 for provider-level reporting.
Our answer
Caveat for all of the below: everything in this section is subject to seeing the actual data we get back from Snap. Getting sight of that Snap output is key and urgent, as it underpins the whole of what providers will see.
Overall
- The mock-up (slide 10) is achievable. The headline tiles (learners invited, start surveys, destination surveys, matched pairs), response rate by course, start surveys by contact method, and the before and after measures all come from the survey and ILR data.
- Response rate by course needs the number of surveys sent out per course as the denominator, so we need a list of surveys issued from somewhere (most likely the Snap or IBP issuance side), tied to course. That course tie could come from the ILR or the issuance data, not the ILR alone.
Response by contact method (A29)
- Achievable. The panel splits the completed start surveys by the method each response came in through, and a response only ever arrives by one method, so it is a single, clean value.
- All we need is to identify that method. Two ways to get it:
- Stamp a hidden field on each survey on the way out, carrying the channel, so it returns on the response.
- Query the outgoing (sent) survey records for the method each was sent by, from Snap, or from IBP if they keep the log.
- Question for Eddy or Fraser: can we set a hidden field per channel on the way out, and does Snap hold the sent-survey method we can query, or would IBP provide it?
Row-level security
- Yes. Each provider sees only its own learners, using dynamic row-level security driven by a provider mapping. This matches the "your learners only" note on the slide.
Minimum group size
- Yes. I would apply the tender's minimum of 30, with smaller groups suppressed so individuals cannot be identified.
Question 4
Matching and data flow
We're now asking providers for the bare minimum, probably learner reference, name, email and mobile. The course and learner detail comes from WYCA's ILR/DfE data. Can you confirm:
- that the learner reference is enough to join survey responses to the ILR data in your model
- how QR-code responses (name, date of birth, postcode, learner number if known) get matched
- how the ILR course list gets back into Snap to pre-fill the destination survey. Does your pipeline supply it, or does IBP take an extract?
Our answer
Caveat for all of the below: I can set out the intended approach, but I cannot confirm the detail until I have seen the data in Bronze. As with Question 3, this is why getting data into Bronze is the key dependency.
Learner reference
- Yes, the learner reference should be enough to join survey responses to the ILR data, provided it is carried on the survey and returns on the response.
- One important point: the learner reference ties a response to the learner, not to a course, and a learner can be enrolled on more than one course. So a single survey response cannot be attributed to a specific course from the join alone.
- Course-level attribution only holds where the question is course-specific (for example the destination survey asks learners to confirm their courses and rate how much each one helped), or where the survey relates to a single course.
QR-code responses
- These have no personalised link, so there is no single key. We match on the fields provided (name, date of birth, postcode and provider, plus learner number if given) against the ILR.
- This is a fuzzy match across several columns, not an exact join. Each field is scored (approximate on name, tighter on date of birth and postcode) and the scores combine into an overall confidence score for each candidate match.
- We set a confidence threshold: above it we accept the match automatically, below it we hold the record back as unmatched or for review, so we avoid false matches.
- Confidence is lower than the personalised link route, which is why the in-class QR is run as a test. Date of birth is important for the match.
ILR course list into Snap
- To answer directly: IBP take the extract in. Our pipeline does not supply the course list to Snap.
- IBP hold all the data before we do, and load the learner and course list into Snap for issuance, as they hold the identifiable data.
- The flow is one way. My pipeline pulls the responses down through the Snap API and joins them to the ILR course and aim data in the model.
- No data travels back up from the reporting layer. We do not push anything up to Snap or IBP.