At times, requirements in reporting as well as expectations in analysis and planning functionalities at a company are growing faster than the technical infrastructure can accommodate. At the end of 2013, the Herne-based publishing house NWB Verlag, too, reached the point of completely rethinking its current BI solution approaches. Jointly with the BI team of noventum consulting GmbH, a fundamental renewal of the BI architecture was achieved in the process. Subsequent to the implementation of an enterprise DWH as basis for reporting and analysis, the quality improvement of the planning processes was scheduled for 2015. Due to the successfully completed project, the comptrollers of NWB Verlag are now able to present more detailed and more flexible planning results that are of higher quality, and can do so at considerably reduced effort.

NWB Verlag is the leading specialised German publishing house for tax law, commercial law, and accountancy. The publisher's portfolio includes databases, software, magazines, books, and seminars. Due to the multitude of individual products and product groups within the publishing house, the 250 employees find themselves in a complex planning process.

The Initial Situation

Due to the complex product structures and the missing technological support, the planning processes at NWB Verlag were very protracted. The necessary planning functionalities (e.g. application/input, distribution, allocation) had been implemented with the help of a multitude of Excel documents. The planning required laborious work steps that were prone to error: manual copying of data and updating of Excel references, maintaining and enhancing complicated Excel documents, constant analysis and correction of errors, etc. In order to keep the complexity of the Excel documents in check, the individual planning tracks were – for the most part – separated from one another. There was no integrated planning result for the company as a whole. Under these circumstance, a scenario calculation and the comparison of different planning versions were unthinkable.

The project objective

Subsequent to the successful implementation of an enterprise DWH for the reporting and the analysis of actual data from the Kloppotek publishing house system and the Gypsilon financial accounting system, an improvement of the planning was being strived for. For this, the controlling department of NWB Verlag, jointly with the noventum consultants, conceptualised a completely new planning process, taking into consideration the technical support required for this. The objective was to increase the process quality and to accelerate the planning process at the same time. For this, the effort for purely administrative and technical activities such as the manual consolidation of data had to be reduced significantly in order to generate more time for content-related work. The technical and content-related quality of the panning results also was to be increased at significantly reduced effort. While, until then, scenarios, planning versions and their comparison had been largely unrealistic due to the excessive manual effort required, they were to be made possible in the future. In short: The whole planning process was to be set up more intelligently and more smartly.

The project result

Nowadays, the NWB Verlag publishing house has a new planning solution with which the defined objectives were achieved.

The planning process was revamped and implemented in a planning data basis which is integrated completely into the central enterprise DWH. The different input from product centres and cost centres as well as persons responsible for the project can be combined with one another. This way, a detailed and integrated planning result can be generated at the push of a button. With the help of a multi-dimensional cube, said result can be analysed and linked directly to actual data.

The initial data capturing continues to be carried out via Excel. For this, approx. 20 standardised templates were defined for data entry, in which all drivers required for and relevant to the planning process of NWB Verlag (e.g. sales figures, average prices, honorarium rate) are taken into consideration. For creating the plan, the drivers for the different products, cost centres, projects, etc. are provided by the respective responsible parties in a total of approx. one hundred Excel documents. The automatic initialisation of these files, based on historic values from the DWH, enables all parties involved to carry out an efficient and sensible planning.

Within a few minutes, the captured data can be loaded into the planning data base at the push of a button and transitioned into a detailed and integrated planning result. While doing so, the individual input files are combined with one another and information is being retrieved from the enterprise DWH – for example, to break down a plan value as realistically as possible based on historical data. The planning results are then available instantaneously and can be analysed immediately with the already familiar analysis functionalities for the enterprise DWH. In this, the plan values can be separated out based on all dimensions (e.g. cost object, cost centre, bundle), and all attributes and aggregation levels already known from the actual data can be utilised. Furthermore, for a plan value, it can be clearly traced and comprehended from which input file it was derived. This way, planning results can be changed targetedly through the adjustment of the drivers in the files and the reloading. In this, the user can decide whether an already existing plan version should be updated with the changed input or whether a completely new plan version should be created. This way, various planning scenarios (e.g. worst, median and best case) can be played through and put in relation to one another.

The importing of Excel documents, their integration, and the mapping of the planning logic (e.g. offsetting, distribution, allocation) is carried out with the help of the SQL Server Integration Services (SSIS). In order to minimise the effort required for the modification of the SSIS packages in case of necessary future changes in the planning process (e.g. new templates), the implementation was carried out with the help of the BI Modelling Language (BIML). Due to the utilisation of BIML, maximum robustness when reading in Excel documents is achieved. The multi-dimensional analytical basis is presented by SQL Server Analysis Services (SSAS) Cubes, which can be analysed by the comptrollers via the pivot tables in Excel that they are familiar with.

Summary and outlook

The effort for the administrative part of the planning processes was reduced to a minimum. Now, the specialised departments can focus much more intensely on the subject-matter and content-related aspects of their plannings and within a shorter period of time create, analyse and perform the final coordination of different planning scenarios. The quality of the content of the planning results was significantly increased at reduced effort.

The decision-makers at NWB Verlag are very happy with the phased further development of their BI architecture. "Now, we can work significantly more efficiently. I am expecting a pronounced increase of the planning quality at half the investment of resources," said Bettina Renner, group leader Controlling at NWB Verlag. The cooperation with the consultants of noventum consulting was expressly praised by the managing director, Mark Liedtke: "Due to their profound understanding of our requirements concerning controlling and their comprehensive solution expertise in BI/Data Warehouse environments, the project was in the best possible hands with noventum."

In a follow-up project that is currently on its way, NWB Verlag are – with the support of noventum – expanding the new planning system in order to create a fully integrated forecasting and/or extrapolation process.

 

Road to Azure Data Platform

Sicherheit und eine solide Grundlage
für zukünftige Analytics-Architekturen