Ana səhifə

McLaren Electronics Fuels Analysis of Formula One Racing Data with sql server


Yüklə 1.21 Mb.
tarix27.06.2016
ölçüsü1.21 Mb.












McLaren Electronics Fuels Analysis of Formula One Racing Data with SQL Server














Overview

Country or Region: United Kingdom

Industry: Manufacturing – High Tech & Electronics
Customer Profile

McLaren Electronic Systems is part of the McLaren Group. Based in the United Kingdom, the company provides automotive electronics that combine reliability with cutting-edge technology.


Business Situation

McLaren Electronics needed to replace file-based storage with a relational database for the more than 2 terabytes of engine control unit (ECU) data collected each year by a typical Formula One racing team.


Solution

The company is using the FILESTREAM feature of Microsoft® SQL Server® 2008 to store ECU data in a relational database while providing rapid access to the data streams for users.


Benefits

Easier data management

Greater visibility into the data

Fast and seamless data access

Easy to use analytic tools

Ability to work offline










“To support the racing teams we need three things from a database: performance, integrity, and robustness. If you can find all three, you’ve got a winner. SQL Server 2008 wins on all three points.”

Peter van Manen, Managing Director, McLaren Electronic Systems










McLaren Electronic Systems, part of a family of companies that includes the McLaren Racing organization, is a leader in developing specialized motor racing products including the engine control unit (ECU) that manages the complex engine, transmission, and other key elements of Formula One race cars. After the Fédération Internationale de l'Automobile (FIA), motor sport’s world governing body, awarded McLaren Electronics and Microsoft the contract to provide the ECU solution that will be used in all Formula One race cars, McLaren Electronics sought a more efficient way to manage the terabytes of ECU data that a team generates in a year. After performing a proof of concept study, McLaren Electronics found that Microsoft® SQL Server® 2008 provided the solution it needed for storing data while providing Formula One type retrieval speeds.





























Fast Facts

Sensors on a Formula One race car

100+

Data generation rate while racing

100 kilobytes to 0.5 megabytes per second

Data generated during 1 Grand Prix race

1 to 2 gigabytes

ECU data collected per car per year
(including practice laps and equipment testing)

2 to 3 terabytes

Application Platform Capabilities

Development, Data Management



Situation

As Formula One Race cars zoom around Grand Prix race tracks reaching speeds of more than 200 miles an hour, they are also transmitting wireless data to trackside support crews at the rate of up to 0.5 megabyte per second. And as fans in the stands are glued to the action on the track, engineers in the pit garages are equally entranced by the data racing across the screens of their laptop computers.


McLaren Electronic Systems, an independent company of the McLaren Group of companies, which includes the famed McLaren Racing organization, is a supplier of advanced motorsport control and development support systems including the engine control unit (ECU) that manages the complex engine, transmission, and other key elements of Formula One race cars.
Formula One racing, regulated by the Fédération Internationale de l'Automobile (FIA), motor sport’s world governing body, represents the highest class of auto racing, and is constantly pushing the leading edge of technology. Computers have long been used in designing and optimizing the complex settings for race cars, and for collecting onboard data that is transmitted back to support teams. A key component is the ECU, which gathers data from sensors on the car and wirelessly transmits data to race team support crews.
As part of its ongoing efforts to control costs, provide a level playing field for racing teams, and sustain safety, the FIA in the spring of 2006 invited manufacturers of automotive electronics to propose a standardized ECU for inclusion in all Formula One cars for the 2008, 2009, and 2010 championships. The FIA chose the ECU solution developed by McLaren Electronics and Microsoft, and awarded the joint-effort the contract to supply the solution to all competing Formula One teams.
The solution comprises the power train control, data logging and telemetry control components on the car and also a central data server in the garage which receives data from the car, logs it for near instant analysis and redistributes it to client tools of the racing team’s choice. (NB McLaren can also provide the client tool capable of both receiving real time and interrogating historical data). The in-garage components (data server and client tool) are known as ATLAS (Advanced Telemetry Linked Analysis System).
The ECU monitors all aspects of the power train and gathers data from the more than 100 sensors located on each car. Generating between 100 kilobytes and 0.5 megabyte of data per second, one car’s ECU will potentially gather more than 1 gigabyte of information from the sensors during a grand prix race, with all of it continuously broadcast in real time back to systems located in the track-side garage of each Formula One. Here the ATLAS data server must log the data without loss and provide the means for Engineers to almost instantly retrieve and analyze the data. In addition, where not all the data could be transmitted for example due to restricted bandwidth, on returning to the garage the missing data is taken directly from the car and the gaps automatically filled in by the server.

