How to utilise open source MySQL database server in your ERP system
By John Chee
ii) TYPICAL SMB environment
Most small- and medium-sized businesses (SMBs) are equipped with computerised applications to run their day-to-day activities. The common modules are General Ledger, Accounts Receivables and Payables, Order Management and Inventory (trading stocks). Some SMBs have installed tier one systems which would easily cost them RM2 – RM 3 million.
In the normal daily operation, the (ERP) system is heavily worked and you can monitor that by looking at the CPU and memory usage in the server. This is because hundreds of invoices are churned out and you have 50 to 100 users accessing the system all over the branches in Malaysia and Singapore.
iii) BUSINESS NEEDS
Now the business heads want to look at their sales performance daily. They need to look at how well their agency products are performing so that they can take appropriate action without delay should they detect any weaknesses.
iv) TYPICAL SETUP OF A FMCG COMPANY
Let's look at the scenario of a FMCG (Fast Moving Consumer Goods) company. The profit centre structure looks like this:
- Business (Heavy Machinery, Bearings, Health Equipment, etc)
- Agency (Agency 1, Agency2, etc. This should be the principals who represent the products)
- Branch (Petaling Jaya, Penang, Ipoh, Johore, Kuching, Singapore, etc)
- Stock Keeping Unit (SKU) – 10-mm bearing, A200 Printer, etc)
- Total business units – 10
- Total SKU in inventory – 100,000 items
From the above profit centre structure, you need to run summarised reports by the followings:
- Business
The General Manager wants to know the sales of all businesses by business-agency-branch because he has to report to the Chief Operating Officer (CEO).
- The Business Unit manager wants to know the performance of this business unit only. He would like to see summary by agency-branch-SKU.
- Branch
As there is a head in every branch, the Branch Manager wants to know the performance of this branch only. He would like to see summary by branch-agency-SKU.
v) WHAT ARE THE PROBLEMS?
1. Your ERP system is running at optimum capacity. You are aware that the summary reports required daily by the business heads entails heavy CPU processing of data. To run all the reports on the ERP server will literally bring the server to its knees! And you know you cannot afford to this.
2. The company has spent a bomb on the ERP system. The management have told you that there is very little left in the IT budget.
vi) WHAT ARE THE CHALLENGES?
1. You would like to demonstrate that with little budget left, you are able to manage the IT department and you would do your best to meet the requirements of the business units.
vii) LET'S DO IT
1. Let's say the ERP system runs on an Oracle database (or any heavy-duty database) which sits on a Linux box.
2. Now purchase a new server and install Linux on it. Get Apache and MySQL up and running.
3. You know that your ERP system is working from 7.00 am to 9.00 pm. Allowing 2-3 hours of buffer, you know your system is quiet from 12.00 to 6.00 pm.
In your ERP server, write a PL/SQL program to get all the summarised data and placed them in a set of temporary tables. You need to write PL/SQL program because it's Oracle native database programming language and you can process data very much faster because all the actions are done on the server.
Now schedule the PL/SQL program to run at 12.00 midnight daily. Let's say you have estimated the duration to take about 2 hours.
4. Now go to the new Linux server where PHP and MySQL are installed. Write a PHP program to grab the data located in the temporary tables in the ERP server and dump them in MySQL databases.
In the Linux server, schedule the PHP program to run at 3.00 am. Let's say you have estimated the duration to take 1 hour to dump the data from Oracle database to MySQL database server.
5. By 6.00 am every morning, your data as requested by the business heads are ready in the new Linux server where MySQL is installed.
On the client, you may use any client tools for reporting. There are hundreds of tools in the market. In this example, I use PHP, Access and Visual Basic.
For PHP, you can write programs and the end users will access the reports through the internet browser.
For Access, you may use ODBC protocols to connect Access to MySQL database server. Then you can make use of Forms, Queries,Tables, etc, to generate the reports. Don't forget to make use of the Snapshot Viewer to send reports through email.
For Visual Basic, you will write the common client-server programs to create rich and attractive client interface to woo the business managers. Connect to MySQL database server using ODBC.
6. As a daily routine, in the ERP, you may wish to delete all the data in the temporary tables before running the PL/SQL program. Then in the MyQL database side, you may wish to all delete the records before appending new records to the tables.
viii) CONCLUSION
1. In the LET'S DO IT section, you can see that the ERP server is not burderned at all by the requirements of the business heads. The ERP server will continue to run happily processing day-to-day transactions.
2. The business managers will get all the reports they want. They can see summary reports, cross-tab reports and any personalised customised reports. Being non-IT savvy, the do not need to know about the technical stuff of open sources. Thanks to open source PHP and MySQL!
3. The management gives you a pat on the back. Now you feel like you're drifting in the air! You have held on to the IT fort and you have demonstrated that you can do more with very little IT budget.
4. The management has seen your potential. Now expect more challenges to come from the management.
Read more articles here
About John Chee
Find out more at http://nextlevel.com.my/tutor/johncws.
Copyright Notice
This article is copyrighted by John Chee. You are allowed to reprint
this article on your web site as long as you include the full article text, the
author profile information above and this Copyright & Reprint Notices.
|
Change Currency