Company Gains Business Insight With Data WarehouseBy Eileen McCooey | Posted 2017-09-07 Email Print
A data warehouse with in-database analytics gives Western Refining valuable insights into operations, slashing processing time and improving data accuracy.
Massive amounts of data can be invaluable, but can also be overwhelming and underutilized. Until several years ago, Western Refining was grappling with data management problems shared by many enterprises. The pipeline logistics, refining and marketing company had an enormous amount of data, but it was siloed in different databases or existed solely on a spreadsheet on someone's laptop. Reconciling information from all those sources took enormous effort.
"We'd have to run queries on multiple systems, and then massage the results," recalls David Brand, manager of business intelligence for the company, which was recently acquired by Tesoro and renamed Andeavor. "It could take four or five days to get the information we needed. We also had problems with quality. Overall, our data wasn't delivering much value to the business."
Management wanted a solution that could provide speedy access to information across the enterprise. "We wanted the best in the industry, and the clear answer was Teradata," Brand says.
In 2011, Western Refining purchased the Teradata Data Warehouse Appliance 2650, an integrated data warehouse platform that offers fast parallel processing, scalability to handle massive volumes of data and in-database analytic capabilities. The system housed the production and development environments in Tempe, Ariz., where the company's IT and most administrative operations were based.
The first project was an eight-week proof of concept that tackled the thorny problem of inventory control. "Crude, gasoline and jet fuel inventories are stored in terminals, pipelines and rail cars," Brand explains. "We only control some of those sites, so getting our arms around the information was a challenge, especially when it wasn't inside our systems."
The test proved successful, so Western Refining went live with Teradata's relational database, using Informatica as the ETL (extract transform load) tool. The improvement was remarkable: With the click of a button, the company got a complete picture of inventory every day. However, skeptics doubted the accuracy.
"We had daily arguments with traders who insisted our numbers were wrong," he says. "But when we compared our figures to all the sources, 98 percent of the errors were on the traders' side because of out-of-date systems or manual errors in data entry."
After four months, no doubt remained. "That was a watershed moment," Brand says. "Teradata became our trusted system of record. Now, when we go into meetings, we all have the same data, and it's always correct. That's eliminated arguments and saved us a lot of time."
Moving Sensor Data Into the Warehouse
In 2013, Western Refining moved another major project onto the Teradata platform. The EPA regulates emissions, flare and waste water, among other things, all of which are read by sensors. An employee was spending up to 10 hours a day importing sensor data into Excel spreadsheets and running calculations using four of the most powerful computers in the company.
"When he requested a fifth computer, we decided to move the sensor data into the warehouse," Brand says. "There was no commercially available method for moving the massive amounts of data into a relational database environment, so we built our own." There are about 30 billion records in the primary sensor table, but Teradata can handle it, which he calls "a credit to the architecture."
"We get real-time results in seconds and can marry up that data with other information, giving us new insights," Brand adds. "EPA reports and audits have become much simpler, and our dealings with the agency have improved because it's clear we have a good handle on things."
Blending gasoline to hit octane specifications, once a time-consuming and error-prone process, also improved dramatically. In the past, an employee worked full-time entering data by hand, and the complexities resulted in a 30 percent error rate.
When Western Refining began using the Teradata system, it was able to hit required specs exactly. That conserved pricey ingredients such as butane, yielding a 3,000 percent ROI on an annualized basis.
The company can now run daily comparisons of actual-to-planned production, both in barrel output and market price. "We can react immediately," Brand says. "Previously, it took about six weeks to get the reconciliation from accounting, so it was too late to make adjustments."
Western Refining can also run industry-standard Solomon metrics daily, rather than waiting for biannual rankings. That gives the company a view into its operations that other refining companies don't have, according to Brand.
In 2015, the company installed a new Teradata 2750 system in its El Paso headquarters. That became the production environment, with the original Tempe system serving as the disaster recovery and development environments. Teradata's Unity Data Mover product keeps them all synchronized.
"With Teradata, it's so easy to get information that our people are always thinking about ways to use it," Brand says. "Now that we have high-powered tools, we don't hear staffers saying something would be too hard or impossible. They're really excited."