data warehouse technical group - trustmark
TRANSCRIPT
trustmark.org.uk
Data WarehouseTechnical Group
28/11/2019
trustmark.org.uk
Agenda
• Resources• Considerations• Use case discussion
• Assessor• Coordinator• Ofgem impacts• Third party
• Future session / communications• AOB / Q & A• 1 – 2 -1 discussions (if time permits)
Introduction
Room available until 12:30
Fire Alarms: None ScheduledAssembly Point: Green sign outside main entrance
trustmark.org.uk
Github
Resources
• Sample project• Documentation• Link to Postman script
https://github.com/trustmark-2005-ltd/TrustMark.DataWarehouse.Api
trustmark.org.uk
Postman
Resources (cont.)
• Examples of the calls provided by the API• Autocomplete are mocked examples
https://www.getpostman.com/collections/64b2a1e75e1fcd7b65a3
Other support and familiarisation• https://www.trustmark.org.uk/support/dw/general• https://www.trustmark.org.uk/ourservices/data-warehouse
Environments• Sandbox - For testing API and integration• Production
trustmark.org.uk
WorkTypeId & Trade Codes
Resources (cont.)
WorkTypeId • Used to distinguish
measures installed under the same TradeCode
• When used, you do not need to provide details of the measure
WorkTypeId Work Type Measure Category Measure Type PAS Annex Standard ECO Measure name Trade Description ECO TrustMark
Trade Code
Financial
Protection DW-100 Energy Efficiency Cavity Wall Insulation Cavity wall insulation (0.040) B1 PAS 2030:2017 CWI_0.040 Cavity Wall Insulation [B1 - 2017] 3 90 CWI-01
DW-101 Energy Efficiency Cavity Wall Insulation Cavity wall insulation (0.040) B1 PAS 2030:2019 CWI_0.040 Cavity Wall Insulation [B1 - 2019] 3 91 CWI-01
DW-102 Energy Efficiency Cavity Wall Insulation Cavity wall insulation (0.033) B1 PAS 2030:2017 CWI_0.033 Cavity Wall Insulation [B1 - 2017] 3 90 CWI-01
DW-103 Energy Efficiency Cavity Wall Insulation Cavity wall insulation (0.033) B1 PAS 2030:2019 CWI_0.033 Cavity Wall Insulation [B1 - 2019] 3 91 CWI-01
DW-104 Energy Efficiency Cavity Wall Insulation Cavity wall insulation (0.027) B1 PAS 2030:2017 CWI_0.027 Cavity Wall Insulation [B1 - 2017] 3 90 CWI-01
DW-105 Energy Efficiency Cavity Wall Insulation Cavity wall insulation (0.027) B1 PAS 2030:2019 CWI_0.027 Cavity Wall Insulation [B1 - 2019] 3 91 CWI-01
DW-106 Energy Efficiency Cavity Wall Insulation Party cavity wall insulation B1 PAS 2030:2017 PWI_Cavity Cavity Wall Insulation [B1 - 2017] 3 90 CWI-02
DW-107 Energy Efficiency Cavity Wall Insulation Party cavity wall insulation B1 PAS 2030:2019 PWI_Cavity Cavity Wall Insulation [B1 - 2019] 3 91 CWI-02
DW-140 Energy Efficiency Loft Insulation Loft insulation where there is greater than 100mm pre-existing insulationB9 PAS 2030:2017 LI_greater100 Loft Insulation [B9 - 2017] 3 92
DW-141 Energy Efficiency Loft Insulation Loft insulation where there is greater than 100mm pre-existing insulationB9 PAS 2030:2019 LI_greater100 Loft Insulation [B9 - 2019] 3 93
DW-142 Energy Efficiency Loft Insulation Loft insulation where there is less than or equal to 100mm pre-existing insulationB9 PAS 2030:2017 LI_lessequal100 Loft Insulation [B9 - 2017] 3 92
DW-143 Energy Efficiency Loft Insulation Loft insulation where there is less than or equal to 100mm pre-existing insulationB9 PAS 2030:2019 LI_lessequal100 Loft Insulation [B9 - 2019] 3 93
DW-243 Insulation (Cavity / Internal / External) 0 52
DW-244 Loft Insulation Installers 0 54
DW-248 Energy Efficiency Retrofit Assessment Not included PAS2035 Retrofit Assessor 1 120
DW-249 Energy Efficiency Retrofit Coordination Not included PAS2035 Retrofit Coordinator 1 121
trustmark.org.uk
Considerations
API keys• Must be requested from [email protected]• There are different API keys for each environment.• If you have a portal login you can download your API
keys from the ‘My Account’ area.
Your setupWhen setting up your API Keys we will need to understand:• Whether you are a 3rd party lodging on behalf of
someone else?• Whether you need access to the DWH portal?• Whether Lodgement Certificates are to be sent?• How lodgement is to be paid for?• Whether lodgement is for PAS2030, PAS2035 or both?
Lodgement feesTwo typical models in place:1. The business uses the data warehouse to top up their balance, when a lodgement is made the
balance is reduced (businesses shave to opt into this)
2. The integrating party handles the payment with the business and pays TrustMark periodically
Approach• We have provided a suite of taxonomy lookups.• Using these will minimise rework for future changes.
There are different API keys for each environment.
trustmark.org.uk
Taxonomies
Considerations (cont.)
https://www.getpostman.com/collections/64b2a1e75e1fcd7b65a3
Documents• You must first complete the UploadFile call
to receive an UploadToken• You can then attach each document to the
lodgement using the Documents section of the lodgement.
trustmark.org.uk
Use case discussion
Retrofit Assessor Schemes1. How assessors will interact2. Assessment Format?
• Energy report• Condition report (one pdf. with multiple images)• Occupancy report
3. The relationship between your systems and the DW4. Retrofit Assessment Lodgement:
1. As stand alone2. When instigated by Retrofit Coordinator
Managing status of assessors:1. When an Energy Assessor is removed from
delivering EPCs, how can they be removed from TrustMark at same time?
2. When a Retrofit Assessor is removed from one scheme how can this be conveyed to other schemes with which they are registered?
Discussion points received - None
Discussion points for room
trustmark.org.uk
Use case discussion
Retrofit Coordinator Schemes / PAS 2035• How coordinators will interact with DWH?• Lodgement starts in DWH and RC invites participants?• The relationship between your systems and the DWH
Discussion points received - None
1. Managing the status - is this the same as that discussed for Assessors?
Discussion points for room
Id RMI Other EEMs PAS2030 PAS2035
clause
Title # of
documents
Advisor Assessor Designer Retrofit
Coordinator
Installer Evaluator
1 Advice report 0+ Y Y
2 [7.1] Risk Assessment 1+ Y Y
3 [6.2] Intended Outcomes 1 Y
4 [8] Assessment Report 1+ Y Y
5 [9] Retrofit Design 1+ Y Y
6 [9.2.2] Improvement Option Evaluation 1 Y
7 [9.2.10] Medium Term Low Carbon Plan 1 Y
8 [12.1] Handover documents for Client 1+ Y
9 Y [11.3] Claim of Compliance PAS2030 1+ Y Y
10 [6.1.4] Claim of Compliance PAS2035 1 Y
11 [14] Monitoring and Evaluation outcomes 1+ Y Y
12 o o Y [12.1.2] Guarantee 1+ Y Y
13 o o Other commisioning certificates 1+ Y Y
14 o o o o Other 1+ Y Y Y Y Y Y
15 o o o o Contract / Invoice 1+ Y
denotes
Mandatory Y denotes participantMandatory can be part of Assessment o uploaded if existsNon-Mandatory
Required for Work Type RoleRequired documents
trustmark.org.uk
Use case discussion
Ofgem – Measure lifecycle Key points
1. Each Measure has Unique_Measure_Reference (UMR) allocated
2. Once a lodgement is complete a CertificateID is generated
3. Score Monitoring elements (Heating source, Property Type, POPT, SWMR_RN) provided by Ofgem
4. Mismatches will be reported back to supplier by Ofgem
Measure gets installed
Lodged in DWH (API or manually)
Reported to supplier
Supplier notifies Ofgem
Ofgem conduct checks and then
checks against DWH
Ofgem Validation Criteria (Match/Mismatch)• Trustmark_Business_Licence Number• Measure_Type• Date_of_Completed_Installation• PAS edition • Trustmark_Lodged_CertificateID• Trustmark_Unique_Measure_Reference
trustmark.org.uk
Use case discussion
Ofgem / Suppliers
Discussion points received1. How can a supplier validate the information provided
by installer is correct?2. Address matching and remediation
Reported to supplier
Supplier notifies Ofgem
Ofgem conduct checks and then
checks against DWH
Evidence to supplier:
1. Where Installer platform used – confirmation provided via API
2. Where UI used, installer:• can download list of measures installed with
Certificate Number and UMR• can take screen shot confirmation
trustmark.org.uk
Use case discussionInstaller CSV Extract
Discussion points received1. How can a supplier validate the information provided
by installer is correct?2. Address matching and remediation
Address_name Address_number Address_line1 Address_city Address_postcode Measure Date_of_work installer_license umr Your_measure_reference ofgem_measure_name Standard PAS Annex Lodgement_status lodgement_date Lodged_by Certificate_id
Flat 3b The Square Basisngstoke RG21 4EB Cavity wall insulation (0.040) 03/08/2019 1624715 1dBPAMq6xa3 CWI_0.040 PAS 2030:2017 B1 Complete and paid 31/08/2019 1624412 5001
Flat 3b The Square Basisngstoke RG21 4EB
Loft insulation where there is
greater
than 100mm pre-existing
insulation 10/08/2019 1624412 1dBPAMq6xa4 LI_greater100 PAS 2030:2017 B9 Complete and paid 31/09/2019 1624412 5001
Flat 3c The Square Basisngstoke RG21 4EB
Loft insulation where there is
greater
than 100mm pre-existing
insulation 10/08/2019 1624412 1dBPAMq6xa9 LI_greater100 PAS 2030:2017 B9 Complete not paid 1624412
Flat 4a The Square Basisngstoke RG21 4EB
Loft insulation where there is
greater
than 100mm pre-existing
insulation 10/08/2019 1624412 1dBPAMq6xb1 LI_greater100 PAS 2030:2017 B9 Not_complete 1624412
99 Test Street TestTown TE55 7QQ Weed control 07/10/2019 1624412 2dBPAMq6xb2 Complete and paid 07/10/2019 1624412 5002
Screenshot of Lodgement
trustmark.org.uk
Any Other Business
Future Communications• How would you like us to communicate with you?• Do we need to create a FAQ guide for your installers?• Online community?