Implementation of an end-to end Performance Management solution
Starting out as a family business more than 50 years ago, founded in 1947 by Eduard Cuyvers, the Echo Group is currently Europe's second largest producer of pre-stressed & re-inforced floor elements used in private housing, utility construction and apartment buildings. Within the Echo-group a separate place is reserved for Echo Engineering, which delivers, on a global scale, know-how and technology in terms of production machines and equipment producing floor elements up to the construction of entire factories within this area.
The Echo Group was coping with a number of difficulties throughout their entire information chain:
- Previously an investment was made about 2 years ago in a (so-called) data warehouse, consisting of a limited number of stand-alone cubes & reports directly targeting the various AS/400 source systems. This application did not live up to its expectations due to lack of skills in using and supporting the solution, slow and non-transparent cube build processes and lack of proper management of the solution
- Both operational and management reporting, as well as planning and budgeting were completely Excel based processes, with all associated problems
- Labour-intensive processes for planning and budgeting which were not automated and were very error prone
- Very difficult to track planning and budgeting progress or even get a correct and comprehensive view on the figures at certain points in time
- Wrong perception of current data quality, resulting in reports containing incorrect or not up-to-date information
- Lack of historical & detailed information, standardization & documentation
- Cumbersome & non-transparent process in producing reports, without an inventory of existing reports, nor search capabilities
- Complex, undocumented, non-transparent and at times unknown or not thoroughly tested AS/400 source systems
- Central & very demanding role for IT being a small group with limited capacity, it took a long time to create new reports or make modifications, next to the execution of a vast number of manual interventions due to the existing architecture
Echo Group was missing the required detailed and consolidated information to effectively monitor and execute its strategy and therefore a clear need to design and implement an automated solution to support all Performance Management processes existed.
Based on our 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, where 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
- towards the planning and budgeting applications in providing bi-directional flows of data going back and forth
towards the scorecard solution in providing aggregate information
The solution truly is an enterprise data warehouse unleashing information within a wide variety of subject areas such as: invoicing, ordering, tenders, accountancy, statistical volumes, production, defects, complaints, costs, waste, days sales & purchase outstanding etc.
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.
Due to the fact that several element61 consultants, each very experienced in one or more Performance Management areas, intervened, it was key to keep a clear overview on the overall project. This was done via a phased project planning, a thorough 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 made much more streamlined and effective.
Special attention was given to the data quality aspect. Although initially perceived as being good quality data, it turned out to be a potential pitfall after all. Here the E-PAC-TL concept was being put into practice; ETL standing for the well-know terms Extract, Transform & Load. PAC meaning Profiling, Analyzing and Cleansing of the data.
Taking into account the lack of source system documentation, small workshops were held with in-house system engineers in order to learn' the content of the various AS/400 source systems. But it was still very important to verify the perceived knowledge compared to the true' content of the data. Therefore all data warehouse candidate sources where profiled & analyzed in a pragmatic way in order to capture the current state of the data, upfront to the (E)TL activities. To name but a few of the checks which were made: distribution of values, missing values, verification of primary and foreign keys, correctness of the figures, comparisons between report outputs and table content
Finally, if possible, either the data was corrected in the source system, and potentially certain source procedures / tables / screens were modified in order to prevent certain errors from happening again in the future. On the other hand when it was not possible to solve the issue within the source system, the issue and its appropriate resolution were known before the ETL build phase was started so proper countermeasures could be taken.
Within the entire PAC-process, a central source data log was established in order to establish an optimal communication between the data warehouse project team and the source data owners.
Another innovative aspect of the project consisted in the approach. This approach dealt with larger subject areas split up into manageable pieces which could be delivered on a frequently basis as increments of 2 to 3 months. Within this approach it was very important to act in a true coaching and knowledge sharing mode, where an important part of the efforts were executed by internal ECHO Group staff, who had no relevant BI nor data warehousing background at all prior to the project start. element61 developed a training and co-development program, ensuring that the internal resources acquired the necessary skills very efficiently. Here attention was given both to general concepts within data warehousing, such as data modeling and ETL, next to more in-depth topics towards the tooling itself.
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
- 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 ReportStudio is used a report builder
- IBM Cognos 8.4 MetricsStudio is used a Balance Scorecard tool
IBM Cognos 8.4 Planning is used as Planning and Budgeting solution
element61 has been responsible for the following roles & activities:
- Definition of a Performance Management roadmap
- Project management
- Definition of the architecture
- Microsoft Integration Services and Cognos 8 Business Intelligence & Planning expertise
- Installation of all components
- Design & build
Rollout, training & coaching of the customer both on the concepts & technology deployed
Whilst Echo Group still is on its Performance Management journey, various benefits can already be stated.
While previously, the IT department sometimes was a bottleneck, mainly due to limited resources, the ownership of Performance Management has now shifted to the Finance department, obviously still relying on IT for technical support and activities. On the one hand non-IT persons will be more self-supplying, in grasping their bit of information from the data warehouse and making their own reports, on the other hand IT persons will be released from repetitive Performance Management related activities, making room for move value-added activities.
Data quality has not only been improved within the Performance Management area, but also within the daily transactional processes, due to the fact that certain data quality issues, which emerged during the construction of the Performance Management solution, have been resolved at the one place where they belong: within the source systems.
- In the longer term, Echo Group will be able to better monitor and execute its corporate strategy. By implementing an automated Planning & Budgeting solution, Echo plans to move from a quarterly to a monthly forecasting cycle, which should have a significant impact on how the company is run. Another example is that the data warehouse now enables immediate and detailed analysis of KPI's that need to be investigated. In the past, this was a time-consuming and cumbersome manual process of comparing and consolidating different Excel sheets.