Collecting additional data for business intelligence purposes has always been a challenge. While the majority of business data is efficiently captured and processed through large enterprise-grade applications (such as ERP or CRM systems), there are inevitably tasks that require capturing extra data for reporting.

Here are some real examples of such datasets, which I have faced in the past:

• Reporting-Specific Categorisation: Adding specialised tags, classifications, or groupings that are not used in the source system but required for advanced reporting.
• External Party Data: Data provided by third parties, partners, or vendors who, for security or logistical reasons, do not have direct access to the organisation’s primary applications.
• Anonymised Sensitive Data: High-level aggregation of the data, initially sensitive and accessible only to a limited group within the organisation, and not a part of the data warehouse collection for security reasons.  
• Niche Data Entry Forms: Customised ad-hoc data forms or workflows which could not be added to the central enterprise application, usually due to lack of quick and efficient maintenance.

When the data doesn’t collect itself

These extra datasets are usually only required either for reporting on a specific matter or for a specific management level only, so the wider organisation – and specifically the IT department – tends to treat this task as a whim of the business intelligence team. Therefore, it is often that the BI team is left to its own toolsets to build a means to collect this additional data, and to make this experience as painless as possible for users.

Data engineers don’t typically have strong application or web development skills. I was once tasked with creating such a form with ASP.NET as I had a brief experience with it before I became a data engineer. I was actually quite surprised when it actually worked as intended for users! I then left that workplace soon after the release, so to this day I’m still wondering whether it was compliant with the organisation’s security standards and if it outlived my time there for long?

The toolset data engineers usually have on hand are related to the data transformations and databases. One of the common ways of collecting data from users is via spreadsheets. Users copy the updated files into an agreed folder, and the ETL pipeline uploads them into the data warehouse. This process is usually prone to failures as users keep surprising us with reorganising the columns, using wrong data types (e.g. adding comments to numeric fields), special characters etc. Without active data validation in place, this method of extra data collection usually doesn’t work well.

Oracle has a good tool for creating forms for data collection, called Apex. Data collected via these forms is written directly into the database, and security management is made easy based on the database access rights.

Similarly, Snowflake data platform adopted Streamlit, a python library for simple data input and output. It is possible to create an input form having only very basic python programming experience.

Using PowerApps for collecting data

Most of our customers already use Microsoft 365, so PowerApps is available for them, reducing the complexity of authentication and security settings by using AD-based logins. PowerApps is a powerful, low-code solution for creating small-sized applications, like data collection forms with active validation.

PowerApps combines visual development with a functional language rather than a programming language. Paradoxically, this makes development easier and harder simultaneously. On one hand, it is easy to achieve the basic functionality, like standard UI elements: labels, text boxes, buttons, and dropdowns; and implement basic data verification. On another hand, implementing more complex logic becomes a bit of a struggle. However, that’s a classic 80/20 situation.

The presentation view is primarily optimised for mobile device screens, which is excellent when a mobile-first approach is required. However, for the data collection applications we build, our customers require mostly the desktop version. Adjusting the UI to look professional and utilise the available space efficiently on a large desktop monitor was surprisingly difficult. This made us be creative with containers to manage the layout. However, relying on containers inherently limits UI flexibility. Moving or adjusting a single element can sometimes have a cascade effect, unexpectedly ruining the layout of the entire form: an experience that could be compared to the similar formatting issues in Microsoft Word.

PowerApps allow multiple developers working on the same application simultaneously, and all the changes are visible instantaneously. However, with limited version control capability, all changes require to be well coordinated within the team.

A significant technical limitation we faced in our most recent project is the data load limit, capped at 2,000 records. While this threshold sounds fine for a small application, in our case a lookup table had slightly over 2,300 records. You might argue that selecting a value from such a long list is not a common user experience anyway (in our case users could rely on filtering and search functionality), the limitation still had to be addressed. Overcoming this required creative workarounds, but in the end it worked fine.

Usually for such projects we use SharePoint lists as data storage. They are easy to set up and make secure, they have their own data validation and lookups, they work perfectly with PowerApps and it’s really easy either to load data from them into the data warehouse or use them as a source for a PowerBI report directly.

Overall I found PowerApps a highly valuable tool for a quick but reliable additional data collection challenge, offering seamless security integration and low-code development.

Our team now has a range of experience with building such applications for multiple clients, making a positive change in their processes efficiently.