AuraQ has vast experience helping organisations find the gaps that can be filled to enhance their business. This could be to improve efficiency, integrate legacy systems or deliver new portals and strategic applications to create competitive advantage. Contact us to request a free, no obligation Gap Analysis.
Moving from bad habits in Excel to best practice in low-code
I like Excel…I mean I’m one of those weird people who really likes Excel. I have used it to do everything from building Tetris to creating tools which support business critical processes in FTSE100 companies…
I know, putting the words ‘Business Critical’ and ‘Excel’ in the same sentence will send shivers down the spines of many readers, and for good reason. As much as I love it, Excel has been used to create some horrific business processes down the years. The technical limitations, security issues, scalability problems etc. have all been documented a million times, however Excel still dominates the business landscape. This is because, despite these risks, it is such a flexible platform that whenever IT doesn’t have the resource to deliver what the business wants, business users can use Excel to fill the gaps. This flexibility is a double-edged sword that can encourage bad habits, which result in many of the problems associated with Excel. In this blog, I am going to explain what it is about Excel that enables bad habits, and how other tools, such as low-code platforms, provide a better environment for creating solutions without removing any of the flexibility.
With great power…
The reason I love Excel is the fact that not only is it a powerful and flexible platform, but it is still very accessible. Excel is ubiquitous. Almost every work PC in the world has it installed, and it’s the only software most people will ever use to manipulate data. Almost everyone who works in an office knows how to do basic tasks like summing columns or taking averages. Some people know how to do things like vlookups, pivot tables and conditional formatting. Then there are the Advanced users who use VBA and external data connections to create incredibly complex tools that border on being dedicated pieces of software.
The problem starts when you open Excel. It doesn’t know if you are a Basic or Advanced user. It also doesn’t know if you are doing a quick calculation, which you won’t save, or creating a large planning tool to track annual billion-dollar budgets. Everyone just gets the same white screen with empty squares to do with as you please. Having such a wide range of abilities using the same sandbox style platform is what allows bad spreadsheets to be created. These are often created by someone just throwing something together to help them do their day job, without the support of developers. I’ve seen this in every department from HR, finance, operations and commercial teams, but the most interesting came while I was auditing business jet emissions.
Every Excel geek’s worst nightmare
Private jets are often managed by the pilot on behalf of the owner. Pilots are clever people, but have spent years training to fly aircraft, not creating spreadsheets. Some were good at it, but most understandably just wanted to get something that worked and get back to flying. The below image isn’t a real flight log, but it shows many of the issues I have seen over the years in lots of different contexts.
This spreadsheet looks good if all you want to do is look at it on the screen or print it out. However, if you hand it to an Advanced Excel user and ask them to do any analysis or present the data in a different way, there are several things that make this difficult:
There is one common theme to all these issues – people try to store data in the way they want to view it. This is natural. When business users are creating solutions to business problems, they are always going to view the end product they want in their mind’s eye and work backwards from there. The problem is computers use data differently to the human brain and structuring the data for what works for people then limits what you can do with your own data as the process changes or scales up.
Whenever I have created solutions in Excel, I have always tried to separate the user from the data as much as possible, usually storing the data externally. There were a lot of tricks I used to do this, including embedding SQL queries, VBA userforms and pivot tables. Most Excel users don’t have the training or experience to use these tricks. However, the spreadsheets they build seem to work despite the above issues, so why would they change it? This is how Excel rewards bad habits. It’s only when the company grows and changes, and you start using the data for purposes you didn’t anticipate that these start to become a problem.
Doing it better with low-code
Mendix, a low-code development platform, is different. While it isn’t installed on almost everyone’s PC like Excel, it does offers organisations who are looking to do things better a way to navigate the issues highlighted above and improve the way in which they manage business processes and develop solutions. It protects users from the types of errors discussed by inherently separating the data from the interface. The user must create a Domain Model first, which defines the structure of the data, then they build Pages to interact with the information. While forcing the user into a specific design path sounds like a bad thing, in reality, it doesn’t limit the functionality they can build and is just stopping them from doing things they should never do anyway. Domain Models are made up of Entities for each table they require, with Attributes for each column and Associations between different Entities for related data. This sounds complex, but Mendix allows you to create them visually, so it’s very easy to see what is going on. For example, to recreate the Excel sheet, we could create an Entity like the image to the left:
The Flight Hours and Fuel Burns were calculated in simple formulae in Excel, they are calculated fields here, while all the other fields will require the application user to enter data manually in the Pages created later. I want international flights to be a different colour, but since I can’t do that manually on the Page as we did in Excel I know it will require an Attribute here. I could just make it a manual tick box, but as we have already created some calculated fields, and we are already entering the From/To, could we calculate this field too?
As shown in the right-hand side image, this would require a separate Entity for ‘Airport’, with a ‘Country Code’ Attribute. This replaces the text To/From fields with dropdown lists, reducing the chance of a typing error and also means you only need to enter the country for each Airport once (or even connect to an external source). You can also use this Entity for things in the future, such as linking it to ground handling costs or airport restrictions.
Since most users try to crowbar everything into a single table, this is a much less obvious solution in Excel. However, Mendix Studio Pro makes it easy and intuitive. New users are trained to think like this, so this is a much more natural solution to the problem.
Combining the data and interface
Once you have your data structure, it is easy to start building the interface by creating Pages. This is as simple as right clicking on an Entity, selecting ‘Generate Overview Pages’, and Mendix automatically creates the two Pages – one for creating/editing new flights and one for viewing a list of the flights.
You can create your own pages from scratch if you want to make something more complex or bespoke, but these pages are fully functional straight out of the box. So, apart from a few cosmetic tweaks (re-ordering the boxes and changing labels), the New/Edit flight page is exactly as Mendix produced.
You can create your own pages from scratch if you want to make something more complex or bespoke, but these pages are fully functional straight out of the box. So, apart from a few cosmetic tweaks (re-ordering the boxes and changing labels), the New/Edit flight page is exactly as Mendix produced.
The auto-generated flight lists page worked perfectly well out of the box, however I wanted to make it look as close to the Excel version as possible, so I needed to make a few more changes. This was no more complex than applying conditional formatting would be in Excel.
Although this approach sounds more rigid, it is not difficult to change the Domain Model after you have started building Pages, so your design can remain flexible, allowing you to take an agile approach to development. I wanted to show the process for creating solutions from scratch to show how easy it is. If you already have a spreadsheet, Mendix make it even easier as you can import an Excel file and it will automatically create an app, just be careful some of the design errors mentioned earlier don’t creep in when you do this.
Other things to consider when switching
In this this blog, I’ve demonstrated how easy it is to move a solution from Excel to Mendix, but this is a simplified example in isolation. There are several other factors when considering whether or not to introduce low-code at scale across organisation:
- Cost – although there is a cost in moving to Mendix, the ability to turnaround apps quicker means you not only save development costs/time but start gaining benefit/savings earlier. The cost of adopting Mendix therefore has a proven return on investment.
- Training Citizen Developers – although Mendix is very accessible and provides a simplified development environment for business users, it does take some time to get used to it. It’s therefore important that staff are given training, time and support to upskill themselves to get the most out of the platform. You need to ensure you train or hire suitable people.
- Professional Developers – The Mendix platform is designed for a variety of app developers, from business-oriented developers without any software development skills to very experienced technical developers with extensive software development and programming experience. For more complex solutions support may be required from professional developers who can extend solutions with custom code, consume data from multiple sources, build mobile apps, or leverage microservice architecture.
- Support – while business users can create apps, IT remain in control of what is deployed, allowing for support structures to be put in place to ensure applications are maintained over time.
- Infrastructure – Mendix provides a range of options for infrastructure, such as internal hosting, Mendix Cloud, 3rd party Cloud (e.g. Azure) to allow companies to ensure their data is stored in line with their individual requirements.
Conclusion
Without specific training in database structures, most people will try to store data in the same format they want to view it. While this is understandable, it is not best practice and Excel does little to discourage it. This is ultimately what leads to bad spreadsheets and prevents people from learning how to do things better.
What makes low-code truly different is the visual approach it takes to database design. This helps those without the training in computer science required to work with traditional technologies to create, modify and utilise more complex databases. It’s not a wizard or guided process that would stifle creativity – low-code is still a sandbox and you are free to create any end product you need, you are simply working in an environment which encourages you to build the foundations before you start decorating the bedroom.
Related Blogs
Drag