“The essence of the problem we faced was how to deal effectively with a high volume of time series data. Prior to SQL Server 2008, this was not something that we felt any database could really handle properly.”

Paul Spence, Product Director, McLaren Electronic Systems







McLaren Electronics and Microsoft have worked together closely, developing Formula One technologies. The ECU data, collected during practice laps and racing, as well as from testing equipment in the garage and from factory simulators, has proven so valuable that McLaren Electronics wanted to provide a storage solution that made it easier and faster to retrieve telemetry data stored in specially developed, highly efficient files. In addition the company wanted to make this data available through other tools such as Excel with a standard Open Database Connectivity (ODBC) or Object Linking and Embedding Database (OLEDB) data access capability, without the need to understand the file formats being used.
Using a relational database in place of the file system could help teams better manage, aggregate and search data. However, it was essential that data retrieval times match the race-environment performance required by McLaren Electronics and its Formula One racing team customers. Performance, in this case the time taken to load a given session of telemetry data into the client tool for analysis, needed to be near-instantaneous.
Storing the information in the database relational tables as binary large object (BLOB) data was not regarded a viable option as this would require a more significant rewrite of their specialized telemetry client tool. The file formats were already highly efficient but they needed to find a way to manage the data more effectively and expose it through standard data access interfaces.
“Working within the Formula One racing environment, the teams need instant availability for all ECU data, current and past,” says Peter van Manen, Managing Director, McLaren Electronic Systems. “The teams also generate a great deal of data from dynos [engine test bed], wind tunnels, and simulations. While we’ve been able to supply excellent real-time data, we needed a better way for racing teams to store and access all of the available data to help them better analyze performance against a spectrum of variables associated with ride height, tire, suspension, gearbox, and engine settings.”
Solution

McLaren Electronic Systems saw immediate value in the Microsoft® Application Platform and, in particular, the FILESTREAM feature of Microsoft SQL Server® 2008 database software. New for SQL Server 2008, FILESTREAM integrates the SQL Server Database Engine with the NT File System (NTFS) by storing binary data as files on the file system while letting the data remain an integral part of the database and maintaining transactional integrity. Transact-SQL statements are used to insert, update, query, and back up the FILESTREAM data within the database. In the meantime the existing client tool can use Win32 file system interfaces for streaming access to the data as if reading and writing to a file.


“The essence of the problem we faced was how to deal effectively with a high volume of time series data,” says Paul Spence, Product Director with McLaren Electronic Systems. “Prior to SQL Server 2008, this was not something that we felt any database could really handle properly.”
Working with Microsoft and Conchango, a Microsoft Gold Certified Partner specializing in enterprise data integration, McLaren Electronics executed a proof of concept (PoC) project to test the efficiency of FILESTREAM. The PoC was a success and McLaren Electronics and Conchango are now fully developing the solution using SQL Server 2008 and its FILESTREAM feature at its core.
Architecture

The McLaren Electronics solution has a multi-tier architecture that includes:



ECU. The McLaren Electronics ECU is the onboard hardware device with embedded code that gathers data from the Formula One car’s engine, transmission, suspension system, frame components and other key elements, encrypts the data; and transmits it to the trackside support team.




Figure 1 – Basic architecture of the McLaren Electronics solution created and deployed using the Microsoft Application Platform
ATLAS Server. ECU data from the car is sent to the ATLAS server solution developed by McLaren Electronics, initially using the Visual Studio® 2005 development system, and now Visual Studio 2008. The ATLAS Server unpacks the telemetry data from the ECU and multicasts it to ATLAS client software being used by support crews.

ATLAS Client. The ATLAS Client software runs on support team computers, usually laptops in the garage. The ATLAS Client provides near real-time display of data and corresponding graphics that help support teams to monitor the health of the racing car across a spectrum of parameters. The ATLAS client, developed by McLaren Electronics, runs on the Microsoft Windows® XP or Windows Vista® operating system. Microsoft Office Excel® 2007 spreadsheet software is used for additional data analysis and visualization. On large racing teams there can be more than 30 support team members studying ECU data on their laptops during a race, with one set of engineers and technicians studying transmission feeds, while others analyze telemetry data from the engine, suspension, and other systems.

