data analytics in practice - it-novum eng/data_analytics_i… · data analytics in practice...

Users report:

Data Analytics in PracticeSuccessful data projects with Pentaho

it-novum GmbH GermanyHeadquarter Fulda: Edelzeller Straße 44 · 36043 FuldaPhone: +49 661 103 333Branches in Dusseldorf & Dortmund

it-novum Schweiz GmbHHotelstrasse 1 · 8058 ZurichPhone: +41 44 567 62 07

it-novum branch AustriaAusstellungsstraße 50 / Access C · 1020 ViennaPhone: +43 1 205 774 1041

Considering the increasing speed of change in business, it is not surprising that data analysis solutions have risen sharply in importance. Companies have to make much wider-reaching decisions in much shorter periods of time. To do this, they need a reliable database that, on the one hand, contains all the organisation‘s information and, on the other, can be evaluated quickly and easily.

Or at least that‘s the theory.

In practice, however, things are often much different: When beginning a new data project, users often wish they could rely on the advice and experience of others. So, instead of writing another theoretical paper on implementing business intelli-gence solutions, we decided to give users a chance to be heard.

In the following chapters, project managers from different companies and institu-tions describe how they came to their data solutions, the problems they encounte-red along the way and how they overcame them.

The projects in question come from both the private and public sector. They also include solutions that have already been implemented and have been in operation for several years. We have done this to ensure that the cases cited are not isolated, unrealistic solutions, but actual applications that have been in effective use for some time.

The solutions are based on the open-source and market-leading Pentaho Business & Big Data platform.

Our contributors: Public sector

Companies

Michael Becker German Federal Police

Dr. Sonja Kabicher-Fuchs Wiener Wohnen Kunden- service

Rolf Petry Bosch

Helmut Borghorst Deutsche See

Jürgen Sluyterman RSAG

Jan Janke CERN

Devan Manharlal Health Ministry of Mozambique

Jens Junker VNG

Contents

1. German Federal Police......................6

2. CERN................................................10

3. Wiener Wohnen Kundenservice......14

4. Bosch................................................18

5. Deutsche See....................................22

6. VNG...................................................26

7. Mozambik.........................................30

8. RSAG..................................................34

6

Michael Becker, Police Superintendent, and Henry Liebrenz, are working in the Centre for Information and Communication Technology in the Federal Police regional headquarters in Potsdam.

7

German Federal Police

A data warehouse for the German Federal Police

In recent years, the German Federal Police have

set up a data warehouse that contains all relevant

information for preparing their PES data – a key set

of police statistics. This information comes from a

variety of source systems drawn from the 160

districts, 80 inspectorates, 10 directorates as

well as the national headquarters. This is then

processed and checked for quality assurance in

the new data warehouse. This information is then

available

almost instantly for answering queries from

Parliament or the Feder Ministry of the Interior.

8

The German Federal Police data project

Prior to the introduction of a data warehouse, the Federal Police Service recorded its PES data in some 72 Excel spreadsheets with each procedure that was statistically relevant to the police having to be entered manually. Each local police station collected the data and sent the Excel spreadsheets to their respective higher-level directorates. These, in turn, reviewed the information, collated the data and then forwarded it to the national headquarters. The national headquarters then reviewed the information and consolidated this on a nationwide level. This resulted in a month-long delay before the data could be made available to the various official authorities.

Through data warehousing and the use of comprehensive data integration processes, the Federal Police have created a solution with which they can more efficiently handle their many inquiries – an average of three to four a day from Parliament, the national headquarters, the Federal Ministry of the Interior, the EU and Frontex. The data is then cleansed and codified for quality assurance reasons, as the data supplied by the different police inspectorates is always collected differently or is not complete and therefore cannot always be compared with each other. Using their current solution, officials can create analyses with just a few clicks and send them to the requesting department without any delays.

Can you describe how your data project works?

We collect the entire PES data, which contains a total of 20 police responsefields, into a data warehouse. These fields include, among other things, the number of individuals crossing the borders, police search hits, criminal offences, air security, railway police and asylum statistics. To do this, we rely heavily on Pentaho Data Integration (PDI), the Schema Workbench and the BI server supplied by Pentaho.

