The sticky notes you find around Railinc tell stories about our culture and how we develop our products; how we learn from and work with our customers; and how we meet the technology needs of the freight rail industry. A regular feature on the Railinc Tracks blog, Found on a Sticky Note takes a look at an individual sticky note and provides insights into who we are and what we do.
We deal with big data all the time at Railinc. But one challenge of working with massive data sets is pulling only the information our customers need. When it comes to big data, more isn't always better.
And searching big data sets isn't easy, either. It requires query processes that enable the parsing of billions, or even trillions, of data records quickly to deliver exactly what the customer requests.
Until recently, our Car Hire Rate Negotiation Self-Service (CHRNSS, pronounced "churns") application was mining too much data as it delivered results to customers. The result: CHRNSS was churning, loading too slowly.
This Found on a Sticky Note looks at work we've done to ensure CHRNSS searches and pulls only what's needed, reducing the time it takes for the application to load and to return data after a query.
CHRNSS Helps Lessees, Car Owners Negotiate
CHRNSS gives users a single access point through which they can participate in negotiations around car hire. Car hire is the compensation paid to the owner of a railcar for its use.
In other words, if I want to "rent" a railcar that you own, we can use CHRNSS to negotiate the rate I pay. CHRNSS helps users better define the car types they need, car owners better segment their cars during the rate negotiation process and bid recipients quickly validate that the offered cars meet expectations.
It's a great tool that makes a complex process less complicated. But CHRNSS was taking up to a minute to load, and that was a problem for our customers.
Why was it taking so long?
CHRNSS provides a real-time feed of railroad data. Before we completed this work, the application pulled a massive amount of data, including information that wasn't necessary, from multiple tables when a user logged in. This led to the long wait time.
Slimming Down the Search
The sticky notes above, taken from the CHRNSS team's Agile task board, outline two steps in the work we did to simplify the data mining process and reduce the time it took for the application to load.
From top to bottom, the sticky notes read:
US27619
Update data model for BOT, BOTED
US27620
SQL script to push rate data up into bid_offer_transaction
US27618
Remove/change references to bid_offer_trans_equipment_detail
The top note, User Story 27619, tells the development team, in general terms, about the activities detailed in the two notes below it.
We updated the data model for a table called bid_offer_transaction (BOT). To do this, the project's lead developer created an SQL script that pushes relevant car hire rate data into the BOT table.
Now, the application doesn't have to search so many tables for the relevant car hire rate data. The data is already where it needs to be, in the BOT table.
We also removed references to a table called bid_offer_trans_equip_detail (BOTED). These references complicated the query process and slowed CHRNSS.
"There was a lot of unnecessary activity going on in CHRNSS, which was degrading the customer experience," said Rob Hannah, the project's lead developer.
Cutting Load Time to Less Than Two Seconds
Without references to these tables, CHRNSS now can make a beeline to the relevant car hire rate data. The updates to the application also enable users to see changes to offers as they occur.
Because we're mining only the relevant data sets, CHRNSS now loads in less than two seconds.
"This process involved refactoring a lot of code, and it was a huge testing effort that collected input from a group of CHRNSS users," said Meghan Finnie, a business analyst on the CHRNSS team. "But we wanted to get it right, and the feedback helped us make sure we gave customers what they wanted and make it easier for them to use the application."
—Railinc Corporate Communications