AHDB RAG analysis management solution with Power Platform and Microsoft Teams
Solution Summary
The Agriculture and Horticulture Development Board (AHDB) is a statutory levy board, funded by farmers, growers, and others in the supply chain to help the industry succeed in a rapidly changing world.
AHDB was looking to improve the efficiency and ease of the routine RAG (Red/Amber/Green) Analysis, performed every 6 months for their Research and Knowledge Exchange projects. There was an additional requirement for managing the collection of Data Quality assessments from the same projects.
AHDB wanted to combine the two workstreams into a unified solution to streamline the data collection processes, prevent data duplication, and reduce the amount of manual workload necessary to facilitate these processes.
Intelogy used Dataverse for Teams, introducing a structured, central data store with Microsoft Dataverse. Three canvas Power Apps were built to address varying audiences within AHDB who would require access to the solution in different roles. These canvas applications are supported by a series of Power Automate workflows, reducing the manual workload for process owners in monitoring completion status and sending reminder notifications. The Power BI Online Service was used for the visualisation of data and reporting, which was greatly enhanced due to the new central data store.
Customer Quote
“For myself, as a back-end user, the new RAG Reporting apps has automated the entire process. For the first time we have user friendly apps for both end users and back-end users. This means no more spreadsheets and multiple users can access the apps at any one time. It’s easy to use and less time is required by Research Managers to complete. We now have more meaningful data to interpret and better reporting capabilities by using Power BI, we can easily drill down into the details and look at specific areas of interest.”
– Michelle Quagliano, Technical Programme Support Team Manager, AHDB
Figure 1: High-level solution architecture diagram
Challenge
Once every six months, AHDB conduct a RAG Analysis for their active projects, assigning red, amber, or green ratings determined by answering a set of questions related to project delivery. Their previous process was prepared and completed in a centralised Microsoft Excel spreadsheet hosted in SharePoint Online. With over 250 active projects at any one time, a significant amount of manual effort was necessary to maintain data accuracy, and with no controlled data collection mechanism, completion of these RAG submissions were at risk of input error and potentially wrongly allocated RAG statuses. Furthermore, with every reporting cycle contained within a separate Excel file, it prevented the ability to easily monitor trends in projects and their RAG statuses over time.
The majority of projects also had a requirement for a Data Quality and Sensitivity Assessment to be completed if the project generated data. Like the RAG process, this was heavily based on Microsoft Excel, using a template to be completed by a project manager. Once complete, these Data Assessments were stored in their respective SharePoint team site for each project, which made it challenging to cross-analyse the quality and sensitivity of data generated by projects at an organisational level.
Solution Strategy
Based upon Intelogy’s evaluation of AHDB’s current processes, the Microsoft Power Platform was placed at the core of the overall solution design. The data involved in this process was highly structured and relational in nature, thus requiring a central storage location to facilitate rich and insightful reporting. AHDB’s overall business process is primarily aimed at project owners within the organisation, indicating an objective-driven, team-based environment.
Intelogy decided to use Dataverse for Teams as the foundation for record collection and storage, and the Power BI Online Service for the visualisation of this data.
A dedicated Microsoft Team was created, with a Power Platform environment being provisioned from within this team, creating the Dataverse for Teams platform to contain the relevant apps, workflows and data. Separate channels in Teams were introduced for each area of the project analysis process, providing established locations for accessing applications, reports and any supporting documents, guidance videos (hosted in Microsoft Stream) and on-going chat conversations relevant to the intended audience.
Canvas Power Apps
A series of canvas Power Apps were introduced for minimalistic but logical user experiences. All applications interacted with the underlying data store within the Dataverse for Teams environment. Access to these applications was permission scoped, ensuring appropriate personnel had access to the applications they need for their specific roles.
Three individual canvas apps were built to address the following areas:
• Management of active projects by Technical Programme Support Team
• RAG Analysis report submissions to be completed by Project Managers
• Data Quality and Sensitivity Risk Assessments to be created and updated by Project Managers
Project Management app
Intelogy developed a Canvas Power App to provide the ability for the Technical Programme Support Team to manage projects centrally, with the ability of assigning Project Owners, populating appropriate metadata to enrich the reporting experience and form the basis for the relational data model underpinning this solution. This application also includes the management of reporting periods, allowing for control of start and end dates of data collection, due dates for completion and the triggering of automated workflows to notify project owners and provision data for completion. The application is easily accessible via a Microsoft Teams tab found under the appropriate channel.
RAG Analysis app
The RAG Analysis Canvas Power App is designed for Project Managers to complete and view any Project RAG submissions for projects they own.
Figure 2: Illustration of Amber status conditional logic
This Power App uses a form to capture data using conditional logic to dynamically control the visibility of fields based on the user input.
The user experience was further enhanced by providing an indicator of the RAG status beneath the heading for a selected project and period, updating dynamically as the user moves through the submission, changing colours depending on the selections made, and once the submission is fully complete, determine the outcome of a RAG Analysis report automatically.
Data Quality and Sensitivity Assessments app
Intelogy developed a Canvas Power App to facilitate AHDB’s Data Quality and Sensitivity Assessment process which requires Project Owners to identify, record and mitigate risks to the quality and sensitivity of data associated with active projects. This implementation abstracted the previous questions and metrics to be stored within a purpose-built table in the Dataverse database. As the submission of this data was controlled and channelled through the front-end Power App experience, this allowed for advanced data validation against certain fields which was not previously possible, providing assurance that the data submitted during this assessment process is complete, valid, and complies with AHDB’s internal requirements. This also allowed for the implementation of real-time, responsive feedback, which involved the automatic calculation of values on the basis of other data submitted in the app to dynamically provide an up-to-date representation of the likely outcome of the submission given the provided data. Furthermore, given these values were calculated dynamically, it was possible to leverage this real-time status to invoke other functionality, such as inviting users to record planned mitigations if a project was calculated with a sufficiently high quality or impact risk score.
The app takes a project-first approach, presenting the user with a list of their active projects completed with a status indicator against each project to signal whether a data assessment has been submitted for the given project.
Whereas in the prior Microsoft Excel template it was necessary to manually populate the ‘Project Information’, manual input was eliminated by leveraging the relationship between data assessments and their associated projects in order to dynamically determine and resolve this information. Not only did this make the process easier for users but also reduced the need to duplicate information as well as preventing any issues caused by the incorrect transcription of this information. The application seamlessly handles the entire Data Assessment process, from data collection to data verification all the way to data submission and storing the data in a format that is highly accessible for reporting and auditing purposes.
Power BI Reports
The Power BI Online Service was the tool of choice for providing an accessible and secure reporting environment which surfaced the data stored in Microsoft Dataverse and subsequently enabled use of the powerful data transformation and visualization capabilities of Power BI upon it. Intelogy constructed two distinct reports for the RAG Analysis and Data Quality and Sensitivity Assessment processes respectively.
The RAG Analysis report surfaces the red, amber, and green risk ratings over the lifecycle of a project as well as identifying and presenting the factors that contribute towards these outcomes. The data architecture extends the scope of possible data interrogation, for instance, exploring any impacts a project type, contractor, project value or project owner may have on a RAG Analysis outcome., This has helped unlock additional insights into existing data, whilst simultaneously making these insights significantly more accessible for reporting purposes, such as monitoring and tracking trends over time.
Figure 3: Illustration of Power BI report
The Data Assessment report focuses on representing the likeliness and impact of data quality and sensitivity submissions. This information is available both on a granular a project-by-project basis, but also provides the option to view these metrics against either all projects or all projects as defined by another category such as the business sector to which they pertain. This empowers AHDB staff to use this report both to attain insights against specific projects or categories of projects, or gain an understanding of trends and patterns at the organisational level via the centralised, relational data storage structure offered by Microsoft Dataverse. To make these reports quick to access for all members of AHDB, these reports were pinned as tabs under the relevant channels in Microsoft Teams, allowing users to quickly dive straight into data to help inform their daily workload.
Results
Intelogy created an intuitive, flexible and efficient solution for AHDB that ensures all necessary control of access and governance. The new streamlined experience not only serves to ease the amount of manual work required from Project Owners but also offers unprecedented capabilities such as enforcement of data integrity and direct-from-source reporting.
It is now much easier and less time-consuming for administrators of the AHDB Project Analysis Team to view and maintain all active projects, because they are available in one central location.
The supporting Power Automate workflows have reduced the manual workload required to prepare RAG Analysis reports and because of the automatic notification system, Project Owners are also automatically notified of deadlines to ensure reports are completed before they are officially due. The canvas Power App that supports the Data Quality and Sensitivity Assessments facilitates a cohesive and simple data entry user experience through the implemented navigation steps and visual cues, while ensuring data integrity via conditional logic and mandatory fields. The data collected provides a much richer reporting experience due to the central data storage, resulting in more flexibility for analysing projects at a top-level or on an individual basis.
Because the reporting process is hosted in the Power BI Online Service, it enables easy access on any device and/or allows for native integrations into Microsoft 365 services such as SharePoint and Microsoft Teams.