ATLAS Database. The Atlas Database offers McLaren Electronics a relational database for ECU data to provide an easily searchable central repository. Hosting ECU data on Microsoft SQL Server 2008 running on the Windows Server® 2008 Enterprise operating system, using the FILESTREAM feature for faster access, will provide seamless access to historic ECU data - - whether from previous races, test lab simulations, or just the last lap run. Conchango is working with McLaren Electronics and Microsoft to create the database solution. The database schema includes all of the metadata about the race, test, or other ECU-generating event. The schema also includes a specific session data table with the file stream BLOB column for each ECU-generating event. The solution also uses the common language runtime (CLR) hosted within SQL Server. When an ECU data set is requested, a CLR function expands the binary data as a parameter result set so that it can then be consumed by reporting and visualization tools such Office Excel 2007 and SQL Server 2008 Reporting Services.

"The proof of concept testing verified that using SQL Server 2008 we can gain the same performance with historical data as we would expect to see with the real-time data that is streamed from cars racing around the track."

Peter van Manen, Managing Director, McLaren Electronic Systems







Proof of Concept

One of the major targets set for the PoC was to match access times of the existing NTFS-based file solution. The existing system stored telemetry data in a highly efficient form in one file, while associated metadata was stored in another file. Using the file system in this way provided good performance but made it more difficult to find relevant historical data. The very efficient storage format used was necessitated by the data throughput demands of the Formula One racing environment.


Storing the ECU telemetry data in the file system also made it difficult to provide a generic data access interface to productivity tools such as Office Excel. Use of the file system also presented challenges in managing the very large volumes of data generated by the racing teams—as members need to have access to the complete spectrum of ECU data, whether generated in races, in practice laps, in garage dyno testing, or in factory simulations.
The PoC successfully demonstrated a number of ways in which data storage and presentation could be enhanced by incorporating SQL Server 2008 and other elements of the Microsoft Application Platform.
The PoC showed that:

SQL Server 2008 can deliver fast access to the time-series ECU data previously stored using NTFS files.

The FILESTREAM feature of SQL Server 2008 can be used to expose data as binary files for use by the existing ATLAS client application or as tables for use by any ODBC or OLEDB compatible clients.

Hosting the ECU data on SQL Server 2008 helps McLaren provide a more intuitive client experience by enabling applications such as Office Excel to query the data directly by exposing the data as tables—something which isn’t feasible with data stored on a file server.

ECU data is easier to manage and maintain when stored on SQL Server, compared to hosting it on a file server.
“The proof of concept testing verified that using SQL Server 2008 we can gain the same performance with historical data as we would expect to see with the real-time data that is streamed from cars racing around the track,” says van Manen. “Making historic ECU data immediately available produces huge value for racing teams that may want to analyze how a car performed on the same track before, or to match the racing day weather with previous runs in similar conditions.”
Benefits

McLaren Electronic Systems will gain the easier and more reliable data management it is seeking by moving data from file-based storage to SQL Server 2008. The solution will also provide greater visibility into the data, fast and seamless data access using FILESTREAM, easy to use analytic tools, and the ability to work offline.


Easier Data Management

Moving ECU data from a flat file format into SQL Server 2008 greatly enhances data management. Information stored in a relational data store is easier to centrally manage, back-up, search, and access than records stored in individual files, and the Microsoft Application Platform can be used to manage access rights to data runs and parameters within runs.


“The ECU generates data on 500 to 1,000 parameters that are of continuous interest to the pit-side crews, and there’s another 10,000 data points that are examined less frequently to guide diagnostics,” says Spence. “And there’s a lot of additional data that is generated from the raw sensor information as engineers within the garage study patterns and look for insights for how to better prepare the cars for any given racing event. When data from an earlier test was needed, too often it came down to an engineer remembering a file name, or needing to find a file on someone else’s laptop. Moving all of this to SQL Server 2008 greatly simplifies data management.”

