Using AI to analyse large datasets and improve decision-making

In my role as a Systems Manager in Technology Services within the Properties & Services Division, I recently worked on analysing UCT’s Installed Devices export to produce a Legacy Equipment Report for the university’s access-control modernisation programme.
The task involved identifying:

• legacy reader controllers
• legacy readers
• required upgrades
• building sites most at risk
• data inconsistencies within the dataset

This work formed part of the long-term modernisation planning process that emerged from a strategic workshop with the external security systems partner.

Because the dataset contained more than 2,000 records, manually processing and classifying the information would have been extremely time-consuming.

The challenge was not only the size of the dataset, but also the complexity of the data itself. Some of the information had formatting inconsistencies, including missing leading zeros in Fixed Address fields (a device identifier used to uniquely identify controllers/readers), scientific notation issues (where Excel converts long numbers into shortened formats like 1.23E+10), and mixed formatting that affected classification accuracy.

At the same time, the classification rules discussed during the workshop needed to be applied consistently across the entire dataset to support reliable upgrade planning.

I used Microsoft Copilot (M365 Copilot) to support the analysis process.

First, I provided the AI with contextual information by summarising the workshop discussions and explaining the logic used to identify legacy versus newer controllers and readers. I then uploaded the Installed Devices export and asked Copilot to clean and analyse the dataset.

The AI helped:

• correct corrupted Fixed Addresses
• restore missing leading zeros
• classify devices according to workshop logic
• identify data anomalies
• generate a cleaned Excel file with additional reporting columns and building (EC3) summaries
   

The output included fields such as:

• FixedAddressClean
• VersionNum
• ControllerStatus
• ReaderStatus
• RequiredAction
• Building summaries
• lists of data inconsistencies requiring review

I then used the validated dataset to support the external security systems partner’s upgrade planning process.

Beyond the primary technical use case, I also used AI in smaller operational workflows such as drafting supplier communications, preparing workshop follow-up emails, analysing attachments and summary documents, drafting internal requests, and creating reusable communication templates. 

These smaller day-to-day tasks demonstrated how AI could support both technical analysis and operational coordination within the division.

The prompt I gave Copilot was:

• Step 1: “Please analyse the attached discussion document.”
• Step 2: Identify classification rules as per the discussion document.”
• Step 3: Find attached ‘installed devices spreadsheet’ and clean dataset (fix formatting issues).”
• Step 4: “Apply classification logic to the spreadsheet. Step 5: Generate report with new fields.”
   

I used a progressive prompting approach in stages, starting with contextual prompting to summarise the workshop discussions with the external security systems partner and the logic used to identify legacy hardware.

I first asked Copilot to deeply analyse the workshop discussion documents and understand the operational rules being used to distinguish between old and new controllers/readers.

From there, I moved into data-quality prompting by asking the AI to detect and correct issues such as missing leading zeros, scientific notation corruption, mixed formatting, and non-hex (invalid characters in codes that should only contain numbers and letters A–F) characters within the Installed Devices export.

Once the dataset had been cleaned, I used classification prompting to instruct Copilot to apply the workshop logic consistently across the data by generating categories such as ControllerStatus, ReaderStatus, RequiredAction, and building name grouping summaries.

Finally, I used output prompting to request a cleaned Excel report with additional reporting columns, anomaly summaries, and decision-ready outputs that could be shared with stakeholders.

Before distributing the outputs, I manually verified the classifications and checked the AI-generated calculations and cleaned data against the original dataset.

Where issues could not be fully resolved — particularly around scientific notation corruption — the AI flagged these records separately in a “Data Issues” sheet for manual review.

I also reviewed the logic applied to ensure it aligned correctly with the workshop discussions and operational requirements.

The dataset contained no personal information, staff identifiers, or student data. It consisted only of technical device metadata such as controller IDs, firmware versions, and Fixed Address strings. No policy risks were triggered due to the technical nature of the data, however careful validation and responsible-use practices were still applied.

Even so, I followed responsible-use practices by:

• confirming the absence of personal information
• manually validating outputs
• disclosing internally that AI was used during the analysis process

The work aligned with POPIA, UCT Responsible Use of AI guidance, and the AI Literacy guidelines.

One of the biggest benefits was the amount of time saved. A multi-day manual task was reduced to minutes.

The AI also handled rule-based classification consistently across thousands of records and corrected a large number of formatting issues introduced through Excel processing. This produced a clear, decision-ready output that could be used directly for upgrade planning.

The methodology is also reusable, meaning the same approach can be applied monthly, by building, or for future reporting cycles.

Beyond the primary technical analysis work completed in Microsoft Copilot, I also used AI tools for smaller communication tasks such as:

• drafting professional supplier emails
• translating Portuguese communications
• summarising workshop discussions
• preparing stakeholder updates
• simplifying technical explanations for non-technical colleagues

These smaller workflows helped improve communication consistency and reduced time spent on repetitive drafting tasks.

I also used Gemini for shorter exploratory tasks such as refining role titles, comparing role distinctions, generating draft KPIs for technical job descriptions, and creating operational troubleshooting guides.

Human oversight remained essential throughout the process. AI could apply rules quickly and consistently, but it still required clear instructions, contextual understanding, and manual validation.

The quality of the outputs depended heavily on the clarity of the prompts and my understanding of the operational logic behind the classification process.

I also needed to verify anomalies manually where the underlying data corruption (scientific notation) could not be fully reversed automatically; AI flagged these in a “Data Issues” sheet for manual review.

The AI-generated spreadsheet was exported into a shared OneDrive and SharePoint environment and integrated into the broader access-control modernisation planning process.

The outputs helped support more focused meetings between internal stakeholders, the security and building management company, Risk Services, Operations, and Maintenance teams, while also improving the quality and consistency of reporting.

More broadly, AI has become integrated into several parts of my operational workflow, particularly in technical analysis, communication drafting, reporting support, and document summarisation.

I would avoid using AI where datasets contain sensitive personal information, confidential records, or restricted institutional data. Human review is also essential in situations where operational decisions could be affected by incorrect classifications or incomplete outputs.

I treat AI as an assistant rather than an authority.

Start with a clear prompt and explain the rules or logic you want applied. Do not assume the AI already understands your operational context or technical domain.

Always verify outputs carefully to ensure AI calculations match the source dataset, and especially where classifications, or reporting decisions are involved. It is also important to avoid uploading personal or sensitive information and to follow UCT’s responsible-use and privacy guidance.

One of the most useful practices has been saving effective prompts for reuse. Over time, these prompts become part of your internal Standard Operating Procedures.

PASS staff can apply similar approaches in their own work, for example:

• Upload Excel datasets and ask AI to clean or summarise trends
• Use AI to standardise inconsistent data
• Generate reports or summaries automatically
• Draft emails, meeting notes, or internal requests
• Translate or simplify technical content

This experience demonstrates that AI is most effective when combined with human expertise, enabling staff to work more efficiently, improve decision-making, and focus on higher-value tasks.