Large Corporate Apps – Let’s look what’s required
A look down memory lane
A multi-year project started out as a few excel sheets. How does one take an idea and make it a large corporate app? First, the reason I was asked to design this was the IT department could not roll it out in a reasonable time frame.
Our group specializes in getting a working prototype working. With proper backup and double/triple data checks you CAN design and operate a solution at the same time. THEY SAID GO!
So – join me for a journey of how we co-designed with the customer … a very complex application that replaced about 5 spreadsheets and how we thought about things. It gives you insight into how we work and make decisions and put you the customer in the driver’s seat.
The project started about 3 years ago and it was a phased approach – a minimal viable product, then we added bells and whistles, then we really made it efficient and super easy to use. These parts took about 50%, 25% and 25% of the budget thereby not spending time and resources all at once.
Because we have done this many times before, a proper database, backup considerations from day one, picking up to date tech/framework, enter and exit technologies (import/export/sources) all had to be chosen.
Too many choices. Where to start
Where do we start? Would excel be the right choice – do we just add macros? What is the workforce using this? Are they spread out? In once office? Do we make a FileMaker database and simple screens? Excel was handling it right … kind of?
What problems need to be solved? In this case – many. Multiple concurrent users, no more broken formulas, speed of final results, accuracy and consistency of these results, privacy of certain data (now they can have labour rates for real people not just “a senior engineer” but “Bob the contract engineering specialist”) – you can’t do that in Excel as passwords are easily cracked) and live up to date update of underlying rates.
Excel as a tool was a stretch. If someone messed up a formal or copy/pasted as values, some time it would be months before someone noticed the error and how values would NOT propagate (cause they were no long er a formula). Next are bugs or over size sheets – this sheet was passed around from department to department. Even though it was on SharePoint, only so many people could “attack the input values” at a time.
A system that could handle a worldwide simultaneous “attack on data entry” was needed. We could not afford to pass this around from group to group and wait in between but all groups had to be able to edit their independent parts at the same time. Just before the mass data entry – an admin type (often it was one of the departments) would copy an existing project, set the major boiler plate pieces of info, update the basic employee types, press a button to get up to date employee rates/info/manager and then tell give the “all clear” to the other department leads to enter the hours, types of people and deliverables. Instant estimate/proposal numbers and reports.
Core ... the database
The database is the first step. One ‘oops’ was not realizing how much data was on the screen so it was a blessing that we used SQL Server as its easy to optimize a quick linked query. Using SQL Server, we could fetch more data on the fly as the user needed to see more and more (auto-load).
The database is more than just a place to store data. How fast can you retrieve the data? Can it be morphed and joined to related data … QUICKLY? Can it do some of the heavy lifting meaning – can we send it commands and have it carry out a lot of work and give back a near-perfect answer that simply needs to be dumped on to the screen? SQL Server (and other SQL engines) can easily handle this. This leaves the middle server free to manage security and coordination and not talk to the far-away SQL server (the SQL server was on its own server farm in this case). It also left the browser coding to not have to do much heavy lifting and freed it up to show the data quick and clean and instant. The database is step 1 in our design world.
Final choice for the other bits
Final decision – a web app. One that would allow fast custom data entry. We would need Excel Imports and exports on every tabular screen. It had to be secure and depending on your allowed level of access, behave differently. So we chose a custom web app with a robust database and a reliable back end (the part that does the work between the web pages and the database and other tools). We need very tabular and complex acting (not looking) reports so we chose SSRS – a very old and still used/loved platform. No we didn’t use the pretty PowerBI – read further as to why.
Tech for the web front facing part
Modern apps use what is called “a one page app” that dynamically changes PART of the screen at a time and NOT the WHOLE screen – a much smoother and natural process. So we chose a ‘framework’ and a template that was visually appealing and filled in the blank areas with out custom application. To get geeky, the framework was Vue and we chose it because it seemed to want to rapidly take over as the main framework. We have since learned that it was a good decision but React although slowing picked up pace. They are VERY similar in what they offer.
Back end or coordination tool
As for the back end – the hidden part that the front Vue app talks to – we chose c#. All security front and back are handled by the corporate system we already had offloading all that worry. All we had to do is ensure every request from the web was stopped by these checks. Hackers who try the back end also were stopped.
Time savers
Getting started from scratch – a “click to start a new project” is a terrible approach so we decided to have a copy-project feature. We would then clear and mark this copied project as a master. When we copy from a master project it would clear all the major details cleared or ‘reset’ while keep the boiler plate information. WHAT A TIME SAVER!
Ease of use
Getting data in and out. We made it easier for one discipline of data (eg civil department) to export their whole set of data, clear people’s names and import it as the piping department. We allowed certain shared items (like meetings) to be linked so we didn’t double dip in estimated hours. Again a time-saver
Every screen has an export to excel and reimport the same list. Even though we had a lot of fill-down and auto-fill features, some lists were hundreds of lines long and to use Excel’s formulas etc allowed a quick ‘export, bulk edit’ then it would reimport right over top of the old records.
When it came to employee information and details, Oracle had all that information perfectly up to date, so with a press of a button , it would dig into the Oracle Service and process the data automagically.
Learning and making it more efficient
Near the beginning of the article – you can see we didn’t expect some projects to be as big as they were. So if the project had a certain number of people (P) and deliverables (D) then you would have P * D * 10 items per screen. Well this was 1000’s of on screen elements for this one project. Well we had the correct tools in place – with a little analysis, the database queries were optimized, less data was fetched. The real issue was the number of items on the screen – so we intelligently unloaded and pre-loaded data on the fly as the user scrolled. So the main issue was the web browser’s ability to keep up – not in the background – but the screen elements. Now we know and were able to make a product that behaved over 500 screen elements. Under it – we just don’t optimize – its fast enough and the user’s reactions is what is holding up the show 😉 .
Many other items were added as a result of the user’s feedback – so always allow the budget of your project to reflect that. The people putting forth the initial product and the people how use it have very different focuses – both often correct. Because this was phased, extra budget the last year was created for some great ideas from the “factory floor” users. Happy people mean a better adoption of your web-app investment. The ROI is what’s important.
Reporting
As for data exporting – nothing handles tabular reports better than SSRS (a Microsoft product not many talk about). Its… perfect and it natively has Excel export etc. so it looks nice, is easy to use, has the data quick at anyone’s finger tips. We did NOT want PowerBI as the reports that were generated COULD have been done with a web page – but when SSRS is so perfect and this corporate network has licenses – why spend more money? PowerBI is … somewhat live, every so many minutes, if the servers aren’t busy. SSRS was instant.
Wrap up
Hopefully, you can get a feeling of what we try to think of when developing large applications. If you want people to use happily, it needs to have the right ingredients. Because this workforce is a global one, we put in as a web app on internal servers. It fit perfect.