We are constantly using Pentaho, especially PDI, for numerous developments. We handle tasks such as data integration, data aggregation, data delivery to systems etc. exclusively using PDI. Where previously we had to carry out a lot of tedious and time-consuming work with scripts, we can now complete numerous tasks almost instantly.

Interview with Michael Becker, German Federal PolicePolice Superintendent Michael Becker works in the Centre for Information and Communication Technology in the Federal Police regional headquarters in Potsdam. He is a team leader there with responsibility for developing open-source solutions.

9

For example, we use PDI to collect booking data from all time and attendance terminals of the German Federal Police throughout Germany, carry out precalculations and transfer this data to the national headquarters software so we can plan schedules and handle tasks.

We also use it in another way: In order to determine the daily number of officers who must be available for duty, we feed in the nationwide flight plans from the federal airport coordinator in Frankfurt am Main into our system each day. This data is then enriched with information from the respective local airports and forms the basis for calculating the number of officers required to be on duty.

What was special about your project?

We based our approach to setting up our data warehouse on our PES data. The reason for this is that we had to take a slightly different tack than, say, organisations that can fall back on quality-assured data such as that taken from an ERP system. With us, quality assurance has to be carried out partly during our ETL processes, but then also afterwards, because for technical reasons our upstream systems can only perform very marginal quality controls. We‘ve also not followed the traditional reporting approach in terms of data output.

Why do you rely on Pentaho?

We had several other products we tested in the run-up to using Pentaho, but since we decided rather quickly to use PDI as our tool, the rest was pretty obvious. And it has to be said that we‘ve never found a better – and above all more comprehensive – software solution.

A further advantage with Pentaho is that – with appropriate adaptations – you can run the Pentaho Community Edition as a production system and then decide at any time whether you want to stay with it or switch to the Enterprise version. If you decide to use the Enterprise Edition, the price-performance ratio is also a decisive factor compared to other established software solutions. After working with the Community Edition now for two years, we‘re in the process of moving over to the Enterprise Edition.

10

© 2007 CERN, for the benefit of the CMS Collaboration

11

CERN

The world’s largest research institution bases its business computing on Pentaho

CERN is the biggest research organization in the

world. In 2014, CERN migrated from Business

Objects to Pentaho for data integration, reporting

and analytics. CERN bases its entire business

computing on Pentaho and uses all components

of the Pentaho stack: data integration, reporting,

analytics and dashboards. With more than 15,000

users, CERN runs one of the biggest Pentaho

implementations worldwide.

12


To work at CERN is a unique experience every day. We face very specific challenges in the area of business computing affecting especially the areas of data warehousing, business intelligence and reporting. More than 15,000 potential users are exposed to content served directly or indirectly by Pentaho.

For CERN’s specific needs, we have adapted the Pentaho platform. To keep all content created by CERN under control we have built an infrastructure around it. The tools we have developed provide enhanced search and scheduling capabilities, add support for delegated management of schemas including approval and environment promotion features as well as an integration with the “git” version control system. These tools help us to operate in an environment with more than 15,000 potential users.

Why did CERN decide to use Pentaho?

We started to use Pentaho in autumn 2014 after a longer BI tool selection process that began with a first evaluation of potential applications in 2012. In the past, we have been relying on in-house developed reporting solutions alongside Business Objects (now owned by SAP). Today, we are using both the business analytics and the data integration products from Pentaho.

Interview with Jan Janke, Deputy Group Leader of Administrative Information Systems at CERNJan Janke is responsible for the entire business computing infrastructure of CERN. This includes the internal development infrastructure, the central data warehouse and the related BI and reporting services as well as the maintenance and development of other major administrative applications.

13


With the data integration tools (PDI), we were able to stop relying on manually crafted PL/SQL and shell scripts for important ETL processes. Our new central (near real-time) data warehouse relies on PDI as tool of choice for all ETL processes. PDI also helps us to be agnostic in terms of data sources to read from or write to.

We have chosen Pentaho because it provides us with a powerful data integration tool and brings an appealing solution for our general reporting, data analytics and visualization needs. The fact that most of the tools are available as open source software allows our own developers to easily inspect existing code and integrate our tools with it. Here, the fact that Pentaho is written in Java comes in especially handy as Java or rather the JVM is also our technology of choice for all in-house developments made by CERN’s AIS group.

