Phoenix Business Intelligence Project
Weber Stephen wanted to rebuild its BI environment due to the change in the European organisation structure that was planned to take place in 2014 and 2015. element61 was selected to execute this project in close collaboration with the Weber Stephen team. This case study describes the project, its challenges and the solution that was delivered.
Weber Stephen is one of the best known brands of barbecues and accessories around the world today.
It all started with the invention of the famous round kettle grill around 1950, based on naval buoys, by Mr. George Stephen. The barbecue quickly became a success and the company bought the Weber Brothers factory to further drive production. In 2004, Weber acquired the competitor Ducane, which was specialized in the production of propane and natural gas barbecues.
The first barbeque by Mr. George Stephen
Next to barbecues and accessories, Weber Stephen has also developed other businesses.
They opened several grill restaurants in the US, opened up own barbecue shops along with cooking schools known as Grill Academies. The company has known tremendous growth throughout its history and is now a brand distributed in 30 countries across the globe. The company was family owned until 2010, when BDT Capital Partners bought a majority share.
During the initial meetings with Weber Stephen, several challenges were identified that would prove to be a true test for delivering the Business Intelligence & Data Warehousing project on-time and within budget by the combined element61- customer project team. These challenges were considered as risks and were mitigated throughout the entire lifecycle of the project.
- The move from a de-centralized towards a centralized organization
About six years ago, Weber Europe was a centralized organization. They used one ERP system, Microsoft Navision, and a data warehouse based on Microsoft SQL Server technology along with timeXtender and Targit as front-end reporting tool. As the company was quickly expanding its business in Europe with an impressive average growth rate, it was decided to split up the organization in four regions. That way, they could better serve the local needs of the customers, as well as comply with the different legal requirements in the countries. In 2013, Weber decided to centralize Europe again onto one common ERP and data warehouse platform in order to reduce costs, improve reporting transparency and streamline operational processes. Needless to say, this had a big impact on the everyday working of the company and the IT architecture.
- The ambitious timelines
Upon receiving the final go for building the new data warehouse, five months remained for delivering the new solution by October 1st, 2014. Not only this was the deadline for the go-live of the new Microsoft Navision 2013 ERP, but also all business domains needed to be covered in the "to be" Business Intelligence environment. This implied that the custom development of the ERP and the development of the data warehouse needed to be executed in parallel.
- The loading of historical information
As new business concepts were introduced, business processes were changed, new products were going live, the historical information of Weber showed significant differences with the data coming out of the new ERP system and business processes. As three years of historical data was required, a considerable effort needed to be invested in mapping the old historical information into the new data model of the data warehouse. This mapping effort was required for the four different regions, as over the years, their ways of working had steadily grown apart from one another.
- The changes in various business processes
Along with the change in ERP, several key business processes needed to be changed. For example: for the logistics part of the organization, one new central hub structure was adopted as opposed to the decentralized multi-hub structure that was in place prior to the project. This implied that a considerable consolidation exercise needed to be done in the ERP system, which of course also impacted the data warehouse.
- The availability of a stable source-system and test data
As the new ERP system was still being developed to cover the specific requirements of Weber Stephen, the source system data model was subject to change throughout the project. This implied a high degree of communication between the Navision development team, the business representatives of the four different regions and the element61 team. This risk was mitigated by identifying stable parts of the system and developing the ETL for these parts first, by closely managing and following up the issue log and by carefully planning project resources.
- The availability of technical source matter experts
As the new Microsoft Navision ERP was being developed in parallel with the data warehouse, access to source system experts was not always guaranteed. Both the ERP and the data warehouse team were working against tight deadlines, so organizing meetings with relevant resources was not always straight forward. Furthermore, the business representatives for defining the reporting requirements were spread across several locations in Europe. Per business domain (sales, logistics and finance), workshops were organized to discuss the requirements face to face. Afterwards communication was handled via mail and conference calls.
- Agreement of reporting requirements on a European level
As the different regions had developed different reports over the years, agreeing to commonly defined reports and related data model proved to be a challenge. A consensus was often heavily debated and not every regional requirement could be delivered by the new centralized data platform. Both the expectations regarding the new BI platform and the expectations of the end-users were carefully managed throughout the project.
The project was divided into three iterations from the start of the project.
These iterations were grouped according to the main business domains within Weber:
The reason for this approach was the fact that Finance could be considered the most stable part of the new ERP system. Each iteration was kicked off by a functional requirements workshop with business representatives from the different regions. During these workshops, the key reports were defined that needed to be delivered at go-live of the data warehouse on October 1st, 2014. After the functional requirements were gathered, the analysis of the data warehouse and related data model were initiated.
Using the elementary methodology, an information matrix was created to cover the high level requirements of the data model. For each of the dimensions and facts listed in this model, a complexity indicator was added, along with a stability indicator. These would then steer the rest of the analysis and the development.
First, the stable and less complex facts and dimensions were further developed in the conceptual data model, followed by the more complex items. At the end, the unstable and complex parts of the model were analysed and developed.
A part of the Information Matrix, including complexity and stability factor at the top hand corner
As the second iteration of the project was initiated at the end of the analysis phase of the first iteration, resources could be shifted between work streams to mitigate the risk of the unstable source system and to reduce the risk of rework.
Even though Microsoft Navision 2013 was selected as main source for the project, several instances of the ERP were installed to cover specific local requirements. In total 12 instances, and hence also databases, were connected to the data warehouse. Thanks to the elementary templates for Microsoft SSIS 2012, the loading process could be carefully monitored and specific databases could be loaded upon user request using an easy to use Microsoft Excel based cockpit. This non-functional requirement was needed for the Finance department during closing, or to tackle ad-hoc problems when loading the data towards the data warehouse.
Next to that, specific Microsoft Master Data Services 2012 (MDS) tables were introduced to maintain specific parts of Master Data that could not be provided by the source system. As this was also based on a Microsoft Excel interface, the business end-users could quickly adapt to this new interface and could immediately take ownership at go-live date.
The three business domains were then converted towards Tabular Microsoft SQL Services Analysis Services (SSAS) cubes, building upon views and second level facts to cover for specific reporting requirements.
This technology was selected for its speed and flexibility, and also to best serve the BI-front-end Targit that was selected for the project. This front-end allows for quick and easy reporting and report distribution. It also empowers business end-users with strong slice and dice capabilities, further enhancing the reporting capabilities of the solution. In total, element61 delivered a total of 30 reports (10 per business domain), identified as crucial during the functional workshops with the business end-users.The pr
Weber Stephen opted to choose for the Microsoft stack for both its ERP and BI environment. Several components of Microsoft SQL Server 2012 were utilized to deliver the back-end of the project:
- Microsoft SQL Server 2012 Integration Services (SSIS) for Extraction, Transformation & Loading
- Microsoft SQL Server 2012 Analyis Services (SSAS) for developing the cubes
- Microsoft SQL Server 2012 Master Data Services (MDS) for maintaining the master data
- Microsoft SQL Server 2012 for the database
For the front-end of the BI environment, Targit Decision Suite 2013 was selected for its excellent integration with Microsoft SSAS cubes, its ease of use and extended functional capabilities. Furthermore, Weber Stephen was already familiar with the technology, as the previous decentralized BI environments were based on the same technical components.
Targit BI front-end
was responsible for the following roles & activities:
- BI Project Management
- BI Requirement analysis
- Definition of the data warehouse & BI - architecture
- Microsoft Integration services: ETL development
- Microsoft Analysis Services: Cube development
- Targit 2013 setup & initial development
- Installation of all components
- Development of the logging and maintenance tool for the system
At the go-live of the project, it was clear that Weber Stephen would benefit tremendously from this new data ware house platform.
To list some of the advantages of the project:
- Consolidated data, ensuring the "one version of the truth"
As data was spread out in four different data warehouses, using different business rules with different business processes, if was hard to get a consolidated overview of the entire Europe region. With the new platform, all data is treated in the same way and the master data is also being managed centrally.
- Streamlined reporting processes throughout Europe
The same base set of reports is now in place, ensuring that measures and calculations are utilized throughout the European organization. This ensures discussions now handle about the "the why" of the figures, instead of arguing over "the correctness" of data or definitions.
- More visibility on the process of loading data, and self-service capabilities in terms of incidents
As the element61 SQL Server SSIS templates were used, all the loading of the data is now logged centrally. Furthermore, element61 customized a cockpit view for the ETL, allowing Weber Stephen resources to decide which parts of data should be manually reloaded between source and staging, and staging and data warehouse.
- Reduced complexity of cube design, increasing the penetration of BI throughout the organization
In the four BI environments that existed before, the cubes were not designed in the same way. One region would have one single cube, including all the information residing in the data warehouse. As a result, the end-users needed to know which measures were applicable to which dimensions. With the new setup, three cubes have been defined per business domain, greatly simplifying reporting and reducing the required knowledge to use the Business Intelligence environment. As a result, more people are now using BI in their daily work.
- A data platform for new future projects, such as improved budgeting and forecasting
As all the European data is now consolidated into one new platform, projects that require a lot of information as input can now use the data warehouse as a single source of information. Budgeting, forecasting and planning activities can now easily be developed and the data can be trusted.
- A lower TCO
As before four data warehouses needed to be maintained, the cost thereof has seen a big drop. From a licensing point of view, the cost has also seen a considerable reduction.
From the start of the project, it was clear that the project would be challenging. On the one hand, the project had to deliver a "rebuild and consolidation of four existing DWH platforms, but the biggest challenge was to be found in the tight deadlines and the development -in parallel !- of the new ERP and the new BI data warehouse platform.
Especially the communication and planning aspect took more effort compared to a regular BI project. But thanks to the positive "can-do mentality of both the Weber Stephen and the element61- team, the project became a success.