Development of a Data Analytics Solution for Luxury Vehicle Dealers
The Customer is a major provider of enterprise software products tailored to the automotive industry, such as solutions for dealer management, customer and vehicle relationship management and business intelligence, among others. Based in Germany and represented in 90 more countries, the company serves to over 90,000 clients and 3,500 car dealerships internationally.
The Customer chose Pine Analytical as a technical consultant to create analytical system with rich reporting functionality for internal purposes of car manufacturers and their dealer networks. The system was supposed to cover different business parts of the automotive industry: automobile sales and services, spare parts tracking and financial analysis. As the dealer networks involve international users, the system was supposed to provide a multilingual interface.
Together with Customer’s team Pine Analytical’s BI implementation team designed and developed an ETL-based Data Warehouse (DWH) that includes a data staging area to avoid gathering of irrelevant information from a variety of sources. To cover the required business areas, four data marts were created and merged inside the DWH.
Four multidimensional Microsoft cubes were created to maximize the depth of the analysis:
Security models, covering data usage permissions, were implemented for each of the analytical cubes.
In order to load information to staging databases and DWH, our team developed integration modules. Internal Company’s systems (CRM), diverse SQL databases, excel and flat-files with different structures are used as data sources. Two ways are available for displaying of reports: SQL Server Reporting Services which is available within SharePoint portal and PowerPivot models in Excel.
To ensure making data-driven decisions Pine Analytical team created over 40 customizable reports as well as interactive dashboards with the ability to track KPIs, assign tasks, goals, collaborate and share important information. Reports can be drilled down by various criteria, including time period (starting from one day to the whole period), spare parts categories as well as types, brands and makes of vehicles and service stations.
The system was designed to support three languages to cater for dealers from different countries. All the metrics and reports are available in appropriate language.
Pine Analytical created a complete performance management system that enables data collection and analysis for the required business areas: vehicles sales and after-sales service, spare parts availability and sales as well as financial reporting.
The system let automotive retailers to explore data visually and analytically, therefore enabling decisions based on up-to-date information. All the tests on the Customer environment were successfully passed and the system is being used by a large automobile dealer.
The Customer was satisfied with the solution Pine Analytical delivered and the work of the team was highly appreciated.
Technologies and Tools
Microsoft SQL Server 2008, Microsoft Integration Service 2008, Microsoft Analysis Service 2008, Microsoft Reporting Service, Microsoft SharePoint, Microsoft Excel, Microsoft PowerPivot, T-SQL, MDX.