After Hussel GmbH was sold by Douglas Holding, the existing data warehouse needed to be transferred from the infrastructure it had hitherto shared with the Douglas perfumery to a new data centre. As a Microsoft Gold Partner, we were able to collaborate with Hussel to complete the analysis of the current situation, the design of the BI architecture, and the planning and implementation of the data warehouse migration within a tight deadline – a mere 4 months.
Initial Situation
As part of Douglas Holding, Hussel’s data warehouse was housed in a central IBM DB2 database managed by Douglas’ IT team. This was fed mainly by the Microsoft retail information system Dynamics NAV, data from which was stored on a Microsoft SQL Server database. In order to transfer data from the retail information system to the central database, it was first exported in CSV format. Numerous database and UNIX shell scripts were used to manage the data warehouse, which was populated daily using scripts via a UNIX scheduler and cron and at jobs.
Reporting took place via a MicroStrategy solution with DB2 metadata databases. The test environment used contained a data warehouse and metadata for MicroStrategy. Version control was provided by Subversion, with project management by JIRA.
Requirements: Improved Performance and a Seamless Transition
The aims for the migration to a new data centre were ambitious. The data storage was to be transferred from a DB2 to an MSSQL data centre, which could be integrated more effectively with the company’s existing Microsoft infrastructure. Another aim was to prevent gaps in reporting when transitioning to the next financial year. The company also wanted to improve performance and to implement a system that could be operated by generalists. This would prevent them bloating their staffing levels by hiring unnecessary specialists. The company wanted the entire system to be transparent and manageable.
The New Hussel Data Warehouse
We were able to fulfil all these requirements. Migrating the data warehouse to Microsoft SQL Server 2014 EE simplifies its operation, as the company already used the tool for resource planning (ERP). Using the Extraction-Transformation-Load (ETL) tool from Microsoft SQL Server Integration Services ensures structured processes and minimises downtime through its error handling, auditing and logging functions. As the inovex auditing template was easily adapted to the Hussel project’s requirements, the implementation process was a rapid one.
Additional protection against downtime is provided by the integrated time and workflow controls in the SQL Server Agent. These raise the alarm if an error occurs, enabling a rapid response by operations staff.
System performance has been considerably improved through multiple changes. Metadata-driven extraction and archiving of data from NAV enables individual tables to be transferred automatically to the data warehouse’s core layer. An entry in the metadata eliminates several manual steps, while a delta load handles large volumes of data. The extraction and archiving of data directly from tables creates considerable time savings for future projects, as the loading mechanism can also be used for other source systems. It also eliminates the need for conversion from CSV format, or for managing the CSV files on file shares. This not only speeds up the process, but also completely removes this source of failure. Thanks to the metadata-driven extraction and archiving processes, integrating additional NAV tables now takes just a few hours.
The new Hussel data warehouse contains templates for filling up the star schema. This standardises the process and adds additional functions, like auditing. Furthermore, SQL Server’s Clustered Columnstore Index in-memory technology enables considerably faster analyses.
To guarantee smooth data processing and prevent performance losses, the metadata databases for ETL and reporting will now also be stored in SQL server databases. As all the relevant databases are now based on Microsoft SQL Server, a comprehensive, consistent backup and operating concept has been created for the Hussel Data Warehouse (DWH).
Development, too, is reaping the benefits of the new, homogeneous Microsoft environment. The SQL Server Data Tools integrated into Visual Studio can now be used for database objects and ETL processes. Their visual interface allows accelerated development, while the automation of deployments between the development, test and productive environments helps reduce manual errors. In addition, managing artefacts in Visual Studio allows the use of version management, which facilitates teamwork and the creation of structured releases.
The separation from Douglas and migration to a new data centre went completely smoothly. A heterogeneous team of Hussel employees, supported by Douglas Informatik & Service and inovex BI specialists, implemented the new infrastructure without a hitch – within the requisite timeframe and on budget. This enabled the month-end and year-end analyses to be performed as requested on the new system.
Andreas Smieja
Project Leader, HusselTechnology Stack
- Microsoft SQL Server 2014 Enterprise Edition
- SQL Server Integration Services
- Microsoft Dynamic NAV
- Visual Studio 2013/SQL Server Data Tools
- Git
- Management Studio
- gitlab
- MicroStrategy Reporting