Nissan UK
A Data Management Case Study
Case Study Summary
Situation:
Nissan's existing IT systems for managing warranty budget expenditure needed updating and data management was an essential part of the solution.
Solution:
Excitation undertook several elements of work as part of the overall solution, including data management, strategy development, documentation and training.
Benefits:
- Massive €5 million annual saving
- Faster than previous solution
- More productive staff
- Faster problem identification and resolution
Our client said:
"We estimate that by being able to provide a more accurate warranty spend for the coming year, and more accurately monitoring and controlling that spend, we can save in the region of €5 million."
David Parkinson, Controller, Nissan
Client Background
Nissan is a worldwide manufacturer of motor vehicles. Their UK division manages the IT systems and infrastructure for their operations across Europe.
Project Overview
Nissan’s European Warranty Group (EWG) control the budget to pay for all warranty claims made on vehicles sold in Europe. Their existing IT systems for managing this expenditure had been found to be inadequate, and needed updating.
The solution had to combine the functionality of 3 separate SAS systems into a single system that would provide all the reporting and forecasting requirements for the European Warranty Group. Microsoft SQL Server 2000 and Analysis Services 2000 were chosen for the database platform.
Managing data was an essential part of the solution. Nissan required a database design that could fulfil all the reporting requirements within strict performance parameters and include centralised calculation of complex key performance indicators (KPIs) across many dimensions. A robust data transfer process was also necessary, to ensure accuracy of reports based on data from multiple sources.
Nissan realised they needed external expertise to ensure that the data design and transfer processes were problem free.
Why Excitation was chosen
Excitation offers extensive technical expertise with OLAP database design, and implementing mission-critical solutions on Microsoft SQL Server and Analysis Services.
We also impressed Nissan with our understanding of their business problems, and our willingness to work with them to ensure that their solution as a whole would be a success. This included not only the design and implementation of the database solution, but also the implementation of administrative procedures, supply of complete project documentation (including recommendations for future expansion and development), and provision of customised training for Nissan’s administrative and development teams.
Solution
Excitation provided the following services as part of the overall solution:
- Designed OLAP cubes and dimensions
- Created MDX expressions for all KPIs
- Created the SQL-MDX query syntax and client logic to allow customisation of user reports
- Designed the data cleansing and data realignment processes
- Designed cube aggregation design and processing strategies
- Configured MS SQL Server 2000 and Analysis Services according to the application’s performance requirements
- Produced documentation, including recommendations for future operations
- Trained developers and administrators
The data modelling and OLAP cube design had to meet two challenges: to manage the complexity involved with over 40 KPIs and 20 dimensions, and to incorporate measures and dimensions that are traditionally difficult to model in a multi-dimensional database (namely many-to-many relations, and semi-additive measures).
An additional issue was that Nissan were developing a custom intranet front-end for the application, which would allow users to create their own reports. This front-end had to generate SQL-MDX queries based on the users’ requirements; however the developers had no prior experience of SQL-MDX. The solution was threefold; to produce a OLAP design that appeared as simple as possible to the front-end, to define the calculation logic in MDX expressions within Analysis Services, and for Excitation to supply a set of SQL-MDX query fragments for the front-end, with the associated logic for combining them into valid queries.
To offer users up-to-date data, whilst ensuring sufficient redundancy in the data loading process, 3 separate extract, transform and load (ETL) processes were built – a daily update, a monthly update and a full realignment process. Each process had to be managed individually as a transaction with complete control-of-flow logic, error-handling and status reporting. There was also the requirement for a front-end application to allow user control of the process, whilst ensuring overall data consistency.
Client Feedback
David Parkinson, Nissan’s Controller, commented: "We estimate that by being able to provide a more accurate warranty spend for the coming year, and more accurately monitoring and controlling that spend, we can save in the region of €5 million—just through this analysis and using it to implement the necessary changes."
"The system is much faster, especially for simple queries, and staff are more productive. Using the solution, it is now easy to look at multiple cross-sections of information, which can be used intelligently to predict where problems lie, and enables Nissan to rectify those problems sooner."
"We have no doubt that this solution will give us the business intelligence we need to reduce warranties spend."
"Our users are extremely happy with the solution. It has been a highly successful implementation."