We work with various IT systems every day and many a time we are faced with a situation where our machine takes an enormous amount of time. This blog describes some of the situations that we face and ways to get work done faster.
Making ERP systems work faster
It is unfortunate that most users in the corporate world think that making systems work faster is the job of IT. They do not realize that business requirements also play a crucial part in the entire scheme of things and so it is as much of a business problem as an IT problem.
1. Redesign query and report structures
Many ERPs allow users to design their own queries and reports by giving them an interface where they can select the right tables, fields and data. A few things to keep in mind when designing such reports.
● Make the right data selection
This appears an obvious case but is not necessarily followed. It is of course a no brainer that a data selection of 1 day will be faster than a data selection of 1 month. The concept can be extended to any number of situations.
Simpler the data selection and logics given, the faster is the report output. Sometimes it is better to get 2 to 3 reports which are simpler than a single “ideal” report which has everything.
● Choose the right sequence of search
A search sequence in a query or report can start searching based first based on sales date, then customer number and then product or vice versa. The sequence can make a huge difference in the time taken. Which sequence is correct? There is no one straight answer.
It all depends on the objective of the query, the structure and quantity of data, the nature of the primary key. Even for a custom report designed by IT, a manager not in IT can still help his IT counterpart in these queries with this knowledge.
● Pick the right fields from the right tables
A material master has far fewer records as compared to a Purchase Order book. When picking fields from a purchase order book, it makes sense to pick only fields which are unique to that order and order line. A material description field is best picked up from the material master and not from the order book to save report running time.
● Try a plain dump instead of a structured report
Today’s analytics are either in Excel or in tools such as Power BI, QlikView, Tableau, etc. People do not use reports directly from the system, but instead use this for further processing through these tools.
What does this imply? We do not require the descriptive headers and blank rows in our reports. These features were designed in another time in another context when users were probably getting report printouts to read. A “low-end” unattractive data dump with unintelligible field names in a CSV format might still do the trick in giving a much faster report. Besides it avoids pains in cleaning the data at a later stage.
So try out the various options provided. Excel output may or may not be the best option.
● Try to get the report out of the system in an automated manner
Some ERPs charge an enormous sum for building a bridge to get data out seamlessly. However, It might still be possible to take out text or CSV reports in an automated manner. There are solutions around the format if it in text or CSV or delimited or such structures. What is important is that the data is out in an automated manner for the team to start working on it.
Some ERPs provide a BI or Analytics tool with a connection at the back end directly with the system. This enables users to directly access the data and create custom reports which can be refreshed with fresh data at the click of a button. Explore getting access to these tools and try using them.
2. Plan reverse interfaces using manual updates
Most ERPs allow only structured reverse interfaces which are expensive. If this is enabled, its for the better. If it is not, here are the alternatives
● Use mass update features for updating masters
Most ERPs provide features like mass update or mass upload which master guardians can use to update their masters
● Use query update features for updating editable fields in records
Fields like planned delivery dates on purchase orders are editable manually by those having access in most ERPs. This implies that these fields can be changed with queries also if editing by queries is available as a feature.
3. Use spreadsheet templates for reconciliation with other systems
Most companies still do not use all modules of an ERP. This implies that there are bound to be other systems for specialized applications such as planning, reporting, masters, quality, etc.
When there is data movement from one system to the other, a reconciliation is usually required. This can be accomplished using Excel templates. It is better to plan for this in a systematic manner with expected objectives, periodicity of reconciliation, inputs and outputs. An ad hoc reconciliation is always painful.
A more detailed explanation on working with templates is given in a separate blog here.
Making Stand Alone Systems work faster
Systems often tend to hang or slow down when they are made to handle large amounts of data. This can be a frustrating experience and can significantly slow down operations.
1. Make systems work on demand
A simple way out of this situation is to choose to run specific programs at specific times. If a program takes too long to run for example, plan to run it at night when there is a lower load on the system.
For analytical tools, set a slower data processing frequency depending on the scale of data. Not all data that is large is updated all the time. So run the program to calculate only for the refreshed data.
Some tools have options to process data selectively and manually only on demand. These features can be used effectively for making the system more responsive. In Excel for example, there is the Pivot and the Manual Calculation features which can be used on demand.
2. Remove calculation load of the system
Re-examine the system logics to figure out why a particular action is taking so much time. Very often this is because the logic given is based on ease of writing and not necessarily based on speed of running the logic.
Consciously use and identify logics that are simple to run. Break down the overall working into such simple logics which are easy to understand and run. This may involve an increase in the amount of data created, but it may improve the system speed. With enough persistence, we can strike a balance where the system size is limited and speed is also good.
Sorting of data in combination with aggregation logics is also a frequently used method in order to reduce the overall calculation load of the system.
3. Reduce the lookups load of the system
As mentioned before, systems particularly tend to hang when the amount of data is large. One of the ways of making it work faster is to change the logic in such a way that the lookup processing load of the system comes down.
Watch out if the logic is such that the program repeats the same lookup multiple times. Many IT tools have an indexing feature that enables easy lookups because it avoids multiple searches from multiple lookups between the same tables. Indexing is a way of reducing the lookup load.
4. Remove repetitiveness of calculations
Similar to the principle of reducing the load of lookups is the principle of reducing load of calculations. If there is a large dump of data with classifications, a calculation involving all the rows will consume a lot of time, but may be unnecessary if summary statistics are required at a classification level.
Sometimes, the structure of the system forces the same calculation multiple times. Re-examine of the system structure needs to change to avoid multiple calculations
5. Design user interface for speed
Speed of a system is a function of the amount of data exchanged with the server. A screen packed with a large amount of data or heavy graphics would take time to load and give a slow user experience. If a user changes something, the amount of data that gets uploaded for getting saved also matters. A refresh can be designed as a Full or a Partial Refresh and this depends on the user interface.
Today the variety of interfaces on offer is large. Aesthetics, ease of use, long term requirements, speed, scalability and maintenance are some of the key factors which need to be taken into consideration. People factor is paramount here. For example, techy users may not care much for aesthetics so long as they get speed and functionality. For most users on the other hand, looks and easy readability is just as important. In all this, one needs to factor not just today’s technology, but consider the technological advances going forward so that systems don’t become obsolete. System design consultants have a key role here in thought leadership.
6. Use automation
Today’s technologies provide multiple options for automating tasks. Macros are custom programs that can be developed in EXCEL Visual Basic by anybody. Similarly we have such automation tools in several platforms. RPAs are independent tools to do just this. Some amount of coding knowledge is required. Excel provides a feature to record macros as well.
Automation is a great way of reducing file size and improving speed. In Excel, we recommend that you use readily available Macros.
The EML FORMULA macro offered by EML is an excellent way of improving speed and saving space. It is a re-usable macro that needs to be installed only once and can be applied multiple times across worksheets and workbooks. It basically enables you to save your calculations in Excel without maintaining formulae in all the cells.
Thus there are systematic methods by which systems can be made to run significantly faster. Solution architects often have to strike a balance between speed and functionality taking into account ongoing expected technological advances.
For help on making systems work faster, reach out to Easeylife at www.eml.net.in
Comments