Despite all the hype made around Big Data, “simple” reporting and data visualization remain essential for us. Pentaho solutions like CTools or the Report Designer are equally important as for instance the Analyzer which is part of Pentaho’s Enterprise version. All tools have their strong and weak points. We hope that Pentaho together with the community continues to maintain and further evolve them. While PDI is certainly an important corner stone of the Pentaho offering, we hope that strong innovation continues to happen in the business analytics area, too.

15

Wiener Wohnen Kundenservice is the largest municipal

property management company in Europe. The

company manages around 220,000 community

apartments, 47,000 parking spaces and over 5,500

restaurants and pubs. Wiener Wohnen Kundenservice

relies on analyses and reports to cover its quality

assurance needs. This ranges from personnel planning

and the evaluation of tenants‘ concerns to professional

systems monitoring. The company also has to produce

a variety of reports for which they use Pentaho as their

platform of choice.

Wiener Wohnen Kundenservice

Monitoring more than 1.8 million customer inquiries each year

16


As one of the largest property management companies in Europe, Wiener Wohnen (WW) looks after around half a million tenants, which represents about a quarter of Vienna‘s urban population. The system records over than 1.8 million customer enquiries per year. Monitoring as well as quality assurance, data analysis and reporting are special requirements that we place on our system.

We began our project in 2011. Our goal was to generate pre-defined analyses (reports) on a regular basis and to carry out individual analyses using existing key metrics and dimensions. In doing this, these metrics and dimensions needed correspond to those provided by the old database and also offer additional processed data.

The basis for our work is our call centre data warehouse, which takes data from the telephone system and an operational source system. Using a large number of cubes, key metrics, dimensions and reports, we analyse their performance and derive improvements based on this information.

Interview with Dr. Sonja Kabicher-Fuchs, Wiener Wohnen Kundenservice GmbHProduct Owner/Business Analyst, Dr. Sonja Kabicher-Fuchs is responsible for the professional development and support of the business intelligence solution at Wiener Wohnen Kundenservice.

Pentaho Reporting

Pentaho is the leading open-source platform for data integration, business analysis and data visualisation. It is also used by many companies and public institutions worldwide. By using Pentaho reporting, organisations benefit from:

17


Pentaho has two major advantages: Firstly, the software is a mature and reliable open-source product and secondly, it makes it possible to flexibly develop applications further according to current project requirements.

By using Pentaho we are able to cover our quality assurance needs by using data analysis and reports. This ranges from personnel planning and the evaluation of tenants‘ concerns to professional systems monitoring. In addition to quality management and data analysis, we also generate our reports using Pentaho.

Interactive self-service report creation Web-based reporting for business users Report designer for power users Quick results using in-memory caching Support for common output formats: HTML, Excel, PDF Direct reporting for NoSQL

19

Bosch

Global project reporting

Large companies often have the problem that they

have to monitor projects around the world. Bosch

is a good example: This world market leader has to

manage and maintain more than 1,500 customer

projects around the globe. To do this, Bosch uses

a company-wide reporting platform – its global

reporting system. It integrates numerous source

data systems and is not only available to internal

departments and employees, but also allows

suppliers and customers to create and retrieve

reports.

20

Interview with Rolf Petry, Bosch Service SolutionsRolf Petry is Group Leader for Global IT Applications and Worldwide IT Service Coordination at Bosch Service Solutions in Frankfurt. In this capacity he is also responsible for the company‘s global reporting system.


At Bosch Service Solutions, we use a global, company-wide reporting system for our customer projects. In doing this, we use data from different source systems (and to a certain degree also customer systems) to provide key operational metrics to manage projects, but also key financial metrics for management at all levels. A particular challenge is to merge data from different source systems in such a way that uniform sales and cost reports can be generated.

This global reporting system is linked to a wide variety of systems (including various telephone systems, time tracking systems, email systems as well as SAP systems for HR data) and several external customer databases. The metrics include, among other things, incoming calls, calls answered, customer terminated calls, turnover, expenses and profits both daily and per project.

21

Who works with the reporting portal?

All our customers can access the system and the reporting function. In addition to standardised reports that can be used for all projects, special reports are also available. Using self-service cubes, our employees can perform analyses independently, without needing the assistance of the IT department.

