Project type:  Data mart and OLAP reporting, dashboards

Duration: 3 months

A tier-one, high-volume parts supplier to the automotive industry wanted to gain a competitive advantage with better visibility into profitability and component demand across its product line. With many thousands of parts supplied to auto makers globally, and sales forecasts and contracts constantly in flux, the supplier needed a highly scalable and powerful analytic solution. At the same time, the resulting information had to be easy for business users to access and understand.

WIT rose to the challenge with a data mart and OLAP reporting and dashboard solution that supports two distinct applications:

Challenge

The request for these applications came from the highest level of the supplier company – the CEO wanted visibility into current profitability and component demand to support better business agility.   

The technical challenges were significant:

Changing sales forecasts: The profitability and component analysis both depend on the five-year sales forecast, which represents both current and anticipated contractual agreements.  With the magnitude of the supplier’s businesses, these forecasts change nearly daily.  Because the analysis always needs to be up-to-date, the solution must refresh the data mart on a daily basis.

Part complexity:  The supplier offers many thousands of parts, each of which is comprised of several subparts and components.  Navigating and normalizing the Bill of Material (BOM) for each requires significant processing of large data volumes.

Disparate data sources:  While much of the source data resides in a SQL Server database, the part cost information is regularly provided to the system in spreadsheets from the various groups within the business. The complete solution must extract and integrate data from multiple sources.

Currency conversions:  Because the supplier does business around the globe, the profitability analysis must reflect current currency conversion rates and display profitability data in the appropriate currency (yen, dollar, Euro, etc.)

WIT’s solution

After working with the supplier to understand the business needs, WIT consultants designed and implemented the complete solution, from the data mart design and ETL automation to OLAP development and reporting design. The solution components include:

 ETL processes (SQL Server Integration Services packages)

The system was designed for simplified ongoing maintenance. For example, the ETL process automatically checks for updated spreadsheets with part cost information.  The OLAP cubes are reprocessed with the new data on a daily basis, so that the forecasts and reporting are always using the latest data.   Using audit and reporting capabilities within the ETL processes, the customer can easily track the status of the daily updates and identify any potential problems quickly.

Results

Today, the parts supplier has instant visibility into forecast profitability as well as component demand, delivered on an easy-to-use dashboard. The dashboard offers a line graph and a data grid, with the data grid showing the detail level below the line graph.   Business users select parameters to analyze data at different levels and in different combinations. For example, users can easily examine profitability by customer or through each customer’s specific vehicle lines, down to individual models.

The dashboards also include a “bubble-up” feature to ensure that anomalies at the lower grains of data are flagged, even when the user is looking at aggregated data.  In this way, unprofitable parts or other anomalous situations are easy to discover in the broader scope of the business.