SAP ERP as data source for Microsoft BI (Part 3)
Controlling and Logging Daily Delta Loads from SAP For most of your daily loads of master data tables the best solution is a full load by DeltaQ extraction (see preceeding post). In case something goes wrong in your ETL and you need to get fresh data, you can always restart and do the load again. Ok. But what about your daily delta loads? It is very important to understand the SAP DeltaQ mechanism in order to build a stable and reliable ETL process with delta processing. No rocket science but important to know ;-) So, what do we need to know? SAP DeltaQ Management
- As a prerequisite you need a SAP datasource that is enabled for delta processing. Check in SAP transaction RSO2.
- In SSIS build a data flow with DeltaQ component as source and configure it to use the required SAP datasource.
- Initialize the SAP DeltaQ process for the selected datasource and the configured RFC destination once. From this point on changes in SAP data are captured and queued for you. Your have two options:
- Initialization with data (Update mode 'C' in XtractIS DeltaQ component) You'll get a complete snapshot of current data to load into your target table. This process can be quite time consuming and you'll have to make sure, that no changes to source data occur in SAP during initialization.
- Initialization without data (Update mode 'S' in XtractIS DeltaQ component) SAP starts to collect changes from this point on but you get no snapshot data. This initialization type is done in a few seconds and feasible, if there is no existing data to capture or if you get historical data from another data source, e.g. an existing SAP BW system.
- From now on you start your ETL process with update mode 'D' and always get a set of changes that occured since the last run.
What if…. In case there is an error in your ETL process and you have to reprocess the last delta set again, you'll have to be careful. SAP saves the last delta set for you and you can get it again by setting update mode to 'R' (Repair mode) and specify the last REQUEST_ID in the component settings. BUT ONLY THE LAST DELTA! Every time you start the process again with update mode 'D', the previous delta set will be DELETED. No chance to get it again and you may end up with inconsistent data if you loose this information. This would mean a new time consuming 'initialization with data' and no transaction in SAP allowed meanwhile (Your SAP users and admins will hate you for that). As long as you haven't started again with update mode 'D' you can get the very last Delta set from SAP again and again (until you've got your ETL right ;-) )
DeltaQ Repair Mode
So, where to get the last REQUEST_ID from to start repair mode? XtractIS DeltaQ component has not only data output but also logging information for you. This information is very useful if you need to analyze unexpected results and errors. And it includes the corresponding REQUEST_ID. Capture it! Your basic SSIS package design should therefore always look like this:
You see, nothing difficult but good to know. Hope you find this useful. Cheers.