Richard Tesmer, now the Application Development Manager, began a Data Transformation project, leading to the GIS Application project to maintain the Road Rescue call center activity.
Project Details
- Project Title: DTS and GIS
- Methodology: Waterfall
- Start Date: 01/01/1998
- End Date: 12/31/2002
- Project Team:
- Project Manager: Richard Tesmer, Manager of the Application Development Division
- Lead Developer: Kirk Roybal
- Team Size: 20
- Project Details:
- Functional Reporting Team: Roadside Assistance
- Organizational Primary Focus: Vendor Management
DTS or Data Transformation Services. My first project right out of the gate was to work with the owner on converting his current data from FoxPro and moving it into Microsoft SQL Server, using Microsoft Access as the front-end presentation layer. The driving force behind this change was the addition of several major cellphone companies. Each had its data format. Each had different SLAs for how quickly their data had to be transformed. The operation sounded simple- back up the data, delete yesterday's data for that client, transform the data to our format, and upload that data. It turned out that they sent us millions of records daily that had to be processed within hours. This ensured that anyone calling for rescue services was a current subscriber in good standing. Else, we would have to pay for the service ourselves.
It was a massive undertaking. Until then, the owner had written his transformation scripts in Fox Pro. He could have done it as long as there were only a few clients, but Road Rescue was now approaching 100.
We quickly realized that neither FoxPro nor SQL Server nor Access would give us the speed, flexibility, or ease of creating a script for transformations for new client data formats. This led to my next big project—locating a DTS.
We interviewed several vendors, such as Scaler, DBT, DataCatchup, and IBM InfoSphere, and started with DBSchema. We then learned that having just one database was not going to work. We could not offload all of the DTS to the night shift. We needed to run the process 24x7. It put too much pressure on the mid-tier data layer server and the SQL server. We had to redesign the data warehouse farm. We needed more power as well, which meant moving up to RS6000.
The vendor department was not done with requests; instead, they requested a GIS system. This was long before Google Maps existed, and the government's primary vendor and source was ESRI (which still exists today). GIS was a new area for commercial businesses, so we had to hire experts to assist us; we went with a Russian team that claimed they had direct GIS experience. In time, it was obvious that they had no more experience than my team. There were many technical challenges- first, polygonal math. The vendor management team needed to be able to create a polygon for each service a vendor would provide. With that polygon, we would need to store scoring data. And since GIS was still new, triangulation was the only way to track a subscriber. However, not all areas of the country had cell towers yet, and even if there were, the accuracy was not great. E911 data would become available in a few years, but it would not be until then. The first betas worked as planned. It would triangulate your location within 3 minutes of your call. It would convert your assumed location into GIS format and locate you within another 3 minutes. It would then process your point to find what polygons had that point inside of them, another 3 minutes, and then use the scoring data to rank them. Then, provide this to the subscriber while our system reaches out to the vendor with the rescue information. Sadly, this meant each service call would consume at least 10 minutes. Our SLA started to rescue in less than 5 minutes. The system worked but failed to meet the SLAs. We fired the Russians.
By this time, the internal developers had learned far more than the Russians. But now we had 450 cell phone companies representing 95% of all cell phone subscriptions—and a vendor system bringing the call center to a screeching halt. We brought in more network engineers and realized the only solution was to invest in an even larger server farm filled with RS600. I had to fly to California to meet with the newest owners of Road Rescue. That meeting lasted for several days. I was fully prepared with facts, figures, prices, budgets, and a plan. They agreed, and I flew home and started on the revamped project now with a multimillion-dollar budget. ESRI was brought in to work with us one-on-one to make sure we could hit the SLAs. As time went on, they had to bring in more and more specialists when it was realized how much stress was being placed on the servers.
We had overcome the challenges. The server farm could generate new polygon layers very quickly. We could transform client data in seconds, where it used to take hours. Our call center was now two call centers with over 1000 seats. Suddenly, we were told our name was changing, as were our owners. We would now be known as Road Rescue Merrimac. We were now a part of a global investment firm.
A year later, they acquired a company that practiced reinsurance. That is the service you would call if you dropped or lost your phone. They will send you a replacement device as soon as possible. We had to merge backend systems, rewrite the front-end claims processing system, and add real-time credit card transactions. Again, these are all pretty new systems. These are very heady days for us. We were meeting the challenges. Then we were told that the call center/warehouse for reinsurance was failing, and I had to take my team and find a way to redo it so it would perform just as well as the Rescue side was working.
This time, we had to do this while the Reinsurance side was working, albeit slowly. So, we started to merge and get everyone across the entire enterprise into SAP. From there, we went with the SAP warehouse module. We reworked the warehouse procurement system, logistics, and inventory management and were able to deliver replacement devices within one day of receiving the claims. This also made us a prime candidate for another takeover.
That takeover saw a major marketing change and rebranding. After time and much expense, we came to just Asurion. It's a made-up term, but it implies strength in numbers—which did define us pretty well. Now that we had all these systems in place, the Angle Investors did what most would do- trimming departments and breaking covenants around buyout bonuses. Translation- I was to leave with no bonus money. But I will never forget the excitement of this startup, being there for its growth and and knowing that it is still there today.