In order to calculate the very different and diverse price models, we use a connection to Excel, so that the finance department can continue to work with their Excel files, which are imported via a PDI process that runs overnight. This enables us to calculate the turnover for all projects. This is very important, because it lets our colleagues create their own price models independently and IT doesn‘t have to get involved as long as users stick to the basic model. Using 10 basic models we can generate hundreds of price model variations.


After evaluating a number of systems, we chose Pentaho because it provides us with the greatest range of applications, both in terms of data source connectivity and output capabilities. But it was also important to us that the software we chose not be very complicated. It needed to be something that a database expert could easily master in a realistically short period of time.

Other reasons were the fact that it was a standard software package whose basic system could be installed by external personnel, but that could be further developed by our own employees. Also, Pentaho can be opened up to external users (customers) without entailing any security risks. Plus, its cost efficiency and the open-source aspect of it also played a role. Another key factor was that Pentaho has already been introduced into other areas of the company, so this allowed us to exploit existing synergies such as prior experience with report creation, etc.

23

Deutsche See

Pentaho in the food industry

Deutsche See is the German market leader for

fish and seafood. For years the company has been

working with a comprehensive sales management

system based on Pentaho. The solution provides

management and sales with access to sales data

and other key sales metrics. The reports can also

be accessed from mobile devices while on the

go and can be flexibly compiled into individual

reports. Currently, the portal contains more than

100 reports, which will gradually be extended and

expanded.

24


At Deutsche See, Pentaho is used by more than 300 users in the most diverse areas of the company: purchasing, controlling, marketing, production, sales, logistics and management. Using Pentaho, we store all sales-relevant data such as customers, receipts and items along with their quantities and sales turnover in 20 cubes and 300 dimensions and KPIs.

The sales portal includes various standard reports, predefined individual analyses and preparations for customer visits for the sales team. The portal displays data according to different topics, such as customers, companies, sales, services, markets etc. So, for example, a Deutsche See customer advisor can call up data for each customer from anywhere in the world. All customer-related data such as contact partners, contact details, turnover generated or purchased products is stored in the system. This even includes the current weather at the customer‘s premises. Currently, 100 reports have been created and more are planned for the future. Employees can even create their own reports.

Interview with Helmut Borghorst, Deutsche SeeHelmut Borghorst is Head of Internal Sales and Software Development at Deutsche See. In addition to several proprietary developments in the company‘s merchandise management, he also looks after the company‘s reporting platform.

Pentaho Business Analytics

Pentaho Business Analytics is a powerful application for data analysis. It is also used by many companies and public institutions worldwide. With Pentaho Business Analytics, organisations benefit from:

25


Since Pentaho is open source, the first thing that comes to mind is the lack of licensing fees. But of course, that‘s not everything: Pentaho is a very powerful tool that offers a huge amount of data and its corresponding graphical representations. Plus, further development is progressing very quickly and support can easily be purchased as needed.

Previously, we worked with Excel spreadsheets and PDF reports, but at some point we realised we needed a more flexible solution. We then introduced Pentaho Reports and Pentaho Analyser, which we can use to automatically send out reports. But just sending out reports wasn‘t enough. This is why we decided to introduce Pentaho CTools so that we could set up a web-based reporting portal.

The most important things the project has achieved are greater flexibility and the elimination of the many different individually produced Excel spreadsheets we used to have. With Pentaho, our employees have a central access point and work area and can also access a uniform database. Our primary reasons for introducing Pentaho were its higher flexibility and lower costs. A comparable solution from commercial vendors would have been many times more expensive to implement.

Complete platform for data access, integration and evaluation that includes Big Data sources such as Hadoop and NoSQL

Interactive visual analysis with drill-through, lasso filtering, zooming, heat raster, geo-maps, sunbursts and highlighting of attributes for greater insights

Clear visualisation, interactive reporting, ad-hoc analyses and customised dashboards

Complete range of business analytics paired with flexible data integration

27

VNG Handel und Vertrieb

Automating compliance processes in the energy market

To fulfil the complex requirements of federal and

EU energy authorities gas trader VNG Handel und

Vertrieb has automated the compliance processes

that integrate data from numerous data sources.

These data processes help to realize six-digit

