Data Integration / Data Load (Part 2/3)
9 min read
In this article we will look into depth of how you load data from SAP SAP Sales Cloud into your SAP BW/BI (Business Warehouse/Business Intelligence) system.
Table of Contents
Data Integration / Data Load
Expose DataSources (SAP Sales Cloud)
(Estimate to complete configuration: 05 minutes)
This time estimate should not be used for Scoping purposes
Whether the content from a DataSource can be loaded from SAP Sales Cloud to SAP BW is determined by the type of the DataSource (see above) and if it is exposed for data replication.
Exposing a DataSource in SAP Sales Cloud is possible from the Design DataSources work center view via the Expose button.
When exposing a SAP Sales Cloud DataSource you can choose between three different types:
- Master Data
- Transactional Data
- Master Data AND Transactional Data
The choice you make directly affects how the DataSource is created in the central analytics system and how the SAP Sales Cloud DataSource is extracted by the consumer web service. The decision to expose the DataSource as transaction/master data/both needs to be taken based on the planned use in BW. Basically, a master data DataSource can have keys as well as text fields and can be used as a reference in other transactional DataSources to form a sort of star schema.
What is important to keep in mind is that you have to expose all the referenced DataSources as well. The information about the referenced DataSources can be found on the Referenced DataSources tab in the DataSource details in your SAP Sales Cloud system (see screenshot above).
This often results in exposing a whole bunch of DataSources although originally you only wanted to have a single DataSource in BW. For example to bring the Lead DataSource (CODMKLEAH) into SAP BW with all referenced DataSources, around 30 additional DataSources have to be exposed.
For downward compatibility reasons some older DataSources contain deprecated DataSources as well which were replaced by newer DataSources. If you cannot find those deprecated DataSources in your OWL (Object Work List) so that you can expose them, make sure to check the Show Obsolete check box in the advanced filter criteria:
Data Extraction, Transformation and Loading (SAP BW / SAP BI)
(Estimate to complete configuration: 30 minutes)
This time estimate should not be used for Scoping purposes
Depending on the use case for which you want to have the data from SAP Sales Cloud in your SAP BW system the steps for extracting and processing the data can vary and become complex. That’s why I stick to a simple example and describe how to extract data from SAP Sales Cloud into a BW DataSource using an InfoPackage.
In a first step, we create an application component to group all the DataSources from the SAP Sales Cloud source system. From there, switch to the DataSources pane and right click on an empty space and select Create Application Component.
Provide an ID and a description for your Application Component:
After the Application Component was created right click on it and select Create DataSource… to create a BW DataSource representation for your SAP Sales Cloud DataSource:
Provide a DataSource ID and make sure the right Source system and DataSource Data Type is specified. In contrast to what is mentioned in the documentation the DataSource type should be chosen according to the data which resides inside the DataSource.
On the General Info. tab maintain suitable short and long descriptions and make sure that on the Extraction tab the following values are set and select your SAP Sales Cloud DataSource through the value help (the value help only provides exposed DataSources):
Depending on the DataSource you have to select the right ODP semantic (transactional data / master data / time-dependent master data / texts).
Moreover, it is important to use the “Delta Only Using Full Upload” and “NO DTP Allowed for Direct Access” options as the ODP implementation in SAP Sales Cloud neither supports delta queues nor real time data access during the data transfer process.
After you have selected the SAP Sales Cloud DataSource you can switch to the Proposal tab. By selecting the Proposal tab the BW system fetches the DataSource meta data from the SAP Sales Cloud tenant by calling the ODP Web Service. When the meta data was successfully retrieved the Proposal tab shows you the data structure and the associated data types.
I strongly recommend to use the Check function to verify if all the data types are supported by your BW system. For example older releases don’t support the SSTR data type although it is much more efficient. In such a case you would have to manually adjust the field proposal with a data type which is supported by your system and doesn’t generate any conversion errors.
On the next tab Fields you can select which fields you really want to use in your BW DataSource by selecting or deselecting the Transfer checkbox. By default all fields are selected:
After you have selected all the required fields you can save and activate your BW DataSource and use the preview feature to check if the data extraction works in general but limit the number of records to reduce the extraction time:
During the activation of the BW DataSource the corresponding Persistent Staging Area (PSA) table is generated as well. Information about this table which is used to store the inbound data from your source system can be found in the technical details of your DataSource:
For actual data extraction we create an InfoPackage by right clicking on the BW DataSource:
In the next step specify the name of your InfoPackage and hit Save:
In the Data Selection tab you now have the possibility to specify some selection criteria which allow you for example to load only data for a specific timeframe or a specific organization unit.
The Extraction tab basically shows you the settings made in the DataSource for the adapter and data format.
No further changes have to be made on the Processing and Update tab, whereas the Schedule tab allows you to schedule the extraction in background and periodically which can be useful in a productive environment in combination with the right filter criteria.
In our simple example we will directly execute the InfoPackage by pressing Start.
To check the data which was loaded you can open the Manage PSA window from the DataSource details:
When everything went fine you should see a green traffic light and the PSA Maintenance option will bring you to the list of records which were extracted:
Having a running integration in place should enable the Data Warehouse experts to continue in building the complete Data Flows, including all InfoObjects, InfoProviders, DTPs, transformations etc. and meeting the business requirements.
From an administration perspective you might want to check the data flow between SAP Sales Cloud and your BW system. How to do that is described in the last part of this article: Monitoring.
Based on this scenario, the user can decide what type of data needs to be exposed. User has to expose all the referenced DataSources as well. The information about the referenced DataSources can be found on the Referenced DataSources tab in the DataSource details in your SAP Sales Cloud system. In the SAP BW/BI system, use the Check function to verify if all the data types are supported by your BW system.