“The ECU generates data on 500 to 1,000 parameters that are of continuous interest to the pit-side crews, and there’s another 10,000 data points that are examined less frequently to guide diagnostics. … Moving all of this to SQL Server 2008 greatly simplifies data management.”

Paul Spence, Product Director, McLaren Electronic Systems







Providing a central data repository saves teams from the redundancy of generating data that has already been captured elsewhere in their operations. “Once data has been captured and stored to SQL Server it is available for other team members,” says van Manen. “Whether the information was generated earlier in the morning at the track, from the same track a year earlier, or from tests performed at the factory or in simulations, everyone with authorized access to the database can pull value from the information. The ease with which data can be discovered also saves teams from performing unneeded tests that simply duplicate what has already been documented.”
Data integrity is enhanced by storing information on SQL Server for a number of reasons, including the ease with which data can be corrected or annotated after it has been captured.
“Most of the ECU data streamed from the car is stored in the database and never changed,” says Spence. “But there are plenty of times when the data can be enhanced with driver comments. For example, after a run they might discover that the car’s wing settings were different from what was specified, or that tires were filled with a different air pressure than requested, or that a different sensor was used that wasn’t properly calibrated. There are a number of reasons the data that is captured may need to be altered or annotated to protect the integrity of the total body of information that is being collected by a team.”
Storing data within a SQL Server database also helps preserve investments made in creating the original data. “The teams all invest a great deal in gathering data, whether on the track or in the garage or lab,” says van Manen. “Tests are expensive. Running simulations is expensive. Providing better access to this data increases the value of an expensive commodity. Rather than being forgotten in a folder, the data is there to help them again and again.”
Greater Visibility into the Data

Centralizing ECU data into a SQL Server relational database makes it easier for racing teams to find the specific data sets they need during the process of preparing for a race. “Preparing the car for a race begins long before going to the track, with the team performing simulations and testing,” says van Manen. “Once at the track more data is generated with each test run, and from this pool of information the teams try to determine the ideal calibrations for the car.”


With all of its data stored in a centralized relational database a team will be able to immediately access relevant data from historic captures. For example, a trackside team preparing suspension calibrations for a specific track could simply enter the name of the track to access all data streams that have been collected from previous visits—including variations in track and ambient temperatures.

“A Formula One race car is incredibly complex and can be minutely calibrated … Storing ECU data in SQL Server gives teams the visibility they need into a world of high-value data that would otherwise be difficult to make use of.”

Peter van Manen, Managing Director, McLaren Electronic Systems







“A Formula One race car is incredibly complex and can be minutely calibrated to meet the needs of a specific track, the weather, and a list of other variables,” says van Manen. “The tires are what translate power to the ground, and factors like tire choice and pressure influence traction, braking, and cornering. Dampers [shock absorbers] and other parts of the suspension are calibrated as are the gearbox, motor, and wings that create downward pressure to keep the car on the track. Storing ECU data in SQL Server gives teams the visibility they need into a world of high-value data that would otherwise be difficult to make use of.”
Giving race teams the ability to search their own database for relevant performance information will help them in their constant quest to balance two opposing racing factors that can otherwise be in opposition: The driver’s need for dependability and predictability in the car, while pushing the limits of performance.
“A driver might roll into the pits and report that the car isn’t coming out of the corners as well as it should be,” van Manen says. “The trackside crew needs all of the relevant data points they can find to determine if they should be changing a suspension setting, using a different type of tire, or changing the gearbox.”
Ready access to vast data helps teams respond to the unexpected developments that come with racing. Van Manen notes, “There is a lot of science involved in preparing the car, but there is also a lot of art to racing, which means teams need the ability to rapidly respond to the unexpected.”
Fast and Seamless Data Access using FILESTREAM

One of the factors behind the need for speed in accessing data is that race teams collect so much information. “A racing team might collect 2 or 3 terabytes of data per year, and need the ability to access 4 or 5 years worth of data,” says Spence. “So the volumes are large. SQL Server 2008 enables us to use the FILESTREAM mechanism to point to a BLOB and stream it, providing much faster access than we would get through a typical table query.”


