I would like to follow up on my previous post about a business transformation impact on SAP BW system. Let’s shed some light on different options for handling data transformations in SAP BW. In this article, I will go through pros and cons of data reload, DataMarts, conversion and hybrid approaches
Reload of all data
The seemingly best and simplest solution for mirroring an ERP transformation is the reloading of all ERP data into the SAP BW system. After all, data in the BW system was sent there via the same method to begin with, so it seems obvious to do the same after an ERP System data transformation. With a complete reload of all data into the BW System, first all existing and relevant data must be deleted. This can be done either for all data in SAO BW, or by using the “Selective Deletion“ function for relevant data areas only. Once the conversion of data in the ERP system with SAP LT is finished, a complete data extraction into the SAP BW system can follow.
One thing will quickly become apparent with this approach: The dependency between the ERP and the BW realization of the data transformation. Any data extraction can only take place after the ERP conversion is complete. This makes the BW system unavailable to users during the duration of the following data cleansing in BW (the deletion of old, not transformed data and the extraction). Once the first data is loaded, the system may be available on a limited basis at best, for example, if priority is given by fiscal year and the more recent data are loaded first.
Depending on data volume and system performance, this extraction can easily take from several days to even several weeks. Under certain circumstances, there can be an impact on performance on the ERP side since the extraction is bound to create ERP system load. These factors must be taken into account when planning such a realization. In addition to the required duration, such an approach would also clearly involve a high degree of manual effort.
Although the solution of “simply“ reloading all data from the source system(s) may seem uncomplicated at first, it is usually not a viable approach for large data volumes.
It may seem like a great idea on paper, but the brutal truth is that any realization of a massive data transformations using DataMarts is a lengthy, risky and costly undertaking. This approach involves the creation of temporary “shadow“ InfoProviders in the SAP BW system via copy where all data from the original InfoProviders are loaded into these shadow providers. In this loading process, the ERP data changes are recreated via BW data transformation – which requires the development of the relevant conversion logic in the BW System. Following that, the data in the original InfoProviders are deleted and then reloaded from the shadow providers.
This approach seems to have two advantaged: For one thing, the transformation in BW can take place in parallel with the ERP transformation since there is no time dependency. For another, the solution needs only BW onboard tools. However, this solution also requires massive investments for development and testing, especially in case of a complex ERP transformation which does not just involve a simple renaming of business units, for example.
The solution with DataMarts also requires that the database can temporarily handle large additional data volumes. Especially when running on SAP HANA this will be challenging.
Conversion of SAP BW
The conversion approach to a BW data transformation is based on the idea of executing the transformation in the ERP and the BW System based on conversion technology and tools sharing the same logic and mapping tables. This makes ERP and BW available to end users in a timely manner after production cutover.
A simple BW conversion – e.g. for DSO tables – is simple to realize and requires, in addition to a solid concept, the manual creation of ABAP programs or data scripts for the data changes. However, if numerous InfoProviders are involved, the programming quickly becomes difficult and risky. If, in addition to DSO tables, also InfoCubes and master data must be transformed, then such a solution is hardly conceivable – after all, the SIDs and DIMIDs contained in BW must be handled consistently. A better solution is the utilization of standard software instead of such a risky and costly (because provided by consultants) “makeshift“ solution.
In a BW conversion, the conversion logic first builds a so-called table pool, i.e. list of relevant InfoProviders that is based on a selection of conversion-relevant InfoObjects. Following that, conversion programs are generated which execute the conversion of BW data similarly to the SAP LT approach. These activities take place before to the actual execution of the data conversion. During this preparation phase, all normal activities, such as reporting, loading or other applications in BW remain available without limitation and all users can work in the system as usual because the activities of the transformation team consist only of configuring the data transformation.
The current Version of SAP LT covers BW conversions, but only as a restricted scenario, i.e. a scenario which has to be run by SAP or a few select partners.
In a hybrid approach, i.e. a mix between a tool or custom ABAP-based data transformation and reloading, conversion based data transformations (e.g. of the staging layer and DSOs) are combined with reloads and roll-ups. Some data reloads (e.g. for master data) can be performed easily, while the transformation of the data in the reporting layer may be very difficult.
At Datavard we have look at the ERP and BW synchronization from this perspective and built a toolset which is based on datamart solution. By high automation we overcome the main challenges of “manual” datamart approach mentioned above..
If you now ask me what is the best way how to synchronize your ERP and BW, my answer would be in asking more questions to understand your scenario better. From my experience, I’m doing this kind of projects from more than 13 years now, I have a tendency to recommend the tool based approach as it has certain advantages. However, for some cases you may be fine with complete reload. To help you take decision on your own, I’ve put together a small comparison of the approaches in the table below.
You can also find previous posts on data management and landscape transformation here: