TimoCom Soft- und Hardware GmbH’s core business comprises a wide variety of IT-based transport and logistics services. The company, which is located in Erkrath, near Düsseldorf, is leading the European market with its TC Truck&Cargo® product, a digital freight and vehicle exchange.
Monitoring and controlling the business processes involved in running TimoCom Soft- und Hardware GmbH requires high-end business intelligence components, targeted agile BI Team operations, fast reaction times and a high level of flexibility. As the company’s existing BI solution was no longer able to reliably fulfil these requirements, inovex GmbH and Axians GmbH helped to analyse the current situation, design a new system architecture, and plan and implement the migration of the company’s data warehouse.
Starting Point
TimoCom’s organically developed system was no longer capable of fulfilling its increasingly complex requirements. The reasons for this included the system’s high operating costs (including staff costs and licensing), its insufficient performance and functionality, and its highly complex loading processes. Its convoluted architecture and associated dependencies also made further development very difficult.
Technologically, the business intelligence environment was based on open-source ETL and reporting tools, and a column-oriented database which was no longer supported by the manufacturer.
The Agile BI Project
The team’s remit covered everything from designing the system (including architecture, definition of work packages, and planning) to handling infrastructure issues (such as server installation and backup strategy), right through to implementing, deploying and launching the Microsoft-based business intelligence solution.
Throughout the project period, agility, flexibility and reusability were the main priorities. In the spirit of the “agile manifesto”, feedback flowed constantly from the stakeholders to the iterative implementation of each task, thus ensuring a high-quality result.
In defining the project scope, the various BI domains, such as finance and key account management, were prioritised according to their level of criticality. ETL procedures, reports and key figures from the most important areas needed to be migrated by the end of the project period in order to provide the usual insights.
The incremental construction of the new data warehouse, the number of source systems and their continuing further development, as well as the unclear future requirements, also made it necessary to select the most generic approach.
The data model and loading procedures were developed to be controlled using metadata and designed to permit a high level of automation, thus allowing architectural adjustments to be made with minimal resources.
One of the key requirements was to enable the team to independently develop high quality business intelligence solutions for additional domains and to ensure their smooth operation, both during the project and (particularly) after it ended. In order to achieve this aim, the TimoCom employees received comprehensive training from both inovex and Axians.
The Scrum agile method was chosen for the project. This framework enables self-organising teams to carry out independent, efficient development with high quality results, even in a project team which, as in this case, at times had more than ten members. The agile process was supported by a Jira software system in which the backlog and the associated sprints were defined collectively by the team and tracked using the Scrum board.
The new Data Warehouse for TimoCom
The new, homogenous BI architecture is based entirely on Microsoft technologies.
The target architecture is a traditional Kimball-style data warehouse bus matrix combined with a staging layer where historicized data is stored for reference.
The logical layers of the data warehouse are modelled using Microsoft SQL Server Enterprise Edition, while SQL Server Integration Services (SSIS) extracts the data from the source systems. The multidimensional Business Layer, including KPIs, hierarchies and optimised query performance, is created using SQL Server Analysis Services (SSAS) cubes.
While the reporting system with its impressive visualisation capabilities is based on SQL Server Reporting Services (SSRS), Microsoft Power BI or Excel, depending on the target group.
Automated object generation was facilitated using the Business Intelligence Markup Language (BIML). BIML allows patterns and metadata to be used to generate SSIS loading procedures and projects. Essentially, the migration project could be divided into the following sections:
Automated Creation of Database Objects
Database objects are generated for the logical data warehouse layers Landing Zone, Full Stage and Core Data Warehouse. In addition to tables and views, indices and constraints are provided to ensure optimal consistency and performance.
The objects are generated using metadata tables containing both content from the source systems and control data for selecting the tables and columns within the various source systems.
Automated Creation of Loading Procedures
In order to load data into the data warehouse structures, SSIS packages are generated using different patterns. First, the data is extracted unchanged from the source. It is then enhanced using various metadata fields and converted to the target data types. Once it has been historicized in a Full Stage, it is then transferred to an evaluation-optimised star schema.
The aforementioned layers form the basis for the integration and dimensional preparation of the data, and the resulting architecture ultimately provides relational and multidimensional data structures for various reporting and analysis purposes. In the same way, frontend developments were also implemented efficiently and in clearly defined workflows.
Several independent environments are used during the development cycles. Once a release has been completed, it undergoes technical and content quality checks. The goal is for the developed product to be rolled out from development to production without any code changes.
The high level of reusability enables adjustments and optimisations to be made quickly and efficiently. BIML’s metadata-driven approach was instrumental in ensuring the project’s successful on-time completion, as important design decisions necessitated continual complex modifications. A manual implementation would have been too resource-intensive within this timeframe. An additional benefit of automation is the homogeneity of the objects produced and the lower margin for error.
This project resulted in a BI landscape which had a clear structure, enabled a well-defined assignment of development activities to the individual architectural components, and won over everyone with its extraordinary flexibility and expandability.
The reconstruction of the BI stack posed major challenges. The tight deadline, especially, made it important that we joined forces with a strong partner. The way the project was handled proved to us that Axians and inovex were exactly the right choice. The combination of our internal team and their external support was the key to success. The entire team were enthusiastic and they all worked closely together to create the basis for our BI future.
Stephanie Budzyn, Head of Internal Products, TimoCon
Head of Internal Products TimoComTechnology Stack
- Microsoft SQL Server Enterprise Edition
- SQL Server Integration Services (SSIS)
- SQL Server Reporting Services (SSRS)
- SQL Server Analysis Services (SSAS)
- Microsoft Power BI
- Business Intelligence Markup Language (BIML)
- Visual Studio / SQL Server Data Tools
- Oracle-Datenbanken (Datenquellen)
- Git
- Management Studio
- Bitbucket
- Jira
- Confluence