savings per year.

28

Interview with Jens Junker, Trading and Risk Management Systems at VNG Handel und VertriebJens Junker is responsible for the trading and risk management processes at gas trader VNG Handel und Vertrieb.


VNG Handel und Vertrieb is the energy retail business of VNG holding. With Pentaho Data Integration we connect our different data systems and automate processes. We use Pentaho as a universal tool for reports as well as mass updates.

VNG’s application landscape consists of a row of valuation systems, the trading system [for gas trades, amongst others], execution and deduction systems, SAP and more. We use Pentaho as interface tool to transfer data from one system to another and to combine data.

In addition, we automate business processes with Pentaho: thanks to Pentaho we could reduce manual work for creating UTI numbers to zero. All trades that are affected by EMIR reporting directions need a UTI (Unique Transaction Identifier; required by European Market Infrastructure Regulation for the regulation of over-the-counter derivatives, EMIR).

29

Why do you use Pentaho?

When I came to VNG, they just had concluded an evaluation of ETL tools (Talend, SAS and Informatica). The winner was Pentaho Data Integration. Until this moment, I hadn´t heard of Pentaho but I immediately started to love this tool when seeing how easy it was to solve all kinds of data logistic and processing problems („less code, more design “).

We not only successfully replaced OWB and PowerMart with Pentaho but also found out that it was superior of our 2014 introduced Inubit system as it was a lot less complex. Pentaho‘s visualizing capabilities for jobs and transformations make it easy to convey data streams to our business users. Looking back, we started with version 4.x and did the upgrade to 8.2 in 2019.

How much do you save with Pentaho

With a simple open source tool, we realize six-digit savings per year. The base of our trades is the data in our portfolio valuation. The number of transactions increased strongly in the last years and has become very complex. Valuations have to be archived to show to the authorities when requested. A valuation consists of 5.3 million rows summing up to 7.6 million rows with 125 columns per day. This makes it impossible to archive valuations in our trading system Allegro. With the ETL process implemented in Pentaho, we achieve a compression rate of 91%. This has led to huge savings.

We also save a lot of time at audits: when the auditors need certain data, we just have to create a transformation in Pentaho and export the data. This has made us much more flexible when fulfilling our compliance requirements.

31

Health Ministry of Mozambique

Pentaho helps to save lives

Analyzing health data to provide better health

services is crucial for governments, especially in

developing countries. The Ministry of Health uses

Pentaho to integrate, analyze and visualize health

data from different sources to provide better

health care services to the population.

32

Interview with Devan Manharlal, Health Ministry of MozambiqueDevan Manharlal is Health Information Systems Senior Specialist and has implemented the Integrated Human Resource Information System of Mozambique.


Mozambique runs esipSAUDE, the Integrated Human Resource Information System to improve the country’s health care services. Since 2012, we use Pentaho to integrate data from various health information systems and other data sources to provide health services and prevention programs for the country.

Before that, data was isolated and could not be combined to get information about the allocation of health workers and resources. We integrate the data using Pentaho Data Integration and present it on the project’s website using CDE dashboards.


With Pentaho, we were able to improve the management of health data in Mozambique. The availability of open source data integration and analytics tools makes it easy to provide important services within the limited budget of a developing country.

We use Pentaho not only to visualize the data that our managers need but also to make this data available for them. We created „actionable data“ that they can explore and analyse themselves to take decisions and actions. Pentaho made it all very easy.

33

Pentaho is the solution for many problems we had with data integration and system interoperability as data is integrated from many different sources. Some of our systems had limitations in terms of reports and data access. With Pentaho we were able to solve these problems.

Also, as a developing country, we face budget problems when introducing commercial software. Using open source solutions like Pentaho we can use all the money for the implementation and the training of the staff.

35

RSAG Rhein-Sieg Abfallgesellschaft

Integrated analyses thanks to blending of SAP and external data

Integrating their data warehouse with SAP and

industry software waste management company

RSAG has gained important insights in their

material and capital flows.

36

Waste management company RSAG uses different software solutions (SAP and industry solutions among others) to keep track of their material flows, organize storage quantities and to coordinate vehicle deployments. Before a central data warehouse was introduced, this data remained isolated in the individual software systems. The data was not collected centrally in one place, so the monitoring of stock levels or material flows could only be carried out at a great expense of time and effort.

