BI for Finance on top of SAP (using TimeXtender and Microsoft) in Manufacturing
Carmeuse is a leading producer of high calcium and dolomitic lime, limestone and derived goods, offering customized solutions and services to a wide range of industries.
They have an international presence than spans over 90 plants in 18 different countries and 4 continents. The group has a total consolidated net turnover of 1.15 billion Euros and employes over 4.000 people.
A former, old Data Warehouse solution existed but was too strongly SAP focused and based on SAP BW. It was almost impossible to integrate non-SAP data, and this is becoming more relevant as many dispersed Carmeuse countries have their own operational systems. This also brings up issues of data quality and data integration, because a lot of these countries deliver their data trough flat files. A major challenge was to design a system that can standardize and control this process for both SAP and non-SAP source data.
Additionally, because of the scale of the company and the many local entities, also security and privacy measures were key requirements. A salesman in Turkey must only see Turkish sales data and a Quality controller in USA may only see Quality data for his plant.
Next to technical challenges, time-to-market was also important. The development team for this project was rather small, so Carmeuse was searching for a way to make these development cycles much shorter than it was using the former SAP BW technology.
Instead of upgrading an obsolete technology platform, Carmeuse decided to replace it by a newer modern architecture that addresses all the short comings in the old setup.
The solution: Microsoft & TimeXtender
Microsoft SQL Server
Carmeuse did not want this new Datawarehouse to be solely focused on SAP data, so they moved away and chose Microsoft SQL Server as a strategic platform for the entire Analytics scope. The platform is more open and allows much easier access to all kind of data sources. The project started as on-premise but allows for a hybrid or full Cloud alternative in later stages.
Business users are also shifting to Microsoft Power BI because of its flexibility to facilitate reporting, self-service BI and dashboarding. Power BI has proven, excellent integration with Microsoft SQL Server and Microsoft Analysis Server, which is used to host the semantic model in-memory.
As mentioned in the challenges, Carmeuse needed a way to speed up Data warehouse development. After a thorough selection process, TimeXtender was deemed to be the ideal choice. TimeXtender is a Data Warehouse Automation solution completely integrated with Microsoft SQL Server. It speeds up the DWH and ETL development cycle significantly. It enables customers to tackle these cycles in a more agile way, which also benefits users as new features and bug fixes can be transported to production quickly in an easy and reliable manner.
Introducing this Data Warehousing solution at Carmeuse was not at all complicated and was achieved with an impressively fast learning curve, essential to quickly start using internal resources with limited ETL experience. This also made element61’s coaching and co-development easier, because the ETL/Data integration best practices are integrated in the solution itself. New developers can get a fast understanding of TimeXtender, and – since it is 100% metadata-driven - the solution itself has out-of-the-box documentation for any created data flow. All data runs trough TimeXtender starting from the initial layer, which is a basic copy of source systems, to a staging layer, where all transformations are performed and different sources are combined. The data then ends up in the final presentation layer, the dimensional Data Warehouse. TimeXtender’s data lineage and impact analysis make it easy to see where data is coming from and where it is going.
On demand data processing
Carmeuse is a big company, and the Business Intelligence & Data Warehousing project is integrating a lot of functional domains and various systems that require a load at different times and frequencies. A data loading system was developped that not only processes data every night but can also be executed on demand by some key end-users. A user is able to create a “LoadRequest” that will be added to a queue. Each load request will start one of the many TimeXtender packages, that will load a specific part of the data warehouse. Next to a domain, a load request can specify parameters to limit the amount of data that gets loaded, for example load only sales data for one company from the last two months.
Master Data Management and data validation
Microsoft MDS or Master Data Services (part of the Microsoft SQL Server platform) is used to solve the many Master Data mapping challenges. This solves the issue of many Excel files on shared drives and a lot of different versions that hold different definitions of master data. In MDS, we can define entities that generate underlying tables. These will be an additional source to feed the Data Warehouse. MDS is used in three ways:
Create dimensions that have no source system. This will allow users to input all data manually.
Add data from a source system automatically but allow users to manually add additional data columns.
Add a fact table structure and allow users to upload flat files.
Once users publish their input, Microsoft MDS will check data types and will notify users if they entered something wrong. The MDS solution also supports Data Quality improvements. We have added business rules that perform more specific data checks to ensure users enter data that makes sense. However, it is impossible to create checks for each table, column and scenario. For this reason we also developed a validation mechanism for the major fact tables. Certain categories of data will be blocked before it is sent to the Data warehouse. This way, a business owner is able to validate and accept (or correct) any uploaded input.
element61 was involved in the Carmeuse project for the following roles and responsibilities, in close collaboration with Carmeuse's ICT and business teams :
Best practice advice on making the right choices with respect to the BI architecture and solutions
Installation and support of the various tools (Microsoft SQL Server, TimeXtender , Analysis Services Tabular)
SQL Server & PowerBI expertise
Design and development of the Data Warehouse and semantic model
Support on the usage of TimeXtender
Creating and maintaining models in Microsoft Master Data Services (MDS)
Even for an almost full-SAP company, a Microsoft-based Analytics architecture has proven to be a very capable and quick alternative.
The BI architecture was rebuilt much faster than the previous one thanks to the TimeXtender Data Warehousing Automation solution and reporting over multiple data sources has never been easier. Users can decide when their data is reloaded, which is especially useful and essential during financial closing.
Next to that, Microsoft Master Data Services removes the hassle of working with many Excel files for data mapping purposes and trying to keep them up to date and error free.