Creation and development of a data warehouse & data integration

případové studie

AKKODIS CZ

AKKODIS CZ, a member of the ADECCO GROUP, is a leading provider of development and consulting services for the automotive, railway and aerospace industries. It specializes in the development, validation and implementation of manufacturing processes of modern vehicles, power units and transmissions, control systems and software solutions. AKKODIS CZ provides services covering the entire process from ideation through sketches and concepts, design, calculations, electrical, electronics and validation to prototype production.

Akkodis CZ

Client’s requirements

The client sought to implement a data warehouse to meet the reporting needs of the group (formerly AKKA Technologies), for controlling purposes and as needed for management. The key data integration areas were attendance systems, the HR system, accounting and related lists of values. The objective of the solution was to enable connections between data across systems and automated generation of precisely defined data outputs according to standards defined by the group. In addition to this primary objective, the data warehouse was to serve as a resource for further data integration and for reporting areas outside of the project. The solution is intended to be client-managed, including potential implementation of additional changes and extensions in accordance with the client’s capacity.

After the project was completed, further related activities were requested:

  • assistance and support with migration to SQL Server 2016;
  • coordination of activities during migration to new corporate systems (ERP, HR, accounting);
  • continuous reconnection of source data during the transition to new corporate systems;
  • introduction of a master data management system (Master Data Services);
  • consultation on the design of the system integration layer.

Solution

We opted for a dimensional modelling concept for the data warehouse design. The tables were designed to capture historical changes through SCD-2. Connections between data across systems had to be implemented through linkage tables. A Master Data Services system was launched to manage lists of values, and the data from this service was also integrated into data transformations. A set of views was created over the data model, which met the criteria for data outputs intended for the parent company per the client’s requirements.

In addition, the data warehouse serves as a source of data for reporting (Excel, Power BI), which the client implements using in-house capacities.

As part of the follow-up tasks, the integration layer design was also implemented, which makes data accessible to other systems, records integration progress and addresses other related tasks.

Implementation included the following areas:

  • solution architecture design;
  • integration of data from source systems;
  • data transformation and calculation to the DWH;
  • training of the solution administrator/manager;
  • support for and implementation of changes during systems migration;
  • support and assistance during the upgrade to MS SQL Server 2016;
  • ongoing implementation of changes and downstream data integration.

Benefits

The solution met the parent company’s requirements for regular statements and reports with a defined structure. Daily automatic processing saves on time that had been spent on manual report preparation and eliminates the need for repetitive manual tasks, which reduces error risk. Data historization makes it possible to check historical trends and provides another opportunity to check how systems are being used.

Technologies used

The data warehouse was built on a Microsoft SQL Server database running on the premises. Microsoft SQL Server Integration Services (SSIS) were used as the ETL tool for data integration. To manage master data, a SQL Server Master Data Services (MDS) system was designed, demonstrated and integrated.

Subscribe to our Newsletter!

Get a regular stream of interesting information from the world of data technology directly to your inbox.

  • Every month:

  • Latest news on innovations in Business Intelligence and data technologies.

  • Exclusive invitations to professional events, seminars and workshops.

  • Case studies and practical examples from real projects.

  • Inspiration and tips for your data strategy.

Want to contact us?

Drop files here or
Max. file size: 100 MB.
    This site is registered on wpml.org as a development site. Switch to a production site key to remove this banner.