Implementation of an end-to end Business Intelligence solution
This case study will describe how Securitas in collaboration with element61 defined and executed a Business Intelligence roadmap and architecture. This BI project included designing and building a data warehouse solution consisting of financial & management reporting and operational reporting, all integrated in one single architecture. The data warehouse is leveraged by an OLAP environment for analysis in combination with comprehensive standard reporting that is currently being used by a large number of knowledge workers at Securitas Belgium. This case study will present the different challenges we faced at Securitas and then the approach that was taken in order to achieve the project goals.
Securitas services a wide range of customers in a variety of industries and customer segments. The size of the customers varies from the "shop on the corner" to global multi-billion industries. Securitas employs more than 260,000 people in 40 countries in North America, Europe, Latin America, Asia, the Middle East, Asia and Africa. Their global market share of the outsourced security services market is approximately 11 percent.
In Belgium there are about 5.500 employees working at Securitas. Next to the head office in Brussels there are also 7 regional offices, from where they service all their customers. Securitas is organized in 4 divisions:
- Services: The services are tailored according to the customers business and needs, and they vary greatly. They may involve various types of access/exit control with or without technical support, or service-oriented services, for example, in public environments, or monitoring business-critical equipment.
- Mobile: a cost-effective option for small and medium-sized businesses, where one security officer serves multiple customers within a limited geographical area and carries out beat patrol, call-out services and opens and closes business premises. Beat patrol can be internal or external or a combination of both, and consists of multiple tasks in addition to crime prevention such as shutting down energy-consuming equipment at night, checking equipment and machinery, access control etc.
- Aviation: property protection, access control, screening of passengers, baggage and cargo, profiling and ID-control of passengers, as well as searching and guarding aircraft.
- Monitoring: Various types of alarms can be monitored at alarm monitoring centers for both businesses and private individuals, e.g. home alarms, fire alarms, assault alarms or burglar alarms. Specially trained operators receive the alarm signal, verify the alarm and forward the response according to instructions that have been established with the customer. This could for example involve sending out an alarm response officer, alerting the fire department or police or simply informing the customer. They also offer manning of customer-specific alarm monitoring centers at the customers premises.
An existing data warehouse and BI solution was already in place but Securitas was experiencing significant problems in exploiting this to its full potential. This data warehouse was implemented by the company that provided the accounting software and was not implemented following generally accepted industry best practices. Next to the problem that this data warehouse was only limited to the financial information of the accounting application, there were significant issues with the performance, maintainability, robustness and extensibility.
Next to the financial information in the accounting package, a lot of information was still residing in custom developed AS400 applications. Getting out this information in a flexible and qualitative way presented Securitas with a number of difficulties :
- Next to the reports in the existing data warehouse there were still several operational BI reports created in the accounting application on the AS400.
- Information was downloaded from the server and made available in several applications, mainly Excel, causing substantial issues in report consistency ('single version of the truth') and accuracy.
- Labor intensive process to create these reports. Data needs to be downloaded, manually cleansed and joined together with other data, a process that had to be redone for each reporting cycle.
- Lack of trust in the figures presented, because of missing information where the figures were coming from (metadata is a crucial component of data quality).
- Lack of historical & detailed information. Incorrect tracing of historical changes in the data leads to incoherent reporting.
- No standardization or documentation.
- Cumbersome & non-transparent process in producing and accessing reports, without an inventory of existing reports, nor search capabilities.
- Limited knowledge of internal IT on the existing data warehouse.
- The lack of central decision taking organ that coordinated the reporting requirements and validated business rules.
Securitas was missing the required detailed and consolidated information to effectively monitor and execute its strategy and therefore there was an urgent need to design and implement an automated solution to support all Performance Management processes.
element61 was selected by Securitas as the partner of choice for dealing with all of the above challenges and designing a new end-to-end approach for Performance Management & Business Intelligence.
Securitas and element61 jointly decided to execute the Performance Management journey with an incremental approach, in which the first iteration consisted in building out the Finance subject area, followed by Operational BI for the Mobile division and thirdly focusing on the crucial HR data mart providing them with insight into all of their personnel data. The start of this program was in 2009 and the different iterations have been delivered in 3-6 month increments since that time. This controlled but steady pace has enabled Securitas to become more mature in their BI processes and architecture, thus ensuring the adoption of the new solution by a substantial community of end users.
Based on element61s methodology elementary, which combines the best elements from proven industry practices, real-world lessons learnt and leading project methodologies, element61 designed and built a Performance Management solution, of which the following aspects are worthwhile being highlighted:
- Central to the new architecture is the position of the data warehouse. The data warehouse functions as a hub
- for storing historical & current data which is sourced from the different AS/400 source systems
- for providing a fully integrated view of data coming from various different business entities and business processes
- for improving the quality of the data to be used as management information
- towards the different front-end applications, such as analysis through OLAP, standard and operational reporting and (later in the program) a dashboarding and/or scorecarding solution
- Although from a KPI perspective only medium to high level summarized data is required, all lowest level transactional data is gathered in the enterprise data warehouse, preparing it for all future needs and enabling root cause analysis of anomalies down to the lowest grain of data available.
- A strong project management methodology, based on element61s elementary was adopted in order to facilitate the close collaboration with Securitas staff. This was done via a phased project planning, a detailed project issue log and a daily log, keeping track of all outstanding activities and a status by person. In this way, although not everybody was present all the time, the communication between the customer and the different team members was much more streamlined and effective. The use of a proven methodology also ensures that consistent documentation is built-up and maintained throughout the project.
- Special attention was given to the training of the Securitas personnel who will be maintaining and extending the data warehouse. A co-development model was adopted so the Securitas staff was involved with the project from day one. They were trained in the different tools that were used, and also in the methodology and techniques used by element61. Next to training the staff, they were also involved in all aspects of analysis and development of ETL, Cubes and reports for the project. Because of this co-development approach, the Securitas staff not only knows all the aspects of the application they helped to build, but they can easily perform maintenance tasks on the implementation. Furthermore, they are also able to extend the current solution, hence reducing their dependence on external knowledge and expertise.
The first phase of the project was done using Cognos 8.4 BI at the front-end. For the second phase, element61 and Securitas decided to take advantage of the improved functionality of the new Cognos 10 release. Prior to starting this phase, it was thus decided to migrate all of the existing BI content from version 8.4 to 10. This was done using the dedicated application IBM Cognos Lifecycle Manager. The entire migration program, including test phase, was conducted by element61 successfully in less than 3 working days.
The architecture of the project consisted in combining the most recent versions of components of the combined Microsoft & IBM Cognos stack.
- Microsoft is used as back-end environment:
- Microsoft SQL Server 2008 is used as database
- Microsoft SQL Server Integration Services 2008 is used as ETL containing a staging area and a data warehouse target area
- Microsoft SQL Server Analysis server 2008 is used as Cube technology
- In first phase: The IBM Cognos 8.4 Business Intelligence solution is used as BI front-end environment:
- IBM Cognos 8.4 Framework Manager is used as business layer
- IBM Cognos 8.4 Report Studio is used as report builder
- IBM Cognos 8.4 Analysis Studio is used as OLAP layer
- In second and third phase: The new release IBM Cognos 10 is used as front-end environment
- IBM Cognos 10 Framework Manager is used as a business layer
- IBM Cognos 10 Report Studio is used as report building solution on top of the Framework Manager model
- IBM Cognos 10 Analysis Studio is used on top of Microsoft SSAS 2008 Cubes
The financial source system includes ASWFIN from IBS.
element61 has been responsible for the following roles & activities:
- Definition of a Performance Management roadmap
- Project management
- Definition of the architecture
- Microsoft Integration Services, Analysis services and IBM Cognos Business Intelligence development
- Installation of all components
- Design & build
- Rollout, training & coaching of the customer both on the concepts & technology deployed
- Migration and upgrade from IBM Cognos BI 8.4 to IBM Cognos Business Intelligence 10
Securitas has been reaping the benefits of their investments in Business Intelligence over the last 1.5 years.
- While before it was very labor intensive and error prone to collect information from the different applications, and putting them together in Microsoft Excel or other tools, users now have a set of default reports that can be used. If new insights need to be collected, users can create their own analysis scenarios.
- While before there was no overview of what reports existed where, and who was creating/using them, now all reports reside in one central IBM Cognos Connection portal. All reports are approved by key users and developed by the BICC report development team.
- Whilst before there were discussions on figures or business rules, now decisions can be taken knowing that the figures shown are according to the standard business and calculation rules and definitions defined and maintained by the Business Intelligence Competence Center.
The complete BI front end environment has been successfully migrated to Cognos 10 and is used by the end users on a daily basis. A number of the target benefits have already been achieved by implementing the financial reporting, the operational reporting for mobile and the migration to the latest version.
All of the above results in Securitas now having a Performance Management & Business Intelligence environment that helps them monitor the execution of their strategy (and finetuning it where & when required), down to the very operational level. The enterprise-wide data warehouse is not completely finished yet (there are still a few iterations in the pipeline for the next quarters) but the intermediate results have had a very positive impact on all the levels of the organization that rely on management information for their decision making.