How to approach solving a complex problem? Is there a simple way?
In this article, I am sharing one simple method that I learned while working on a data analytics requirement, which can be applied to solve pretty much all complex problems.
A bit of context and problem definition
A few years ago, I had recently started working on a project for one of our new clients. The client had implemented ERP in the Oracle EBS suite recently and at the moment they were undergoing a business transformation.
In this context, they required some data crunching to be done in a quick time to find out business insights on various businesses so management could decide on several next steps.
I didn’t know much about the business and functionalities implemented, however as a software developer, I had worked on Oracle Ebusiness Suite for the past six years so I was comfortable working on such requirements.
To give an example, this was one requirement regarding the client’s trial order business that I got in the beginning. Our client would give some pieces of equipment to their clients on a trial basis for free, hoping that after the trial period clients will convert into actual sales.
The requirement for me was to do some analytics — how many trial orders were placed by the top ten customers in the past year and how many of them were converted to real orders within three months of placing them, reasons for non-conversion, what can I find out when there was faster conversion, etc.
This would then allow the management to find patterns and come up with ideas to increase the trial conversion rates to increase the revenue. This was a onetime activity.
The solution and the outcome
When I looked at the problem, it looked so simple. The solution I came up with was to write an SQL query to extract the data from Oracle DB and do analysis further.
Writing the SQL query was easy. You just need to identify the tables that store this information, identify the primary and foreign keys, and join them with a where clause with all the filter criteria each record needs to satisfy, optimize for excellent performance, and execute it. There you go, you get results.
I wrote the SQL query. I had written hundreds of them prior to it, so I knew the nuances involved even in simple queries, and I took care of all of that. I ensured the optimizer used the indexes for better performance, etc.
But when I executed the query in TOAD, the query didn’t result in any records. In fact, query execution didn’t complete. It took time; the performance was poor, and I just had to wait. I waited for a few mins, then a few more mins, then a few more. The result would not come in.
I lost patience after a while and canceled the query and tried tweaking a bit. I reviewed the query again to make sure everything was right, re-executed it. Still, I could not get the results.
Since I was new to the project, I didn’t know the size of the database, didn’t know much about the business scenario, how many records it had to process. That’s what happens when you work for a new client, but I needed the result in quick time. I had just reviewed it technically, and I was sure I was not missing anything. The execution plan was looking good, however, the query kept on executing and wouldn’t finish.
This was an onetime data extraction requirement, and they needed quickly and I was getting late. I tried for three-four hours and I still could not complete it. I struggled.
I reviewed the query with one of my colleagues who was working on the project for a little more time than me. He also said that the query seemed to be correct. It should have worked.
I reviewed from all angles, and it looked fine. Probably, the data it had to fetch was so much, that’s why the performance was poor. I tried rewriting the query a few times, but I still didn’t get the data till the end of the day.
#software-development #software-engineering #problem-solving #technology #data #data analysis