Automation of manual data processes

Every recycling center and waste disposal site had to send their data separated tot he headquaerter where they were transferred by hand into Excel sheets to create reports. Despite being a SAP user RSAG decided against a SAP-based solution and for a data warehouse with interfaces to SAP: “We wanted to integrated our numerous data sources to profit from a central database for creating analyses and reports” says Jürgen Sluyterman, IT director. “After evaluating different market solutions we ended up with Pentaho because we could test the community edition for free and and found out that the implementation of the data processes was very easy.“

To gain a consistent, consolidated data base, RSAG introduced a data warehouse based on Pentaho. The platform integrates different data sources and has become the central data base of the company for all kinds of analyses and reports.

Jürgen Sluyterman, Director IT at RSAG Jürgen Sluyterman is responsible for IT systems and infrastructure of municipal waste management company RSAG

37

Integration of SAP Data

Analyzing data from SAP systems is essential to gain important insights in the business but implementing the data processes can be cumbersome. Hitachi Data Connector for SAP ERP and Business Warehouse (HDC for SAP) taps SAP systems to load their information in Pentaho use them for reports and analyses together with data from other sources.

This connection provides a comprehensive view on processes and KPIs. Jürgen Sluyterman points out that there were some challenges with integrating the SAP data. As SAP systems are hosted at the association of communes, accessing the data was almost impossible. However, the SAP reports provided by the association were to inflexible to reflect changes in business operations. Interfaces to operational systems were needed to enable accounting for services.

Hitachi Data Connector enabled RSAG to load data from SAP in Pentaho without touching SAP systems themselves. Data is loaded on a daily base or when needed, f.e. before monthly balances. This means that changes in the associations’s structure and processes are immediately reflected instead of manually adapting reports.

Jürgen Sluyterman points out that the bottleneck is IT: business analysts usually depend on the willingness of admins to provide them with the needed data. Tools like the connector help to avoid these conflicts by leaving data systems untouched.

Within the Pentaho partner network, it- novum also occupies a prominent role: We are one of the top 3 partners in Europe and, as a Pentaho Authorised Training Partner, the only authorised training provider in the German-speaking market.

With over six years of implementing Pentaho’s solutions, it-novum is one of our most experienced partners in Europe. They combine excellent technology skills with a deep understanding of business processes, leveraging the power of Pentaho’s big data and business ana-lytics capabilities.

Paul Scholey, Pentaho/ Vice President EMEA & APAC

As a Jedox Platinum Certified Partner, it-novum develops solutions for data warehousing, analysis, reporting and planning.

Together with its partner Cloudera, it- novum develops analytical applications based on Hadoop and Pentaho.

Our Partners

„

it-novum About us

Why you should get in touch with it-novum...

We profi tably implement these Business Intelligence and Big Data advantages for compa-nies:

— 360-degree view of your customers — Departments evaluate their own Big Data themselves thanks to Self-Service Analytics — Identifi cation of new revenue sources through the intelligent use of company data — Cost savings by using a data warehouse — Avoid the costly and error-prone spreadsheet chaos

If you‘d like to put these advantages to work in your company too, we should get to know each other!

it-novum off ers comprehensive services for your Big Data analytics project: — Data engineering — Implementation of Data Warehouses and Data Lakes — Pentaho/SAP Connector for processing SAP data — Pentaho/HVA Connector for video stream analysis — Predictive analytics and machine learning — Dashboards and data visualisation — Embedded analytics

Leaders in open-source solutions and consulting for businesses

it-novum is the leading IT consulting company in the German-speaking market for open source-based business solutions. From our headquarters in Fulda and our off ices in Düsseldorf, Dortmund, Vienna and Zurich, we serve medium-sized and large companies as well as clients in the public sector.

As a Hitachi Vantara Preferred Partner for Big Data Insights and IoT, we are experts in the use of Pentaho. Using our expertise in consulting, training and support, we help companies gain insights from their data and successfully design their data-driven projects.

Your contact:

Stefan MüllerDirector Big Data Analytics & IoT� [email protected] +49 661 103 942

data analytics in practice - it-novum eng/data_analytics_i… · data analytics in practice...

Documents