Business Intelligence on top of SAP, without using SAP Business Warehouse
Vandeputte Group is one of the leading European experts in safety and well-being. Starting out as a family business at the end of the 1940s, it has now grown into a fully-fledged company group, concentrating on all aspects of well-being and industrial safety. As such the organization is specialized in products and services that limit and prevent work accidents and discomfort.
The organization consists of specialized divisions, each focusing on a very specific market segment.
The Vandeputte Safety division focuses on the development and distribution of a complete range of high-quality personal protective equipment.
Samurai@Work actively works on the improvement of man's well-being in the workplace. The skills and tools concern three complementary areas of knowledge, namely vision and strategy, internal dynamic (personnel and organisation) and well-being in the workplace in all its aspects.
Artelli-Busters supports resellers, wholesale distributors and retailers. Equipped with the relevant knowledge of the market segment, and socially committed to the promotion of safety awareness Vandeputte Group started up a specific service for trade, wholesale distributors and retailers.
The entire group has offices in 3 European countries: Belgium, where the headquarters are located, The Netherlands and France. Approximately 200 employees realized a turnover of nearly 80 million Euros in 2011.
Vandeputte Group in the past encountered a number of problems related to the process of creating and distributing management information :
A lot of manual data gathering & complex calculation work was performed by individuals within the business. As such a lot of valuable business time was spent and lost in the pre-work instead of actively using the produced information. As a consequence, the manual handling of data often resulted into questionable quality of data, due to errors and underestimated data characteristics.
The BI architecture consisted of a partial roll out of SAP BW with a focus on sales reporting using a standard business content deployment approach. Next to this, a too long-running nightly copy of the complete SAP instance was taken and moved to a separate server on which complex queries were executed for reporting purposes. Conclusion: an unstable and overly complex architecture.
While the business requirements as always were not a given fact, the as-is solution was too inflexible in adding new functionality or new fields for that matter. All in all, the end user acceptance was quite low, leading to custom-built stovepipe & undocumented solutions done by the business in Excel.
The solution also did not take into account the historical correctness of the data in case of changes in master data (which is typically being dealt with in a DW environment using the correct slowly changing dimensions types).
- Regarding SAP Business Warehouse, there was nearly no internal knowledge and at the same time the responsiveness of the technology-oriented SAP BW partner was slow and passive.
- Given the fact that the IT department was quite small in size and capacity and the fact of the daily workload variety, it was clear that IT spent too much time in supporting this BI environment.
Taking into account all of the above top of the iceberg only issues, it was clear that Vandeputte Group needed to define a new BI approach together with a revised Business Intelligence & Performance Management architecture.
element61 was chosen as the preferred partner for Vandeputte Group in dealing with these challenges and in managing the implementation of the new end-to-end Business Intelligence architecture.
Using 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 Business Intelligence solution.
Before diving right into "BI technology, a 3-day Business Intelligence roadmap definition exercise was executed. Within this exercise approximately 10 individual interview sessions were conducted with key persons across the entire organization. The aim here was twofold:
Firstly, an analysis was done towards the reporting and analysis issues people were currently facing and secondly an analysis was conducted to understand better which information was really necessary (which contexts, which measures, which kind of reports, data sources involved ). This exercise resulted in a high-level estimate in terms of efforts required for the creation of a data warehouse & BI architecture and on top of this, also estimates on the efforts required to disclose all required information domains across the organization (sales, finance, logistics, management ).
Some of the identified issues:
- No centralized reporting processes
- No consistency in reports being used
- No correct view on definitions, nor calculations
- Questions about SAP BW performance & flexibility
- Lacking internal SAP BW knowledge
- Responsiveness and cost of SAP Business Warehouse partner
- Usage of Microsoft Access & Excel tools resulting in an uncontrolled reporting environment (without any control on data usage, correct relationships, transformations, labor intensive, different versions )
- Source data quality issues
- IT spending too much time supporting BI questions
In a discussion with the project sponsor the different domains were prioritized in function of business value, complexity and urgency, to name but a few.
Within the new architecture the following key points needed to be addressed:
User-friendliness towards self-service BI
Integration of data
Reduced pre-work time
Easy possibilities to add new modules & subject areas
Correct attention for error handling and other operational meta data
Using an established BI method
It is important to acknowledge that within the roadmap definition exercise most of the reporting and analyses could be marked as being complex to very complex, requiring data across different domains. Lets discuss one example; one of the KPIs selected for the first iteration was "Service Level Customer. This KPI compares how Vandeputte Group promises to deliver their orders in terms of quantity & time versus the way the goods are actually delivered to the customer. Now this may seem as any easy statement and in the end it should be, but in practice this means getting the right people around the table in order to reach an agreed upon re-definition of the KPI and the corresponding sub-KPIs. Secondly it was necessary to conduct an in-depth analysis towards all the characteristics and calculations required to fulfill the KPIs. Finally these KPIs needed to be fit within the architecture, while at the same time the architecture should still be able to answer other potential future questions.
Within the first iteration the SAP SD (Sales & Distribution) module was chosen in terms of sales orders, delivery schedules and outbound deliveries. In function of the Customer Service Level KPIs it was important to track all relevant attribute values at the moment of both the order creation and the actual delivery in order to execute correct calculations.
Given some of the issues stated earlier, Vandeputte Group did not opt for a traditional SAP BW implementation. Within the IT division most of the key BI persons were quite knowledgeable in terms of Microsoft technology. Now, with on the left hand side the SAP ERP system and on the right hand side the Microsoft BI-stack, consisting of Reporting Services, Analysis Service & Integration Services, only the middleware was lacking. After an investigation of different possible scenarios, the final choice was made for Theobald software.
Theobald software is a German company which offers a range of products which are able to connect and integrate in a seamless manner to either the physical SAP tables or more intelligently to the SAP data sources layer. The chosen product Xtract IS is integrated with Microsoft Integration Services and as such the complexity of directly accessing SAP data is removed. The product is also capable of correctly reading clustered tables and enables a delta loading mechanism using the data sources in SAP. Xtract IS can be installed in a couple of hours only and the support given by the company has proven to work very swiftly.
Underneath a conceptual view on the architectural approach is given.
The data warehouse area in the above illustration was split into a three main sub-areas:
A staging area
An open data warehouse area which consisted out of all the main star schemas such as sales orders, outbound deliveries etc. accompanied by a very rich set of dimensions.
A specific Service Level Customer area which could be seen as a data mart which caters for all the necessary calculations, all the historical correct attributes, the KPIs and the possibility to create very easy to use lag and lead reports (e.g. on which day was n% of the order fulfilled in terms of quantity). As a consequence the corresponding Service Level Customer reports hold a wide variety of parameters which help in really pinpointing the underlying issues, if any.
The architecture of the project consisted in combining the most recent versions of components of the Microsoft 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
- Theobald Xtract IS was used to connect to SAP ECC natively
- Microsoft is also used as front-end environment:
- Microsoft SQL Server Reporting Services
- Microsoft SQL Server Analysis Services
element61 has been responsible for the following roles & activities:
- Definition of a Business Intelligence roadmap
- Project management
- Definition of the architecture
- Source analysis
- Requirements analysis
- Dimensional modeling
- ETL design & implementation
- Training of the customer on dimensional modeling, Integration Service, Reporting Service and Analysis Services and overall coaching on the chosen BI-methodology
- Report development
Some of the reasons why Vandeputte Group chose element61 as their preferred partner:
- Unique coaching and co-development approach our objective is to enable a customer as fast as possible in executing BI with little to no partner dependency, except for some expert services. The co-development model with expert consultants leads to the most effective knowledge transfer.
- Quality of architecture and usage of templates & deliverables
- Best Practices & methodology
- Multi-technology knowledge a unique blend of SAP / SAP-BW & Microsoft BI knowledge & experience.
It is clear that the combination of a non-SAP BW approach through the use of one of the first implementations of Theobald Xtract IS in Belgium is groundbreaking. Integrating standard Microsoft BI tools with the aforementioned SAP connector completes the package.
At this moment in time Vandeputte Group is working in parallel on its second and third iteration and in the meantime reached the following benefits:
Knowledge transfer program about BI, Microsoft tooling & Theobald Xtract IS towards internal teams is paying off
A complex reporting set touching many end-users & divisions is available
The report set and the overall BI environment shows consistency throughout its information and is accepted as such as a reliable source for management decisions
As a consequence the mindset and the overall customer perception of BI within the entire company has shifted in the right direction
IT is recognized as a business partner and has the mandate to continue down the chosen path
More consistent usage of terminology and metadata
- Less time spent building reports, more time analyzing them