I want to create an application that allows clients to edit data that sits behind a power BI report. The data involves multiple tables and some of them can have up to 1 million rows.
The function of the application would be fairly more complex than anything I’ve built in power BI or Python (my main tools). It would need to have the ability to select a group of rows from the table, based on inputs from the client, and then allow the client to change the value in a specific column. Further complexities would be allowing the client to see how each change affects the outcome of the whole table (parts of the table would have to be recalculated based on this change) but that’s further down the road.
What tool should I use to build this application?
I am unfamiliar with Power Apps but I’ve seen there is a hard row limit that is incompatible with the amount of data I would have to put into it.
As a solution architect who has built numerous data-oriented business systems over 30+ years, I can state that the first order of business is to determine infrastructure, management, and usage requirements such as:
What type of database/data storage mechanism is being used, and where is it located? For example, is it SQL Server on-prem, an Excel file on the network or cloud drive, a system or database in the cloud, etc.? If it’s in the cloud, how is it accessed from a remote/separate application?
What kind of security already exists, and what security do you need to implement for client access to the data and access to different functions within your desired application? This includes authentication (who are you) as well as authorization (what can you access) requirements.
How many clients will need to access to the application? How many will need to be concurrent? This can have a huge impact on licensing and performance, as well as security.
Where do you expect to actually run the application (although this is a bit conceptual initially)? This could also be referred to as “hosting” the application.
How do you expect clients to access the application, for both physical device and UI format? For example, PC/laptop vs mobile (or all), browser vs mobile app, remote access vs on-prem, etc.
After these questions are answered, you can get into deeper discussions and research about what kind of software can be used or created. This is a HUGE topic area, so addressing the above bullets first will help immensely.
Depending on your needs, you may get away with using something existing such as Azure Data Studio or SQL Server Enterprise Manager. I have also used MS Access very successfully as a front end for on-prem SQL Server databases for end user ad hoc queries and data updates, and I believe it has cloud-oriented execution and connection capabilities as well.
Or you may need a front-end web app framework with data access and security capabilities to create a custom software solution, which could involve multiple tools/languages/frameworks since many front-end frameworks only handle the UI. Data access and security issues will bite you badly if they are not a top priority in your solution design, especially if going the custom route.
We do have Power Apps and Power Automate solutions at my current employer, but the solutions created are focused on things like digital forms with approval workflows. I’m not sure that Power Apps can handle your overall requirements, but assuming that it is still an option after you answer the initial questions you could always look into it. As with all software, be advised that additional licensing may be needed to do what you want (per-user licensing, Premium connector licenses, etc).
The final advice I will provide is that the simplest solution that meets the requirements is always the best solution. The simple reason: it is always the least expensive, least complicated, and most adaptable.