- Motivation / Introduction
In part one I have briefly demonstrated the creation of a native DataStore Object (NDSO) using the EDW Template project of the Data Warehouse Foundation. One of the key tasks of a Data Warehouse is the consolidation and cleansing of data from various sources. Depending on the complexity of the scenario and the degree of heterogeneity of the underlying data sources the data is processed in a layered ETL process where the technical cleansing is done first and in later steps the data is cleansed and merged by applying (and adding) business semantics and rules to the data. For SAP BW and SAP BW/4HANA SAP has developed a comprehensive set of recommendations to model such ETL processes in a robust and scalable manner. We call this concept the Layered Scalable Architecture (LSA) ++ (please find a Link to the first guidance document on LSA in the link section at the end of this blog)
The central object in such processes is the advanced DataStore object in BW and BW/4HANA which is a standard object in SAP BW and SAP BW/4HANA to be customized depending on the dedicated use case (object for ETL purposes or to be used as a basis for reporting). Moreover, the ADSO provides valuable services for reliable operations out-of-the box such as:
- Delta- / Full Load capabilities
- (Selective) deletion of data, single requests or the complete content of a NDSO
- Request management
The NDSO available with the Data Warehousing Foundation delivers these services as well natively on SAP HANA. Thus, it is drastically reducing cost of development compared to developing all those capabilities from scratch…
Due to it’s seamless integration with SAP BW (powered by SAP HANA) or SAP BW/4HANA, the NDSO plays a key role in mixed scenarios (where part of the processing is done in SAP BW but another part also natively on SAP HANA) and the interoperability between BW/4 HANA and native HANA / SAP SQL Data Warehousing (where BW/4 HANA is capable to consume / access the data from well-known NDSO data structures)!
- Delta capabilities
Let’s recap and take a look at the status of the NDSO created in part 1. Remember? We already started an initial load into the empty NDSO and activated it.
- Initial Data Load
In this blog we use the sample data which is part of the standard template EDW project based upon the Data Warehousing Foundation providing the NDSO capabilities.
Important: There are two FlatFiles available which can easily be manipulated directly in SAP Web IDE by manually adding, deleting or changing records for testing purposes. Only the FlatFile ‘SalesOrder_1.csv’ is relevant for data loading. If the records are changed, they need to be transferred into a buffer table on the SAP HANA DB which serves in this scenario as the source to load the data into the NDSO with the flowgraph provided (SO.hdbflowgraph). Do not forget to execute a re-build of the ‘SourceData’ folder to update the data in the source table after having changed the FlatFile. This task is not done by the flowgraph.
After having executed the flowgraph, the data of the initial load is available in the Inbound Queue:
Note: The Inbound Queue as well as the ChangeLog have three technical key fields (red frame) to track the sequence of records loaded with each request (LoadId, recordNo) and the operation to be performed (recordmode: ‘N’ = new record = INSERT). Since we did an initial load all records are inserted as new records (recordMode ‘N’). Besides the technical key, the developer can define a semantic key to contol data granularity.
After activation the data is insterted into the ‘Active Data’- and the ‘ChangeLog’ table.
2.2) Delta Load
Let’s see what happens, if additional records are loaded in deltamode.
The first two records are changes to existing records (increase of order quantity; recordmode’A’ = ADD) and the third record is a new one. After activating the request, the Changelog data looks like this:
For customer Muhammed Eldon there are now three records:
- one with Recordmode ‘N’ representing the delta (OrderQuantitiy = 6) loaded
- a similar one with Recordmode ‘X’ and reversed sign which allows to recap the before image value (OrderQuantity = -6)
- and a record with a blank Recordmode ‘ ‘ which displays the actual situation after the delta load (After Image) where OrderQuantity = 10 (6 + 4 from Initial Load).
In the active table finally only the after image record is updated:
Same operations have been performed for Customer ‘Barry French. Since the order from customer ‘Carl Jackson’ is new, the record is also persisted in the active table without delta calculations.
If a request which is already loaded and activated needs to be deleted this is possible anytime with the information tracked in ChangeLog (Before-/ After Images).
With the NDSO all those mechanisms are delivered out-of-the box and can easily be customized according to the requirements of a given scenario. This drastically reduces development efforts and ensures data consistency.
- Further Information
Data Warehousing Foundation (DWF) SAP Help Page : http://help.sap.com/hana_options_dwf
SAP HANA DWF Community Blog: SAP HANA DWF Community Blog
SAP HANA SQL Data Warehousing – Data Warehousing Foundation: https://blogs.saphana.com/2017/10/30/sap-data-warehousing-foundation
Product Availability Matrix (PAM): https://support.sap.com/release-upgrade-maintenance/pam.htm
-> A – Z Index -> SAP HANA Data Warehousing Foundation
SAP HANA Academy Channel – Data Warehousing Foundation (DWF) https://www.youtube.com/playlist?list=PLkzo92owKnVxZcLRGmfZS-bdlIFYRQ39R
First Guidance document LSA++: