00:00:02 Introduction

Thank you, Fay, for the nice introduction. Today is the third webinar of our series, and we will focus on data and database management. In the next session, we will go through data analysis and reporting on the 5th of December.

More in detail, what we will see today includes how we can best manage different components of our database, meaning security, access, roles, responsibilities, monitoring the use of the database, and different workflows to facilitate data access for different roles. Then we will go through how we can facilitate data management when our data is within a system, or even when we have to migrate our data from an external system. Finally, we will have time for Q&A.

00:01:06 Case study recap

Setting the scene, let's look at reminders of our case study used in the past three sessions. In this case, we have a project implemented in Mauritania. The objective is to provide an established source of fruits and vegetables for Mauritanians. We achieve that via increasing productivity in different agricultural commodities and creating a more sustainable agricultural sector in the long term.

We have seen the detailed data model in the previous session. We have two different baskets of data: the reference or operational data, and the master data, meaning data collection forms. The reference data includes administrative areas, projects, activities, and indicators. On the part of the data collection forms, we have the beneficiaries, where we track basic information, which is associated with each farm. Then we have two different surveys: one based on the unit of the farm, and the second tracking information at the participant level.

00:03:06 Roles and responsibilities

We have defined several roles. The admin supervisors have the overview of the collection, are responsible for reporting, analysis, and database design, and have access across all data collection forms. Program managers need an overview of the data mostly for reporting purposes. MEAL officers validate different incoming surveys, manage the reference data, and have access across all data collection forms.

Field officers have the ability to view or edit different records across the collection forms. They have some restrictions, meaning filters on the data they can see based on the location they are working in. In some data collection forms, they can only see the actual data they have recorded or assigned to them.

These responsibilities correspond to specific data flows. The MEAL advisor is responsible for data analysis and has access to all collection forms. The project manager has an overview of the data and access only to the master data. The difference in access between the MEAL supervisor and the project manager is the level of permissions. The MEAL officers are mostly responsible for managing the reference data and validating surveys. Field officers are responsible for filling the forms, such as self-registration on the beneficiary level, and conducting different kinds of surveys.

00:06:10 Database management components

We have framed database management around four different components. The first is that we need to secure access, which is important for data privacy. This reflects within a specific system under user roles and permissions. We also enable specific features to prevent users from adding or editing records that we do not wish them to edit, such as locking records.

Second, we enable different workflows. For example, we might want a field officer to receive an email notification every time they have a new self-registration to facilitate their work. In ActivityInfo, automations are implemented via automation integration.

Third, we monitor the use of a database. We need to monitor who is doing what within the system, establishing key metrics on what information to monitor and how often.

Fourth, we need to facilitate access across different roles. We need to establish different views because people with different roles have different needs to access data quickly. We also need to consider the context, such as the need to translate the database to localize it and facilitate data collection.

00:08:32 Data security

Data security is core when managing beneficiary data. We work in the humanitarian and development sector where we have a responsibility for the data we manage. We need confidentiality, meaning we don't want data exposed to parties who do not need access, especially sensitive information. Only people that need it should have validation to access the data.

We also need to ensure integrity. This helps maintain high-quality data collection, ensuring that people who do not need to do specific actions do not have access, preventing unauthorized changes. Finally, availability ensures that different roles and staff have the data needed to perform their daily workload.

When we implement this within a database, it is done by role creation. We need to think about which data collection forms a specific role should have access to and whether they need to access all records or if we need to filter based on location or assignment. Additionally, we may need to restrict permissions for specific subsets of data, such as locking data collection for a past month once the reporting period is over.

00:11:59 Roles and permissions in ActivityInfo

Roles are a combination of granting resources and parameters. Resources include forms, folders, reports, and databases. Parameters act as filters, such as administrative areas, establishing conditions for access. Permissions reflect different responsibilities: viewing, adding, editing, deleting, and exporting records.

For reference data, administrative supervisors have all permissions. MEAL officers have basic permissions to manage reference data (edit, delete). Field officers and program managers have basic view permissions as they only need to see the data across forms.

For data collection forms, management supervisors have all permissions. MEAL officers have basic permissions to edit or confirm data. Field officers need to view, add, and edit, but usually not delete or export. Program managers need only to view records for audit purposes. We also apply parameters to field officers to restrict access to specific locations.

00:17:50 Demo: Views and locks

Let's look at the field officer view versus the program manager view. As a field officer, I have access to a subset of data. For example, under beneficiaries, I only see a specific list because I am assigned to a specific region.

As a program manager, I have access to different folders and can see all the data collection forms. I cannot delete data, but I have visibility. Additionally, I have access to review fields, which allow me to approve records.

Regarding locks, we can restrict editing for a specific subset of data, such as records from November. If we navigate to a beneficiary record from November, the edit button might be available, but the review button is deactivated if locked, or the entire record could be locked preventing edits. However, for a December record, data collection is still ongoing, so editing is permitted.

00:21:51 Workflows

Workflows help us optimize different tasks. They are usually triggered by a specific action within the database under specific conditions. For example, when someone adds a record on the beneficiary form, we can enable a notification that sends an email to the field officer. Another example is triggering an email notification for reviewed records to the M&E supervisor when a record is edited. Within ActivityInfo, we can enable different kinds of workflows, often associated with third-party integrations like Power Automate.

00:23:29 Monitoring the database

We monitor the database to optimize performance and usability, identifying users for specific forms and highlighting training needs. We also monitor to track actions, such as reverting a deletion. If an M&E officer deletes a record by mistake, this should be trackable and reversible.

For data security, we need to identify unauthorized actions. If roles change, we need to identify the alteration. For example, if an M&E officer identifies an issue with a beneficiary form, the solution comes from the audit log. In ActivityInfo, under database settings, we can filter the audit log for a specific form to see updates or deletions. We can then recover specific fields or records that were deleted by mistake.

00:25:48 Facilitating data access

We facilitate access through table view options, custom views, and translations. For an M&E officer who needs to navigate records, print for audit purposes, or confirm history, we can manage the table view. We can sort, filter by text or ID, and navigate to associated records (like individual surveys) without leaving the table view.

We can create different views for different needs. For instance, a "Review View" for M&E officers to validate data, a "Personal View" for quick checks, or a "General View" for consolidated information. Finally, we can translate the database. In our Mauritania case, French is the main translation. We can ensure all data fields and the system interface are available in the required language to facilitate field officers.

00:30:58 Data management and migration

We often need to manage information that is out of the database, implying migration. We must consider the data type, format, and storage. We need to match external data with the internal system structure. Before importing, we perform data quality checks to ensure no duplicates in the files and that formats (dates, select options) are consistent.

In our case study, we might have the first year of data in Excel files for beneficiaries and farms. Since beneficiaries are associated with farms, the farm cannot exist without linking to a beneficiary. When importing, we must include the parent ID (Beneficiary ID) in the farm table.

00:34:14 Managing data within the system

Once data is in the system, we can edit records to correct mistakes, use the importer to bulk update data, and use the duplicate scanner. Duplications are common. The system can detect duplicates based on predetermined fields and determine if it is an exact or fuzzy match.

In our example, we have beneficiaries collected across administrative areas. If beneficiaries move, we might run into duplicates. We enable a periodic review to check for duplicates based on name, sex, and date of birth. We can then decide to merge records or delete true duplicates.

00:36:16 Demo: Importing and deduplication

We use the importer to upload beneficiary data from an Excel sheet. We match the columns with ActivityInfo fields. Calculated fields like age are ignored during import. We can use unique key fields to identify existing records and update them or add new ones. After importing beneficiaries, we import the farm records, ensuring we map the "Parent" field to associate the farm with the correct beneficiary.

For deduplication, we run a check based on fields like sex and date of birth. We review the results to determine if they are true duplicates. If they are, we can delete one. If they are duplicates with different valid information, we can choose to merge them, selecting which values to keep from each record to create a single, correct entry.

00:40:40 Key messages and conclusion

It is crucial to consider different roles, responsibilities, and permissions. We need to facilitate users by identifying if additional features like views or translations are needed. Periodic reviews are important to ensure data is efficiently used and to identify training needs. Finally, consider the data management process, including how to process external data for import and setting up reviews for duplicate records.

In our next and final session on December 5th, we will explore how to design reports that enable timely monitoring and evaluation, bringing together the data structure and management we have discussed to answer critical questions in the MEAL plan.

00:43:47 Q&A

What about comments? It is important to have a field for comments so officers could have specific observations noted. Comments can be added as another field within ActivityInfo, such as a text field. This can be done during the database design phase. Comments on specific records can be added as data collection progresses. You can also have read-only comment fields for administration or specific comments dedicated to field staff.

Sometimes it is not duplication; it is telling more, and a dimension about the data. That is true. Qualitative data matters. We can find an exact match based on fields, but the context might differ. That is why there is always a review process. A human eye reviewing the qualitative information can confirm whether a system correctly marked records as duplicates or if it is a false duplicate. We do not choose to blindly deduplicate based only on specific fields.

What are the challenges in data management? Challenges often start with data cleaning, validation, and confirmation of duplicates. Consistency and data integrity track back to correct data. If data is not correct, valid decisions cannot be made. Other challenges include data sharing with different stakeholders and managing permissions to ensure confidentiality. We need a system that can accommodate both simple and complicated role configurations.