This case study describes the work achieved by element61 together with and at Sita Remediation to set-up a complete Business Intelligence & Management Reporting environment. The assignment at Sita Remediation started with a Business Intelligence roadmap to define the different project-iterations, organization, technology and architecture needed to get started with business intelligence.
The customer
Sita Remediation is part of the environmental department of the French Group Suez. Sita Remediation is located in Grimbergen and is the headquarter for the remediation services of Sita offered in Belgium, the Netherlands and Germany. The main activity of Sita Remediation is the soil remediation of polluted soil with several techniques, applied locally at the polluted sited or in soil handling facilities spread over the countries.
Sita Remediation is specialized in handling complete remediation projects offering their knowledge to get the best economic and environmental solution to remediate the contaminated or polluted site. To rinse soil they use techniques of biological soil treatment, soil washing and thermal soil treatment; ground water treatment is always done locally with mobile installations. Sita Remediation is able to fulfill larger remediation projects and give a new destination to the remediated sites. These larger projects are also known as Brownfields, and serve mostly as new industrialized locations.
The challenges
Sita Remediation was aware of the importance of good management reporting, and had implemented a basic reporting environment per operational application, delivering the most important key-figures to the financial department, but not always delivering enterprise wide insight to the business across functions and processes.
To be able to further expand and give access to correct information to all stakeholders, Sita Remediation tasked element61 to help them out with the design of their future reporting environment, including the choice of a Business Intelligence & CPM technology platform that could grow over the years and will fulfill the current and future needs of Sita Remediation at an affordable price.
During the roadmap exercise several challenges and opportunities for improvement- were identified:
- The complete reporting and database environment was built in Microsoft Access.
Microsoft Access was used as the main data- and reporting environment at Sita Remediation. With Microsoft Access only limited people had direct access to the data and were able to create their own reports. The reporting environment was mainly maintained by the financial department, who delivered reports to the users of the whole company. To create reports knowledge of building queries in Microsoft Access and the data structure of the sources was necessary and therefore not available in all departments of the organization.
- Distribution of the reports, while maintaining a single version of the truth.
The availability of the reports in MS Access was not optimal; all reports could only be consulted by a limited set of users at the same time. Sita Remediation would spread the reports into MS Excel towards other people, all creating their own variant of a pivot table analysis. Within the MS Excel-files people were able to change the figures, enabling them to create their "own single version of the truth.
- Need to be able to have one unique location to store budget and planning data.
With the performance management project, a clear need was identified to automate planning and budgeting and store them in a unique location, without disabling people to consult and analyse these figures.
- Enable more root cause analysis of (financial) figures.
Within the existing reporting environment root cause analysis was only possible by redesigning the query in the report. Redesigning a query is always time-consuming and requires several cycles to get access to the final root cause of a new management inquiry. Sita Remediation needed a solution to drill-down into their information by using predefined hierarchies & analysis paths to find the source of certain data patterns.
- Integration of the data across the different applications.
Within the different applications a basic integration was available, but Sita Remediation felt the need to tighten the integration enabling them to have a more entreprise-wide insight into the financial and operational results of a customer, their produced soils and all of this across time & across the different sites of Sita Remediation.
- Integration of data from several countries in one reporting environment.
Since Sita Remediation has activities in several countries a unique view on the overall result was necessary: currently the integration of the data was a manual consolidation from the different country reporting environments. In the future, Sita Remediation has to be able to get all data from one data warehouse enabling them to report on the individual country company level as well as on the corporate level, with just a simple change of some parameters, allowing for cross-country comparison and benchmarking.
Solution
The first step, in the project element61 conducted at Sita Remediation, was to deliver a clear phased approach into a personalized Business Intelligence roadmap. Next to the roadmap, element61 helped Sita Remediation in choosing an architecture and technology to reach the objectives Sita was pursuing with the Business Intelligence & Performance Management initiative.
The roadmap for Sita Remediation included amongst others:
- Phases within the project, based on the identified reporting subject-areas, business priorities and applications used at Sita Remediation
- Proposal for the Business Intelligence solution
- Architecture of the environment
- Detailed description of the BI methodology for the BI program at Sita Remediation
Sita Remediation decided to implement IBM Cognos Express, a special bundle of IBM Cognos that includes both Reporting and a Planning & Budgeting module to replace the Microsoft Access environment at the front end. The data warehouse has been based upon Microsoft SQL Server 2008 for both the database and the ETL solution.
In a first phase, Sita Remediation has chosen to replace their current accounting reporting from MS Access. This first phase also had to create the necessary buy-in from the organization, proving that a data warehouse and a specialized BI tool on top of it, was able to address the issues the organization was facing. Another important reason to select the accounting application to be first in row is the importance of the financial data: the accounting is master of most data that is present within the organization. The accounting department administers for instance the complete address data of customers and suppliers. It is important to identify the master of your "master data to be sure to capture the right data within your reporting environment and establish the right cross-applications insights. All bookings of actuals are registered within the accounting application and then transferred into the data warehouse to be able to report upon, based on advanced grouping of individual accounts.
In the second phase of the project, element61 deployed the budgeting and planning module of Cognos Express to be able to create monthly estimates of the P&L and the financial statements before the end of month closure. These estimates also are registered into the data warehouse to be able to report upon the difference between the estimates and the actual registered figures at monthly closure. As the estimates are now registered in the data warehouse, the estimated figures can now be reported using the structure of the different groupings that were applied in the first phase of the project.
For Sita Remediation, the follow up of customer proposals and the probability of winning a quote is obviously of big importance and an important business driver. Therefore this part of the process has been implemented in the third iteration that was executed at Sita Remediation.Sita Remediation had to be able to have an overview of the expected turnover of a project, the tons of soil to be cleaned and the proposed cleaning methodology. Based on this information and the probability to close the deal, a planning can be made both for the required capacities on the different sites and budgeting purposes. The figures can be reported by customer, type of cleaning, site, type of pollution and many more analytical viewpoints or dimensions.
As soon as a project has been won, the polluted soil will be transferred from the customer sites to the cleaning plants. Business Intelligence on the soil cleaning process was the last iteration in the project.
At any given time, Sita Remediation has to be able to report how much polluted materials or soils are present on its grounds with the exact location of it. Within the created reports both the weight of the input as the cleaned output is visualized, enabling Sita management to analyse their stock of soil and final, cleaned products. Traceability of soil within the data warehouse will enable Sita Remediation to immediately analyze and trace back the source of any intermediate or final product; the trace back of what happened with polluted soil is a governmental obligation to be fulfilled by Sita Remediation, next to being an important business process in their sector of activity.
All of these subject areas are all integrated into one single data warehouse enabling Sita Remediation to report over multiple figures and facts, coming from any of the different sources, and this for multiple common analysis dimensions. element61 has used their own elementaryTM methodology to implement and document the project.
Sita Remediation has chosen to implement the Performance Management environment for the headquarter first. Once the complete implementation is done for Belgium, they will start with the deployment of their foreign subsidiaries. Thanks to element61s elementaryTM methodology the lessons learnt are easy to identify and will make sure that the known pitfalls in the processes are avoided.
Technology used
Sita Remediation has made a best of breed choice in selecting the front-end and the back-end technology used for the BI program :
- Microsoft is used as back-end environment :
o Microsoft SQL Server 2008 is used as database
o Microsoft SQL Server Integration Services 2008 is used as ETL containing a staging area and a data warehouse target area
- IBM Cognos Express is used as Business Intelligence Front-end reporting environment :
o IBM Cognos Report Studio for the default parameterized reports
o IBM Cognos TM1 Archtitect and TM1 Contributor for budgeting and planning
o IBM Cognos Framework manager for building the semantic layer
element61 responsibilities
element61 has been responsible for the following roles & activities:
- Definition of a BI & Performance Management roadmap
- BI Project Management
- BI Requirement analysis
- Definition of the data warehouse & BI - architecture
- Microsoft Integration services: ETL development
- IBM Cognos Report Studio & IBM Cognos TM1 setup & development
- Installation of all components
- Design & build in a coaching & co-development modus
- Rollout & training of the customer.
Conclusion
Sita Remediation has chosen to have an iterated approach to implement their Performance Management environment. Thanks to the iterative approach, success in every phase of the investment project, convinced the organization to decide on implementing the next phase of the program.
Based on the currently implemented iterations some clear benefits are already visible for Sita Remediation :
- Reporting is not the sole responsibility anymore of the financial and accounting department.As a result the time freed up can be used for finance and controlling specific tasks.
- The reporting cycle is less manual and resulted in important time improvements.
- Data is now automatically available on a daily basis with the most recent data.
- Input for the planning and budgeting process is executed by the business users resulting in more accurate data readily available for management. Management can create a clear vision and strategy on more accurate figures from the planning and budgeting process.
- The data warehouse helps to improve data quality across the organization: by combining data from different sources into one common dimension it becomes clear that some sources are not as up to date as expected, and the issues can be addressed and corrected once and for all.