The unpredictability of racing is another major force behind the need for immediate access to large volumes of ECU data.
“The reasons teams gather so much information is that you're never absolutely certain as to what you will need to look at during a race or during a test,” says van Manen. “What you do know is that any decision making, especially during a race, will have to be made nearly immediately. A team has a massive amount of data to guide their decisions, but they need the ability to access that data swiftly and precisely. If they need to compare data points with earlier runs, all of the earlier examples need to be immediately accessible for comparison. With Formula One, you are operating within a very impatient world.”
Spence agrees about the demands of working within an impatient world: “The most important thing to the team is the speed of access. “We all know how frustrating it is if you load something on a computer and you have to wait for a minute. When you're in the racing environment the pressure is intense. You need a robust, high-performance system when you’ve got someone shouting at you, ‘We need to know what to do before the car goes!’ The proof of concept demonstrated that SQL Server 2008 and FILESTREAM clearly outperformed our file system, while greatly improving data integrity.”
The combination of performance, data integrity, and robustness has made McLaren Electronics eager to offer its database solution to all Formula One teams.
“To support the racing teams we need three things from a database: performance, integrity, and robustness,” says van Manen. “If you can find all three, you’ve got a winner. SQL Server 2008 wins on all three points.”
Easy to Use Analytic Tools


Figure 2 – SQL Server data displayed in Office Excel


McLaren Electronics values the ease of use race teams gain through SQL Server 2008 integration with Microsoft Office Excel. A SQL Server stored procedure is used to expose the FILESTREAM information as tabular data for consumption by ODBC-compatible clients such as Office Excel.


“Working in a high-pressure racing environment, people want to use the tools they are already familiar with, which within Formula One racing means Excel,” says Spence. “There’s no time to be fumbling with an unfamiliar interface. We’ve found that race team engineers know and trust Excel, so we were relieved to see how easily data could be moved from SQL Server to Excel for analysis and visualization.”
Using Excel visualization to analyze data can help engineers reach faster decisions. “Race engineering requires the ability to reduce vast amounts of data to some critical decisions that improve the car’s performance,” says Spence. “Excel is very good at helping engineers identify key data points.”
Looking further down the line, McLaren Electronics is interested in using SQL Server 2008 Analysis Services to create multidimensional data cubes to provide additional pathways for analyzing ECU data.
Ability to Work Offline

SQL Server 2008 Express, available as a free download, gives race team members the ability to easily work in the field when not plugged into the central database. This is helpful because the global nature of Formula One racing means that engineers and other crew members sometimes work on their own in the field.


“The flexibility that we get from SQL Server Express is important,” says Spence. “Sometimes there is just one engineer with a laptop working with a driver at a runway somewhere, or doing work on a test rig, or trying out a new piece of equipment at a supplier. When out in the field, they need to have the same access to data as if they were in the garage working as part of a team of 20. SQL Server Express gives them the support of the full database with the ability to seamlessly synchronize later.”
Summary

McLaren Electronic Systems is helping Formula One race teams gain more value from the more than 2 terabytes of ECU data they collect each year by using the FILESTREAM feature of SQL Server 2008 to create a central repository with rapid response times.


For More Information

For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to: www.microsoft.com


For more information about Conchango products and services, call +44 (0)1784 222222 or visit the Web site at: www.conchango.com
For more information about McLaren Electronic Systems products and services, call +44 (0) 1483 261400 or visit the Web site at: www.maclarenelectronics.com


This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Document published February 2008










Software and Services

Microsoft Servers

Windows Server 2008 Enterprise

Microsoft SQL Server 2008 Enterprise

Microsoft SQL Server 2008 Express

Windows Vista

Windows Vista

Windows Visual Studio 2008




Windows Office System

Office Excel 2007


Hardware

AMD-based and Intel-based computers

Partner

Conchango





Windows Server 2008, SQL Server 2008, and Visual Studio 2008

Windows Server 2008, SQL Server 2008, and Visual Studio 2008 provide a secure and trusted foundation for creating and running your most demanding applications. Combined, the products offer advanced security technology, developer support for the latest platforms, improved management and Web tools, flexible virtualization technology to optimize your infrastructure, and access to relevant information throughout your organization.


For more information about Windows Server 2008, go to:

www.microsoft.com/windowsserver2008
For more information about SQL Server 2008, go to:

www.microsoft.com/sql/2008/default.mspx
For more information about Visual Studio 2008, go to:

www.microsoft.com/vstudio.


Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©atelim.com 2016
rəhbərliyinə müraciət